do a sql union in oracle stage

[kjaouhari]

Hello all ,

This is what I want to do :
I have 2 tables with 2 columns
the first have columns row_id and data_load_insert and the second have row_id and data_load_update

I want to make a table from these 2 table like :
row_id, data_load_insert, data_load_update

of course a row_id can have a data_load_insert and a data_load_update

If you have an idea to perform it ...

thanks in advance for your help dsxians !

[loveojha2]

Various options:
1. SQL Join:

If it is equi join

Code: Select all

select a.rowid,data_load_insert,data_load_update 
from tablea a,tableb b
where a.rowid=b.rowid

If it is full outer join

Code: Select all

select a.rowid,data_load_insert,data_load_update 
from tablea a full outer join tableb b on
where a.rowid=b.rowid

2. Use lookup for joining and then populate.

3. Populate data from first table(insert), then from the second table (update else insert).

[ray.wurlod]

In particular observe that what you want to do is NOT a UNION, it is a full outer join. You could effect this in Oracle OCI stage, by specifying the full outer join in the Table Name field. A lookup won't do it - you would need two lookups, which would be inefficient.

[kjaouhari]

Thanks all !

this is what I did :

select R.ROW_ID
from (
select T2.ROW_ID from TABLE2 T2
union
select T1.ROW_ID from TABLE1 T1
) U, TABLE1 T1, TABLE2 T2
where R.ROW_ID = T1.ROW_ID (+)
and R.ROW_ID = T2.ROW_ID (+)
group by R.ROW_ID