DB2 With clause conditional select

Question

In my java code, I have written a native query. The below list object should be parametrizable, as like list = ('a','b') But my intension will be that one of the subqueries should only be executed, when there is a match of the given list with the constant 'a', 'b' .... Is it possible?

WITH A AS (SELECT ID FROM A WHERE 'a' in list), (SELECT ID FROM B WHERE 'b' in list), (SELECT ID FROM C WHERE 'c' in list), (SELECT ID FROM D WHERE 'd' in list)

Answer 1

As you know, @The Impaler said: SQL is a declarative language, not an imperative one. It can only select in a logical way. You can achieve your result by creating a list as a table and using Transposed Matrix, which is a LATERAL function in DB2.

db<>fiddle

WITH list (list_name) AS (VALUES 'A', 'B', 'C', 'D')
    , A (ID1, values1) AS (VALUES (1, 'A'))
    , B (ID2, values2) AS (VALUES (2, 'B'))
    , C (ID3, values3) AS (VALUES (3, 'C'))
    , D (ID4, values4) AS (VALUES (4, 'D'))



SELECT transposedID AS ID, transposedValues AS values FROM ( 
SELECT table_1.* FROM ( 
    
SELECT * FROM A,B,C,D 
INNER JOIN list l1 ON 'A' in l1.list_name
INNER JOIN list l2 ON 'B' in l2.list_name
INNER JOIN list l3 ON 'B' in l3.list_name
INNER JOIN list l4 ON 'B' in l4.list_name
) AS table_1
) CROSS JOIN LATERAL (
    VALUES 
        (ID1, values1),
        (ID2, values2),
        (ID3, values3),
        (ID4, values4)
    ) AS Transposed(transposedID, transposedValues);

Result:

#|ID|VALUES|
-+--+------+
1| 1|A     |
2| 2|B     |
3| 3|C     |
4| 4|D     |