DB2 – Using Same Table in a UNION Multiple Times

db2db2-10.5db2-9.7db2-luw

I am given a query that is using the same table with same columns and filters multiple times and the only thing is changing is the Type which is given manually. I am thinking there must be a better way to do that.

Query statement is like below:

    Select Column 1, Column 2, Sum(Column 3) AS Test, "First Query" AS "Type"
           From Table X
           Where Column 1 = "Fly"
    Group by Column 1, Column 2
    
    UNION ALL 
    
    Select Column 1, Column 2, Sum(Column 3) AS Test, "Second Query" AS "Type"
           From Table X
           Where Column 1 = "Fly"
    Group by Column 1, Column 2
 
    UNION ALL 
    
    Select Column 1, Column 2, Sum(Column 3) AS Test, "Third Query" AS "Type"
           From Table X
           Where Column 1 = "Fly"
    Group by Column 1, Column 2

Thanks!

Best Answer

Use

Select X.Column1, X.Column2, Sum(X.Column3) AS Test, Types."Type"
From Table X
CROSS JOIN ( SELECT "First Query" AS "Type"
             UNION ALL
             SELECT "Second Query"
             UNION ALL
             SELECT "Third Query" ) AS Types
Where X.Column1 = "Fly"
Group by X.Column1, X.Column2, Types."Type"

Maybe SELECTs in Types needs additional FROM DUAL or similar, or DB2 uses some another method for to select scalar value - test.

UPDATE (copied from the comment)

You can shorten the CROSS JOIN part with:

CROSS JOIN ( values ('First Query'),('Second Query'),('Third Query')) types (type)

FWIW, Db2 also allows

CROSS JOIN ( values 'First Query','Second Query','Third Query') types (type)

but that's non standard I beleive – Lennart

Related Question