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
Dig Deeper on Oracle development languages
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading