Skip to content
Advertisement

Oracle combines two records in one record despite of Union

I have created two simple queries to combine the records using UNION

However, I faced a problem in one case, the data in the 1st table is exactly like the 2nd one

Then the result appears in one record, not two records as others I need them in separate lines

For example, I have two tables for bank

BankAccount1 & BankAccount2

The result should be like:

but Oracle shows result like this

What do you recommend to solve this issue?

Advertisement

Answer

From the The UNION [ALL], INTERSECT, MINUS Operators documentation:

The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION ALL operator does not eliminate duplicate selected rows

So you need to use UNION ALL instead of UNION:

So, for your sample data:

This outputs:

ID |      REF | AMOUNT
-: | -------: | -----:
 1 | 10101040 |    200
 1 | 10101040 |    200

db<>fiddle here

(Note: Your query is syntactically invalid as you need a SELECT after the UNION and the column names will be the taken from the first SELECT of the UNION and the outer SELECT would expect the columns ID1, REF1, Amount1 rather than ID, REF, Amount.)

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement