SQL & PL/SQL

Greetings,

Here is my data under Oracle Database 19c Enterprise Edition Release 19.0.0.0.0

create table sampleData(
my_id number generated by default on null as identity,
sample_id number,
fieldA varchar(20),
fieldB varchar(20),
fieldC varchar(20),
fy number,
amt1 number
);
--Record 1001
insert into sampleData (sample_id, fieldA, fieldB, fieldC, fy, amt1)
values (1001, 'LocationA', 'LocationB', 'LocationC', 2021, 10);
insert into sampleData (sample_id, fieldA, fieldB, fieldC, fy, amt1)
values (1001, 'LocationA', 'LocationB', '', 2021, 20);
insert into sampleData (sample_id, fieldA, fieldB, fieldC, fy, amt1)
values (1001, 'LocationA', '', '', 2021, 30);
--Record 1005
insert into sampleData (sample_id, fieldA, fieldB, fieldC, fy, amt1)
values (1005, 'LocationA', 'LocationB', 'LocationC', 2021, 15);
insert into sampleData (sample_id, fieldA, fieldB, fieldC, fy, amt1)
values (1005, 'LocationA', 'LocationB', '', 2021, 25);
insert into sampleData (sample_id, fieldA, fieldB, fieldC, fy, amt1)
values (1005, 'LocationA', '', '', 2021, 35);
--Record 1006
insert into sampleData (sample_id, fieldA, fieldB, fieldC, fy, amt1)
values (1006, 'LocationA', 'LocationB', 'LocationC', 2021, 100);
insert into sampleData (sample_id, fieldA, fieldB, fieldC, fy, amt1)
values (1006, 'LocationA', 'LocationB', '', 2021, 200);
insert into sampleData (sample_id, fieldA, fieldB, fieldC, fy, amt1)
values (1006, 'LocationA', '', '', 2021, 300);

Query solution:

with    sample_ids_wanted    as
(
    select  1001  as sample_id, 'BASE' as label  from dual union all
    select  1005,               'COMP'           from dual
)
,    relevant_data    as
(
    select  my_id, s.sample_id
    , s.fielda, s.fieldb, s.fieldc, s.fy
    , amt1, to_char(amt1) as amt1_str
    , w.label
    from    sampledata        s
    join    sample_ids_wanted w  on  w.sample_id  = s.sample_id
)
,    unpivoted_data    as
(
    select  *
    from    relevant_data
    unpivot (                (valnum,          valstr)
          for  comp_field  in  ( (amt1,            amt1_str)   as 'AMT1'
                         )
          )
)
,    base_data    as
(
   select  ub.*
   from    unpivoted_data     ub
   where   label  = 'BASE'
)
,   comp_data    as
(
   select  uc.*
   from    unpivoted_data     uc
   where   label  = 'COMP'
)
select  b.my_id               as active_id
,       c.my_id               as comp_id
,       fielda || ', ' || fieldb || ', ' || fieldc as comp_key
,       fy
,       comp_field
,       nvl (b.valstr, '0')   as base_val
,       nvl (c.valstr, '0')   as comp_val
,       nullif ( abs (nvl (b.valnum, 0) - nvl (c.valnum, 0))
               , 0
             )            as difference
from    base_data b
full join comp_data c  using (fielda, fieldb, fieldc, fy, comp_field)
where     lnnvl (b.valstr  = c.valstr)       -- exclude rows with no difference
order by  comp_key, comp_field;

Query result:

   	ACTIVE_ID	COMP_ID	COMP_KEY	FY	COMP_FIELD	BASE_VAL	COMP_VAL	DIFFERENCE
1		6	LocationA, , 	2021	AMT1	0	35	35
2	3		LocationA, , 	2021	AMT1	30	0	30
3	2		LocationA, LocationB, 	2021	AMT1	20	0	20
4		5	LocationA, LocationB, 	2021	AMT1	0	25	25
5	1	4	LocationA, LocationB, LocationC	2021	AMT1	10	15	5

The lines 1 & 2 should be combined and shows value 5 as difference. The same with lines 3 & 4.

The issue is that some values for columns “fieldb” and “fieldc” are nulls.

Any ideas in how to solve this issue?

Johnny