CODEWITHSUNDEEP
sqlt-sql
W.H
W.H

Reputation: 197

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"

Upvotes: 0

Views: 3363

Answers (2)

Jim Macaulay
Jim Macaulay

Reputation: 5155


Hi
In this case you can use INSTR function,

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

INSTR - instring function checks the required string or pattern and processes the data

Upvotes: 1

JohnHC
JohnHC

Reputation: 11205

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

Upvotes: 4

Related Questions