Oracle UNION different columns

Question

I have two tables looking something like:

TABLE_1
COL_A (int), COL_B (float), COL_C (float)


TABLE_2
COL_A (int), COL_B (float)

My query is using a UNION to put the results of these tables together, but where TABLE_2 doesn't have a COL_C, I'm looking to put something like '0'. But I just get a 'ORA-00918: column ambiguously defined' error

How can I get around this?

Answer 1

SELECT COL_A,COL_B,COL_C FROM TABLE_1
UNION
SELECT COL_A,COL_B,'0' AS COL_C FROM TABLE_2

You may also be able to get away with

SELECT COL_A,COL_B,COL_C FROM TABLE_1
UNION
SELECT COL_A,COL_B,'0' FROM TABLE_2

The basic idea is that both must have same number of columns of the same corresponding data types.

Answer 2

You can try this

SELECT COL_A, COL_B, COL_C FROM Table1
UNION
SELECT COL_A, COL_B, 0 As COL_C FROM Table2