Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Fetching incorrect output

User_JIAY3May 9 2021 — edited May 10 2021

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:
image.png
Need below output format.
image.pngSample 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.

Comments

Post Details

Added on May 9 2021
4 comments
161 views