Hi Guru's,
Oracle version - 19c
Whenever we joins 2 tables with outer joins then some of the columns which are not matching will have the null values. My question is how can we avoid that.
Sample code is as below:
with a as
( select 1001 as id, 'New York' as loc, 'This is test' as des, to_date('01-JAN-2022','DD-MON-YYYY') as mnth, 125.23 as qty from dual union all
select 1001 as id, 'New York' as loc,'This is test' as des, to_date('01-FEB-2022','DD-MON-YYYY') as mnth, 25.10 as qty from dual union all
select 1001 as id, 'New York' as loc,'This is test' as des, to_date('01-MAR-2022','DD-MON-YYYY') as mnth, 34.99 as qty from dual
)
, b as
(SELECT 1001 as id, 'New York' as loc,
add_months(trunc(to_date('01-DEC-2021','DD-MON-YYYY'),'MM'), level) as mnth
FROM dual
CONNECT BY LEVEL <= 12
)
select b.id, b.loc, a.des,b.mnth, nvl(a.qty,0) as qty
from a right outer join b
on (a.id = b.id and a.loc = b.loc and a.mnth = b.mnth)
order by mnth;
Dataset A contains 3 records and dataset B contains 12 records for each month but if you see the output of the above queries the column "DES" from table A is having null values after 3 records. So there are 3-4 columns as such in which I would be required same data as in dataset A.
So how can we achieve this?
Expected O/P - Null should not be there in "DES" column instead it should have "This is test" for all the rest 9 null records
Regards,
Sid