issue with case statement

Question

Input data:

NUMBER	FLAG_1	FLAG_2	FLAG_3	FLAG_4	FLAG_5	FLAG_6
123456	X	X	X	X	X	X

Case stmt:

SELECT DISTINCT 
NUMBER,
CASE 
    WHEN FLAG_1='X' AND FLAG_2='X' AND FLAG_3='X' THEN 'PRE'
    WHEN FLAG_4='X' AND FLAG_5='X' AND FLAG_6='X' THEN 'POST'
ELSE 'NA' END
AS FLAG 

FROM TABLE

output of my query:

NUMBER	FLAG
123456	PRE

expected:

NUMBER	FLAG
123456	PRE
123456	POST

how the quyery can be tweaked?

Answer 1

Since the case expression returns the value for the first TRUE when, your sample row's 'POST' will never be found.

Simple trick that will work fine for you is a UNION, where you have the case twice, with switched condition orders.

SELECT 
    NUMBER,
    CASE 
        WHEN FLAG_1='X' AND FLAG_2='X' AND FLAG_3='X' THEN 'PRE'
        WHEN FLAG_4='X' AND FLAG_5='X' AND FLAG_6='X' THEN 'POST'
        ELSE 'NA'
    END AS FLAG 
FROM TABLE
UNION
SELECT 
    NUMBER,
    CASE 
        WHEN FLAG_4='X' AND FLAG_5='X' AND FLAG_6='X' THEN 'POST'
        WHEN FLAG_1='X' AND FLAG_2='X' AND FLAG_3='X' THEN 'PRE'
        ELSE 'NA'
    END AS FLAG 
FROM TABLE