Q

How to return multiple values for THEN clause in an SQL CASE expression

Read this tip from SQL expert Rudy Limeback on how to return multiple values in THEN clause of SQL CASE expression.

I want to return multiple values in the THEN clause of a SQL CASE expression.

SELECT c.company_code, c.com_inactive_flag, e.emp_no 
FROM company c, employees e 
WHERE c.com_code = e.com_code 
AND e.emp_status in ( 
CASE c.com_inactive_flag 
WHEN 'N' THEN 'A'
WHEN 'Y' THEN 'B,C' END )

If the flag is N, all those employees will be retrieved who have status A; otherwise all those who have B and C flag should be retrieved. But because I have 'B,C' it will not return anything. Is there any way I can put 'B,C' etc. instead of just one value like 'B'?

Yes, there is a way. In this case, it's probably easier just to use Boolean logic. Combine ANDs and ORs with the right level of parentheses nesting, and you can do pretty much anything in SQL.

SELECT c.company_code
     , c.com_inactive_flag
     , e.emp_no 
  FROM company c
INNER
  JOIN employees e 
    ON e.com_code = c.com_code 
   AND (
         ( c.com_inactive_flag = 'N' 
       AND e.emp_status = 'A'
         )
      OR ( c.com_inactive_flag = 'Y' 
       AND e.emp_status IN ('B','C')
         )
       )

Note "Boolean logic" is named after George Boole, inventor of the word AND

This was first published in November 2008

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close