Using same table in a UNION multiple times

All we need is an easy explanation of the problem, so here it is.

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!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: , , ,

Leave a Reply