sql- suppressing the amount value to get the unique value when its multiple rows of same information

Sorry for the confused title.

I am on Teradata 16.1, I have two tables, one is a simple table with id and amount and another one is a detailed table with sub id’s and their amounts. In the second table the amounts are splits of the sub id’s which makes up the amount of id’s.

When Joining these tables the table A amount is getting repeated ( which is obvious ), but I need to get it only once so that I can reconcile that amount to other tables.Thinking of two solutions ( and open to others as well) but dont know how to achieve in sql. Its all in the Image.

Any insights?

create volatile table  main_table
(
 id varchar(10), amount decimal(10,2) )  primary index (id) on commit preserve rows;




 insert into main_table values ('A1',600); insert into main_table values ('A2',700); insert into main_table values ('A3',800);




 create volatile  table sub_table
(
 id varchar(10),  sub_id varchar(10), sub_value decimal(10,2) ) 
 primary index (id,sub_id) on commit preserve rows;


 insert into sub_table values ('A1','X1',300); insert into sub_table values ('A1','X2',300); 
 insert into sub_table values ('A2','Y1',700); 
 insert into sub_table values ('A3','Z1',200);insert into sub_table values ('A3','Z2',300);insert into sub_table values ('A3','Z3',300);

Answer

You can number rows from sub_table to identify the 1st and hide main_table amount on others

WITH 
s as (
    select ROW_NUMBER() over (partition by id order by subid) n, *
    from sub_table 
)
select m.id main_id, case when n = 1 then m.value else 0 end main_value, s.id sub_id,  s.value
from main_table m
join s on m.id=s.id

result

main_id main_value  sub_id  value
A1  600 A1  300
A1  0   A1  300
A2  700 A2  700
A3  800 A3  200
A3  0   A3  300
A3  0   A3  300

Advertisement

Answer