CODEWITHSUNDEEP
db2union
KrisErickson
KrisErickson

Reputation: 73

IBM db2 union query bad results

When I execute this query in SQL Server which calls to IBM,

Select * from openquery(ibm,'
                        Select COST_AMT,'Query1' as Query 
                           from table
                           where clause 
                           with ur;
')
union
Select * from openquery(ibm,'
                           Select COST_AMT,'Query2' as Query 
                           from table
                           different where clause 
                           with ur;
')

I get different results in the union query than when I execute them separately and bring the results in together. I have tried the union query inside the openquery so I believe this is an IBM thing. The results appear to be a distinct selection of COST_AMT sorted by lowest to highest.

ie:

1,Query1
2,Query1
3,Query1
1,Query2
2,Query2
3,Query2

but the data is actually like this:

1,Query1
1,Query1
1,Query1
2,Query1
2,Query1
3,Query1
1,Query2
1,Query2
1,Query2
2,Query2
2,Query2
3,Query1

Am I missing something about the ibm union query? I realize I could sum and get the answer, (which is what I plan no doing) but I want to know more about why this is happening.

Upvotes: 1

Views: 615

Answers (1)

mustaccio
mustaccio

Reputation: 18945

This has nothing to do with "ibm" or "db2" -- the SQL UNION operator removes duplicates. To retain duplicates use UNION ALL.

Upvotes: 1

Related Questions