Search code examples
nulldb2caseexists

DB2 Case When Exists

Searching a table to see if it contains X if it doesn't find X I need it to return Y. This is what I have tried so far:

SELECT WHEN T1.X IS NULL THEN Y ELSE T1.X END AS XY 
FROM TABLE1 T1
LEFT JOIN TABLE2 T2 ON T1.X = T2.X
WHERE T1.X IN (A, B, C);

SELECT CASE WHEN T1.X IS NOT NULL THEN T1.X ELSE Y END AS XY
FROM TABLE1 T1
WHERE T1.X IN (A, B, C)
AND T1.X IN (
    SELECT T2.X
    FROM TABLE2 T2);

SELECT CASE WHEN EXISTS (SELECT X FROM TABLE1) THEN X ELSE Y END AS XY
FROM TABLE1 
WHERE X IN (A, B, C);

I am getting 0 rows returned where it doesn't find A, B, or C, not Y. But if it finds A, B, or C it does properly return X.

Solution

  • Simple UNION

    SELECT X FROM TABLE1 WHERE X IN (A, B, C) 
UNION 
SELECT Y FROM SYSIBM.SYSDUMMY1 
WHERE NOT EXISTS (SELECT X FROM TABLE1 WHERE X IN (A, B, C))