CODEWITHSUNDEEP
sql-servert-sqlconditional-statementscase
jmich738
jmich738

Reputation: 1675

SQL - Using CASE and OR in SELECT WHEN

I have this bit of code:

SELECT 
    'Type1' AS TYPE
    ,1500 AS TotalBalance
    ,0 AS OverdueBal
INTO #temp1


SELECT

    CASE
        WHEN
            (TYPE = 'Type1' AND TotalBalance BETWEEN 1301 AND 5000) OR
            (TYPE = 'Type2' AND TotalBalance >= 1301) AND
            OverdueBal > 100 THEN 'Category1'
    END
   ,*
FROM
    #temp1

What I don't understand is why this CASE statement results in TRUE?
The row does meet the first condition in the braces, but it does not meet the AND condition.
If I remove the second braces condition, then it works as it should, giving NULL as the result.
It seems to me that it is ignoring the braces.

Upvotes: 0

Views: 8546

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

Your parentheses are not complete:

SELECT (CASE WHEN ( (TYPE = 'Type1' AND TotalBalance BETWEEN 1301 AND 5000) OR
                    (TYPE = 'Type2' AND TotalBalance >= 1301)
                  ) AND
                  (OverdueBal > 100)
             THEN 'Category1'
        END)

Your version is parsed as:

SELECT (CASE WHEN (TYPE = 'Type1' AND TotalBalance BETWEEN 1301 AND 5000) OR
                  ( (TYPE = 'Type2' AND TotalBalance >= 1301) AND
                    (OverdueBal > 100)
                  )
             THEN 'Category1'
        END)

That is how AND and OR are parsed. You should use parentheses to be sure that the expression is evaluated the way that you intend.

Upvotes: 4

Related Questions