IF WITHIN A SELECT

Asked

Viewed 35,301 times

1

    SELECT  @VL_RET_INSS                = SUM(VL_RET_INSS),
            @VL_RET_IRRF                = SUM(VL_RET_IRRF),
      FROM  TABELA WHERE VALOR_TABELA > 0

I would like that when the value VL_RET_IRRF was less than 10 the field @VL_RET_IRRF received 0 and when it was greater than 10 received as I did today, could you help me? I don’t know if I should use an if or which way best, thank you.

sql

2 answers

5


You can use CASE WHEN

SELECT CASE WHEN @VL_RET_IRRF > 10 THEN 10 ELSE @VL_RET_IRRF END AS Result FROM Tabela

In the SQL above checks whether the @VL_RET_IRRF is less than 10 if it is shows 10 else it shows @VL_RET_IRRF

You can also use the IF command within SQL. Speaking of SQL Server, in newer versions from SQL Server 2012 has the IIF function which is an abbreviation of the IF ELSE function. See an example;

SELECT IIF(@VL_RET_IRRF > 10,10,@VL_RET_IRRF) AS Result FROM Tabela

To better understand IIF function:

IIF(Expressão de comparação, resultado se for verdadeiro, resultado se for falso)

4

Simple:

SELECT 
CASE WHEN @VL_RET_IRRF > 10 THEN 10 ELSE @VL_RET_IRRF END AS Result 
FROM Tabela

Multiples:

SELECT 
(CASE WHEN @VL_RET_IRRF >= 10 THEN 10 ELSE 
(CASE WHEN @VL_RET_IRRF >= 20 THEN 20 ELSE 
(CASE WHEN @VL_RET_IRRF >= 30 THEN 30 ELSE @VL_RET_IRRF END) 
END) END) AS Result 
FROM Tabela

Documentation: Here

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.