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'?
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