Fetching incorrect output

User_JIAY3

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

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.

Frank Kulash

Hi, @User_JIAY3

Whenever you have a question, post everything (including the CREATE TABLE and INSERT commands for the sample data your existing query) right in this space. Not everyone who wants to help you can or will open attachments.

User_JIAY3

Dear Team,

Please help to re-write the query and achieve the correct report.

Frank Kulash

hi, @User_JIAY3

Thanks for posting the sample data, but please don't change your messages after you post them: it makes the thread hard to read, and your changes easy to miss. Make corrections and additions in a new comment.

So, the query you posted is producing all the rows you want, exactly as you want them, but it's also producing some rows you don't want. Sometimes it produces multiple rows for the same bc_id, but you only want one row for each bc_id. Is that right?

You can eliminate the unwanted rows using a Top-N Query, as shown below. Generate all the rows, like you're already doing, but add a column (I called it rn below) that ranks the rows in the order you want, with the "best" row numbered 1. In this case, I assume a row that actually had a match all three of the optional tables is best, and a row that matched none of the optional tables is worst. Here's one way to do that:

WITH  got_rn  AS
(
	SELECT   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
	,	 ROW_NUMBER () OVER ( PARTITION BY bch.bc_id
				      ORDER BY	   bcupts.upt_id NULLS LAST
				      ,   	   bcupth.upt_id NULLS LAST
				      ,	   bcupt.bc_id  NULLS LAST
		 	   	    ) AS rn
    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   bch.bc_s = 'A'
)
SELECT   bc_id, bc_p
,	 t_val, t_val2, t_eff
,	 upt_val, upt_val2, upt_id
,	 bcupt_s, bcupt_eff
,	 bcupts_s, bcupts_code
FROM	 got_rn
WHERE	 rn = 1
Notice that the ORDER BY bc_id
;

Notice that the sub-query got_rn is almost exactly what you posted, but with rn added to the SELECT clause.

Output (as requested):

                                            UPT   UPT            BCUPT            BCUPTS BCUPTS
 BC_ID  BC_P    T_VAL   T_VAL2 T_EFF        _VAL  _VAL2 UPT_ID   _S   BCUPT_EFF   _S     _CODE
------- ------- ------- ------- ----------- ----- ----- ------- ----- ----------- ------ ------
 123451     123      10      58 01-Jul-0021    10    58   11112 A     01-Feb-0021 A          55
 123452     345      10      58 01-Jan-0021    10    58   11114 A     01-Feb-0021
 123453     567      10      58 01-Jul-0021
 123454     789      10      58 01-Jan-0021    10    58   11115

What if two (or more) rows of output for the same bc_id all match the same number of tables? The query above considers the one with the lowest upt_id to be best, and, if there is still a tie, then one of the rows with the lowest upt_id is picked arbitrarily. It's easy to break ties in other ways, if you want to.

User_JIAY3

Hi @Frank Kulash, Very useful and i got the exact output...Thanks a lot.