W.H W.H - 2 months ago 25
SQL Question

Nested Case Expressions In SELECT

I'm having some difficulty pulling conditional data in a SELECT statement using conditionals.

There's the outer condition checking if a value isn't null and an inner condition checking if the value contains a particular set of characters.

The select statement:

refID = CASE externalID
WHEN IS NOT NULL THEN CASE internalType
WHEN CONTAINS(internalType,'B1') THEN 'C1' + externalID
WHEN CONTAINS(internalType,'B2') THEN 'C2' + externalID
ELSE 'Ext'
END
ELSE 'N/A'
END


Unfortunately, running this code ends with the error message "incorrect syntax near IS"

Answer

Move the field into the case

   refID = CASE 
                WHEN externalID IS NOT NULL THEN 
                     CASE 
                          WHEN internalType like '%B1%' THEN 'C1' + externalID
                          WHEN internalType like '%B2%' THEN 'C2' + externalID
                          ELSE 'Ext'
                     END
                ELSE 'N/A'
           END