This discussion is archived
5 Replies Latest reply: Nov 25, 2012 2:32 PM by TestAnalyst RSS

Issue with recursive join and filter records

TestAnalyst Newbie
Currently Being Moderated
I am having an issue with recursive join and filtering records for the following rules. The table, sample records, test script and rules are as below
drop table PC_COVKEY_PD;

create table PC_COVKEY_PD (
PC_COVKEY varchar(50),
COVERAGE_NUMBER varchar(3),
SEQUENCE_ALPHA  varchar(3),
TRANSACTION_TYPE varchar(3),
COV_CHG_EFF_DATE date,
TIMESTAMP_ENTERED timestamp
);

delete from PC_COVKEY_PD;
 
commit;

Insert into PC_COVKEY_PD values ('10020335P8017MT0010012','001','001','02',to_date('01/FEB/2010','DD/MON/RRRR'),to_timestamp('02/JAN/2010 01:55:59.990216 AM','DD/MON/RRRR HH12:MI:SS.FF6 AM'));
Insert into PC_COVKEY_PD values ('10020335P8017MT0050012','005','001','02',to_date('01/FEB/2010','DD/MON/RRRR'),to_timestamp('02/JAN/2010 01:56:00.268099 AM','DD/MON/RRRR HH12:MI:SS.FF6 AM'));
Insert into PC_COVKEY_PD values ('10020335P8017MT0010032','001','003','03',to_date('14/JAN/2011','DD/MON/RRRR'),to_timestamp('14/JAN/2011 04:25:19.018217 PM','DD/MON/RRRR HH12:MI:SS.FF6 AM'));
Insert into PC_COVKEY_PD values ('10020335P8017MT0010042','001','004','03',to_date('21/JAN/2011','DD/MON/RRRR'),to_timestamp('21/JAN/2011 04:00:31.719444 PM','DD/MON/RRRR HH12:MI:SS.FF6 AM'));
Insert into PC_COVKEY_PD values ('10020335P8017MT0050022','005','002','03',to_date('21/JAN/2011','DD/MON/RRRR'),to_timestamp('21/JAN/2011 04:02:48.953594 PM','DD/MON/RRRR HH12:MI:SS.FF6 AM'));

commit;

--select * from PC_COVKEY_PD order by COV_CHG_EFF_DATE,TIMESTAMP_ENTERED;
/*
PC_COVKEY          COVERAGE_NUMBER     SEQUENCE_ALPHA     TRANSACTION_TYPE     COV_CHG_EFF_DATE     TIMESTAMP_ENTERED
10020335P8017MT0010012          001     001                  02                          01/FEB/2010            02/JAN/2010 01:55:59.990216 AM
10020335P8017MT0050012          005     001                  02                      01/FEB/2010            02/JAN/2010 01:56:00.268099 AM
10020335P8017MT0010032          001     003                  03                      14/JAN/2011            14/JAN/2011 04:25:19.018217 PM
10020335P8017MT0010042          001     004                  03                      21/JAN/2011            21/JAN/2011 04:00:31.719444 PM
10020335P8017MT0050022          005     002                  03                      21/JAN/2011             21/JAN/2011 04:02:48.953594 PM
*/
Rule;

Every PC_COVKEY, query should recursively join and generate set of records depending on latest SEQUENCE_ALPHA for the coverage number at that point of time. For ex,

for 10020335P8017MT0010042 (4 row) should generate 2 records

1. 10020335P8017MT0010042001004 (PC_COVKEY || COVERAGE_NUMBER || latest SEQUENCE_ALPHA--004 for cover 001), SEQUENCE_ALPHA 001 for cover 001 is not the latest for 10020335P8017MT0010042.
2. 10020335P8017MT0010042005001 (coverage number 005, and latest sequence alpha-001 for cover 005).
SEQUENCE_ALPHA 002 for cover 005 is not the latest for 10020335P8017MT0010042 as it happened later stage.

for 10020335P8017MT0050022 (5 row) should generate 2 records as
1. 10020335P8017MT0050022001004 (PC_COVKEY || COVERAGE_NUMBER || latest SEQUENCE_ALPHA--004 for cover 001),
2. 10020335P8017MT0010042005002 (coverage number 005, and latest sequence alpha-002 for cover 005)


WITH SNAPSHOT_CVR_CTP as (
SELECT pcd1.PC_COVKEY,
       pcd1.PC_COVKEY||pcd2.COVERAGE_NUMBER||pcd2.SEQUENCE_ALPHA as cov_key, 
       pcd2.COVERAGE_NUMBER,
       pcd2.SEQUENCE_ALPHA,
       pcd2.COVERAGE_NUMBER||pcd2.SEQUENCE_ALPHA as CVRSEQ,
       max(pcd2.COVERAGE_NUMBER||pcd2.SEQUENCE_ALPHA) over (partition by pcd1.PC_COVKEY, pcd1.COVERAGE_NUMBER 
       order by pcd2.COV_CHG_EFF_DATE, pcd2.TIMESTAMP_ENTERED) as MaxSeq,
       pcd2.COV_CHG_EFF_DATE,     
       pcd2.TIMESTAMP_ENTERED
FROM
PC_COVKEY_PD pcd1,
PC_COVKEY_PD pcd2
) 
select * from SNAPSHOT_CVR_CTP SC
WHERE sc.PC_COVKEY = '10020335P8017MT0010042'  -- 4 row
--AND  COVERAGE_NUMBER||SC.MAXSEQ = COVERAGE_NUMBER||SEQUENCE_ALPHA 
ORDER BY TIMESTAMP_ENTERED

PC_COVKEY     COV_KEY     COVERAGE_NUMBER     SEQUENCE_ALPHA     CVRSEQ          MAXSEQ     COV_CHG_EFF_DATE     TIMESTAMP_ENTERED
10020335P8017MT0010042     10020335P8017MT0010042001001     001     001     001001     001001     01/FEB/2010     02/JAN/2010 01:55:59.990216 AM
10020335P8017MT0010042     10020335P8017MT0010042005001     005     001     005001     005001     01/FEB/2010     02/JAN/2010 01:56:00.268099 AM
10020335P8017MT0010042     10020335P8017MT0010042001003     001     003     001003     005001     14/JAN/2011     14/JAN/2011 04:25:19.018217 PM
10020335P8017MT0010042     10020335P8017MT0010042001004     001     004     001004     005001     21/JAN/2011     21/JAN/2011 04:00:31.719444 PM
10020335P8017MT0010042     10020335P8017MT0010042005002     005     002     005002     005002     21/JAN/2011     21/JAN/2011 04:02:48.953594 PM


I am trying to filter row using MAXSEQ but at the moment MAXSEQ values are not coming as expected. I expect following value for COV_KEY combination
COV_KEY                                         MAXSEQ
10020335P8017MT0010042001001     001004
10020335P8017MT0010042005001     005001 -- match
10020335P8017MT0010042001003     001004
10020335P8017MT0010042001004     001004 -- match
10020335P8017MT0010042005002     005001
Would appreciate if anyone can get MAxSEQ as expected.
  • 1. Re: Issue with recursive join and filter records
    jeneesh Guru
    Currently Being Moderated
    TestAnalyst wrote:
    for 10020335P8017MT0010042 (4 row) should generate 2 records

    1. 10020335P8017MT0010042001004 (PC_COVKEY || COVERAGE_NUMBER || latest SEQUENCE_ALPHA--004 for cover 001), SEQUENCE_ALPHA 001 for cover 001 is not the latest for 10020335P8017MT0010042.
    2. 10020335P8017MT0010042005001 (coverage number 005, and latest sequence alpha-001 for cover 005).
    SEQUENCE_ALPHA 002 for cover 005 is not the latest for 10020335P8017MT0010042 as it happened later stage.

    for 10020335P8017MT0050022 (5 row) should generate 2 records as
    1. 10020335P8017MT0050022001004 (PC_COVKEY || COVERAGE_NUMBER || latest SEQUENCE_ALPHA--004 for cover 001),
    2. 10020335P8017MT0010042005002 (coverage number 005, and latest sequence alpha-002 for cover 005)
    I did not understand this logic, how the two rows are generatd

    And what is your expected output..
  • 2. Re: Issue with recursive join and filter records
    TestAnalyst Newbie
    Currently Being Moderated
    Thanks for looking into problem.

    I am trying to create set of records for each PC_COVKEY. The new unique record is created for each unique cover for the latest sequence alpha at that time.

    For example,
    10020335P8017MT0010042 is the PC_COVKEY from row 4.

    At this point (all the recrods above row 4) there are 2 unique active covers (001, and 005) with different SEQUENCE_ALPHA. For Cover 001, there are 001, 003, and 004 SEQUENCE_ALPHA and for Cover 005, 001 is the SEQUENCE_ALPHA. The latest sequence alpha for 001 cover is 004 and for cover 005 is 001.

    2 records for 10020335P8017MT0010042 PC_COVKEY are
    10020335P8017MT0010042 || '001' || '004'
    10020335P8017MT0010042 || '005' || '001'

    For next PC_COVKEY from row 5
    10020335P8017MT0050022 is the PC_COVKEY from row 5.
    At this point (all the recrods above row 5) there are 2 unique active covers (001, and 005) with different SEQUENCE_ALPHA. For Cover 001, there are 001, 003, and 004 SEQUENCE_ALPHA and for Cover 005, there are 001, 002 SEQUENCE_ALPHA. The latest sequence alpha for 001 cover is 004 and for cover 005 is 002.

    2 records for 10020335P8017MT0010042 PC_COVKEY are
    10020335P8017MT0050022|| '001' || '004'
    10020335P8017MT0050022 || '005' || '002'
  • 3. Re: Issue with recursive join and filter records
    jeneesh Guru
    Currently Being Moderated
    Something like..
    with dist_cov_numbers as
    (
      select distinct coverage_number cov_number
      from PC_COVKEY_PD
    ),
    all_data as
    (
      select pcd.*,d.cov_number new_coverage_number,
             max(decode(coverage_number,d.cov_number,sequence_alpha))
                  over( partition by d.cov_number
                        order by COV_CHG_EFF_DATE,TIMESTAMP_ENTERED
                                  ) max_seq
      from PC_COVKEY_PD pcd,dist_cov_numbers d
    )
    select pc_covkey,pc_covkey||new_coverage_number||max_seq new_key,
           pc_covkey||coverage_number||sequence_alpha actual_key
    from all_data
    order by COV_CHG_EFF_DATE, TIMESTAMP_ENTERED;
    
    PC_COVKEY                   NEW_KEY                           ACTUAL_KEY                      
    -------------------------------------------------- ---------------------------------------
    10020335P8017MT0010012      10020335P8017MT0010012001001      10020335P8017MT0010012001001      
    10020335P8017MT0010012      10020335P8017MT0010012005         10020335P8017MT0010012001001      
    10020335P8017MT0050012      10020335P8017MT0050012001001      10020335P8017MT0050012005001      
    10020335P8017MT0050012      10020335P8017MT0050012005001      10020335P8017MT0050012005001      
    10020335P8017MT0010032      10020335P8017MT0010032001003      10020335P8017MT0010032001003      
    10020335P8017MT0010032      10020335P8017MT0010032005001      10020335P8017MT0010032001003      
    10020335P8017MT0010042      10020335P8017MT0010042005001      10020335P8017MT0010042001004      
    10020335P8017MT0010042      10020335P8017MT0010042001004      10020335P8017MT0010042001004      
    10020335P8017MT0050022      10020335P8017MT0050022005002      10020335P8017MT0050022005002      
    10020335P8017MT0050022      10020335P8017MT0050022001004      10020335P8017MT0050022005002  
    
     10 rows selected 
    Edited by: jeneesh on Nov 22, 2012 10:54 AM
  • 4. Re: Issue with recursive join and filter records
    jeneesh Guru
    Currently Being Moderated
    Duplicated by mistake.. Same as above post..

    Edited by: jeneesh on Nov 22, 2012 10:57 AM
  • 5. Re: Issue with recursive join and filter records
    TestAnalyst Newbie
    Currently Being Moderated
    Thanks for the solution. It seems the logic work for this set of dataset, ie one PC_COVKEY_PD recordset and unique coverage list but when I joined with 2 more dataset of similar to PC_COVKEY_PD, the max_seq didn't come as expected and also result too many duplicate records. I am still working on it and because of time constraints I resolved the issue using join and constraints (without using analytical function)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points