Dear Team,
i have used the 5 tables, need to pull unique values, need below output format.(Also attached table structure and sample data with query.) please help for this.
Table_struc.txt (2.88 KB)
BCH - to get unique id
T_TEMP - Need to compare with this data only.
BCUPT, BCUPTH, BCUPTS - This is core table
My condition is. if T_TEMP table data match with BCUPT, BCUPTH, BCUPTS, it should come on report, if it is not match with any one of the table also should come BCUPT, BCUPTH, BCUPTS with no duplicate data.
Incorrect Report:
Need below output format.
Sample Data:
drop table BCH;
drop table t_temp;
drop table bcupt;
drop table bcupth;
drop table bcupts;
create table BCH (BC_id number,BC_P number,BC_S varchar(4),Bc_FM date,BC_TO date);
--123
insert into bch VALUES(123451,123,'A','01-jul-19',null);
insert into bch VALUES(123451,123,'I','01-jul-19',null);
--345
insert into bch VALUES(123452,345,'A','01-JAN-20',null);
insert into bch VALUES(123452,345,'I','01-JAN-20',null);
--567
insert into bch VALUES(123453,567,'A','01-OCT-15','30-SEP-16');
insert into bch VALUES(123453,567,'A','01-OCT-16',null);
insert into bch VALUES(123453,567,'I','01-OCT-15',null);
insert into bch VALUES(123453,567,'I','01-OCT-16',null);
--789
insert into bch VALUES(123454,789,'A','01-OCT-15','01-MAY-20');
insert into bch VALUES(123454,789,'I','01-JUN-20',null);
insert into bch VALUES(123454,789,'I','01-OCT-15',null);
CREATE table t_temp(t_run number,t_val number,t_val2 number,t_P number,t_eff date);
insert into t_temp values(1,10,58,123,'01-jul-21');
insert into t_temp values(1,10,58,345,'01-jan-21');
insert into t_temp values(1,10,58,567,'01-jul-21');
insert into t_temp values(1,10,58,789,'01-jan-21');
--123451 --123
create table bcupt(upt_id number,BC_id number,upt_val number, upt_val2 number);
insert into bcupt values (11111,123451,10,58);
insert into bcupt values (11112,123451,10,58);
create table bcupth(upt_id number,bcupt_h_seq number, bcupt_S varchar(4),bcupt_eff date);
insert into bcupth values (11111,22222,'I','01-FEB-21');
insert into bcupth values (11111,22223,'V','01-FEB-21');
insert into bcupth values (11112,22224,'A','01-FEB-21');
create table bcupts(upt_id number,bcupt_h_seq number, bcupts_S varchar(4),bcupts_code number);
insert into bcupts values (11111,22222,'I',55);
insert into bcupts values (11112,22224,'A',55);
--123452 --345
insert into bcupt values (11113,123452,10,58);
insert into bcupt values (11114,123452,10,58);
insert into bcupth values (11113,22225,'I','27-jan-21');
insert into bcupth values (11113,22226,'V','27-jan-21');
insert into bcupth values (11114,22227,'A','01-FEB-21');
--123454 --789
insert into bcupt values (11115,123454,10,58);
insert into bcupth values (11115,22228,'V','27-jan-21');
insert into bcupth values (11115,22229,'I','27-jan-21');
insert into bcupts values (11115,22229,'I',55);
select distinct BCH.BC_ID,BCH.BC_P,t_temp.t_val,t_temp.t_val2,t_temp.t_eff,bcupt.upt_val,upt_val2,bcupt.upt_id,bcupth.bcupt_S,bcupth.BCUPT_EFF,bcupts.bcupts_S,bcupts.BCUPTS_CODE from BCH join t_temp
on(t_temp.t_P =BCH.BC_P
and t_temp.t_P in ('123','345','567','789'))
left join bcupt
on (bcupt.BC_id=BCH.BC_id
and t_temp.t_val=bcupt.upt_val
and t_temp.t_val2=bcupt.upt_val2)
left join bcupth
on (bcupth.upt_id = bcupt.upt_id
and bcupth.bcupt_S='A')
left join bcupts
on (bcupts.upt_id = bcupt.upt_id
and bcupts.BCUPT_H_SEQ=bcupth.BCUPT_H_SEQ
and bcupts.bcupts_S='A')
where BC_S='A';
Thanks.