5 Replies Latest reply: Nov 25, 2012 4:32 PM by TestAnalyst RSS

    Issue with recursive join and filter records

    TestAnalyst
      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
          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
            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
              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
                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
                  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)