CODEWITHSUNDEEP
sql
Learner
Learner

Reputation: 11

SQL CASE WHEN with multiple AND and OR

Can you please tell me if SQL code below makes sense. I am using multiple ANDs and OR in CASE WHEN. Thanks for your help! 

SELECT id, period,
 CASE WHEN state = 'group8' AND mathscore = 0 AND manager = '' OR manager ISNULL 
 THEN 'Tom' ELSE manager END AS mgr, 

 CASE WHEN state = 'group8' AND mathscore = 0 AND associate = '' OR associate 
 ISNULL THEN 'Dick' ELSE associate END AS asso,

 CASE WHEN state = 'group8' AND mathscore = 0 AND analyst = '' OR analyst ISNULL 
 THEN 'Harry' ELSE analyst END AS a

 FROM class.sections

Upvotes: 1

Views: 18668

Answers (2)

Eric Brandt
Eric Brandt

Reputation: 8101

Without sample data to work with, I'd wager you're looking for something like this, with your blanks and NULLs grouped together with parentheses.

Also, I changed your ISNULLs (a function, in some dialects) to IS NULL (a state, in SQL Server, anyway).

SELECT
  id
 ,period
 ,CASE
    WHEN state = 'group8'
         AND mathscore = 0
         AND (manager = ''
             OR manager IS NULL) THEN 'Tom'
    ELSE manager
  END AS mgr
 ,CASE
    WHEN state = 'group8'
         AND mathscore = 0
         AND (associate = ''
             OR associate IS NULL) THEN 'Dick'
    ELSE associate
  END AS asso
 ,CASE
    WHEN state = 'group8'
         AND mathscore = 0
         AND (analyst = ''
             OR analyst IS NULL) THEN 'Harry'
    ELSE analyst
  END AS a
FROM
  class.sections;

Upvotes: 0

SomeRSGuy
SomeRSGuy

Reputation: 590

When using ANDs and ORs you should enclose in parentheses to avoid unwanted results. i.e:

CASE WHEN (state = 'group8' AND mathscore = 0)
AND (manager = '' OR manager ISNULL)
THEN 'Tom' ELSE manager END AS mgr

is not the same as:

CASE WHEN (state = 'group8' AND mathscore = 0 AND manager = '')
OR manager ISNULL 
THEN 'Tom' ELSE manager END AS mgr

Upvotes: 4

Related Questions