By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;
| BANNER | BANNER_FULL | BANNER_LEGACY | CON_ID |
|---|---|---|---|
| Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production | Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0 |
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production | 0 |
create table table1
( t1_id integer generated always as identity
, product1 varchar2(20)
, location varchar2(20)
, new_product_name varchar2(20)
) ;
insert into table1(product1, location, new_product_name)
select 'zasHX-1234','Warehoues 1', 'zasHX' from dual
union all
select 'akc-1234','Warehoues 2',null from dual
union all
select 'aaa-1239','Warehoues 3','xyz' from dual;
3 rows affected
create table table2
( t2_id integer generated always as identity
, product2 varchar2(20)
, quantity integer
, adate date
);
insert into table2(product2,quantity,adate)
select 'zasHX', 23, sysdate-5 from dual
union all
select 'akc-1234', 117, sysdate from dual;
2 rows affected
Select t1_id
, coalesce(t2.product2,t2n.product2,t1.product1) product
, t1.location
, coalesce(t2.quantity,t2n.quantity) product
, coalesce(t2.adate,t2n.adate) "date"
from table1 t1
Left join table2 t2
on (t2.product2 = t1.product1)
left join table2 t2n
on (t2n.product2 = t1.new_product_name)
order by t1_id;
| T1_ID | PRODUCT | LOCATION | PRODUCT | date |
|---|---|---|---|---|
| 1 | zasHX | Warehoues 1 | 23 | 16-JUL-20 |
| 2 | akc-1234 | Warehoues 2 | 117 | 21-JUL-20 |
| 3 | aaa-1239 | Warehoues 3 | null | null |