6 Replies Latest reply on Oct 3, 2013 10:44 AM by S_Kulkarni

    Need SQL logic for  scenerio

    S_Kulkarni

      Hi Experts ,

       

      could you please help the   logic in implementing the below scenerio.

       

      I have below table ,ihave given records as two cases and i want to apply few rules and generate output

       

      case1:

       

      Records starting from A1 to another A1 in seg2 column will be considered as single record

      and it also should have same values for seg5 column

       

      I have to apply the following rules for all rows from A1 record to another A1

      1)count of B1,B2,C1 should be >0

      2)there should be only one A2 record with in A1 AND it should have same seg4 value with that of  A1

       

      Case 2 :

       

      If there are multiple values for seg5 with in single record of A1 ,Example consider the value of A1 record

      with Seg4 value of 234860568 ,it has two values of seg5 (they are 971330431724 and 971330431731

      so we will get two records for A1

      )

      I need to apply below rules for validation for each rows

      a.For row with B1 ,Count of B1, B2,C1, D1,E1, F1 records should be > 0.

      b.For row with B1 ,Count of A1, A2 records should be > 1

      c.For row with B2 ,there should be only 1 B2 per B1 record and it should have same seg4 value with B1

       

      My input table

      seg1seg2seg3seg4seg5
      HA1INVOICE234989253
      HA2NAME234989255
      HA2NAME234989253
      DB1NAME234989253575660203748
      DB2NAME234989253575660203748
      DC1AIRBILL234989253575660203748
      DC2AIRBILLEXT234989253575660203748
      DD1DELIVERY234989253575660203748
      DE1PACKAGE234989253575660203748
      HA1INVOICE234860568
      HA2NAME234860568
      DB1NAME234860568971330431724
      DB2NAME234860568971330431724
      DC1AIRBILL234860568971330431724
      DC2AIRBILLEXT234860568971330431724
      DD1DELIVERY234860568971330431724
      DE1PACKAGE234860568971330431724
      DB1NAME234860568971330431731
      DB2NAME234860534971330431731
      DC1AIRBILL234860568971330431731
      DC2AIRBILLEXT234860568971330431731
      DD1DELIVERY234860568971330431731
      DE1PACKAGE234860568971330431731

       

      my output data

      Seg1seg2seg3seg4statusreason
      HA1INVOICE234989253Not validtwo A2 records with insingle A1
      HA1INVOICE234860568||971330431724Valid
      HA1INVOICE234860568||971330431731Not validSeg4 value of B2 is not same as B1

       

      Database version :Oracle 10g

      table and data  details are as below

      CREATE TABLE HR.SAMPLE

      (

        SEG1  VARCHAR2(1 BYTE),

        SEG2  VARCHAR2(2 BYTE),

        SEG3  VARCHAR2(12 BYTE),

        SEG4  VARCHAR2(9 BYTE),

        SEG5  VARCHAR2(12 BYTE)

      )

      Insert into HR.SAMPLE (

      SEG1, SEG2, SEG3, SEG4, SEG5)

      Values (

      'H', 'A1', 'INVOICE', '234989253', null);

       

      Insert into HR.SAMPLE (

      SEG1, SEG2, SEG3, SEG4, SEG5)

      Values (

      'H', 'A2', 'INVOICE', '234989255', null);

       

      Insert into HR.SAMPLE (

      SEG1, SEG2, SEG3, SEG4, SEG5)

      Values (

      'H', 'A2', 'NAME', '234989253', null);

       

      Insert into HR.SAMPLE (

      SEG1, SEG2, SEG3, SEG4, SEG5)

      Values (

      'H', 'A6', 'INVSUMMARY', '234989253', null);

       

      Insert into HR.SAMPLE (

      SEG1, SEG2, SEG3, SEG4, SEG5)

      Values (

      'D', 'B1', 'NAME', '234989253', '575660203748');

       

      Insert into HR.SAMPLE (

      SEG1, SEG2, SEG3, SEG4, SEG5)

      Values (

      'D', 'B2', 'NAME', '234989253', '575660203748');

       

      Insert into HR.SAMPLE (

      SEG1, SEG2, SEG3, SEG4, SEG5)

      Values (

      'D', 'C1', 'AIRBILL', '234989253', '575660203748');

       

      Insert into HR.SAMPLE (

      SEG1, SEG2, SEG3, SEG4, SEG5)

      Values (

      'D', 'C2', 'AIRBILLEXT', '234989253', '575660203748');

       

      Insert into HR.SAMPLE (

      SEG1, SEG2, SEG3, SEG4, SEG5)

      Values (

      'D', 'D1', 'DELIVERY', '234989253', '575660203748');

       

      Insert into HR.SAMPLE (

      SEG1, SEG2, SEG3, SEG4, SEG5)

      Values (

      'D', 'E1', 'PACKAGE', '234989253', '575660203748');

       

      Insert into HR.SAMPLE (

      SEG1, SEG2, SEG3, SEG4, SEG5)

      Values (

      'H', 'A1', 'INVOICE', '234860568', null);

       

      Insert into HR.SAMPLE (

      SEG1, SEG2, SEG3, SEG4, SEG5)

      Values (

      'H', 'A2', 'NAME', '234860568', null);

       

      Insert into HR.SAMPLE (

      SEG1, SEG2, SEG3, SEG4, SEG5)

      Values (

      'H', 'A6', 'INVSUMMARY', '234860568', null);

       

      Insert into HR.SAMPLE (

      SEG1, SEG2, SEG3, SEG4, SEG5)

      Values (

      'D', 'B1', 'NAME', '234860568', '971330431724');

       

      Insert into HR.SAMPLE (

      SEG1, SEG2, SEG3, SEG4, SEG5)

      Values (

      'D', 'B2', 'NAME', '234860568', '971330431724');

       

      Insert into HR.SAMPLE (

      SEG1, SEG2, SEG3, SEG4, SEG5)

      Values (

      'D', 'C1', 'AIRBILL', '234860568', '971330431724');

       

      Insert into HR.SAMPLE (

      SEG1, SEG2, SEG3, SEG4, SEG5)

      Values (

      'D', 'C2', 'AIRBILLEXT', '234860568', '971330431724');

       

      Insert into HR.SAMPLE (

      SEG1, SEG2, SEG3, SEG4, SEG5)

      Values (

      'D', 'D1', 'DELIVERY', '234860568', '971330431724');

       

      Insert into HR.SAMPLE (

      SEG1, SEG2, SEG3, SEG4, SEG5)

      Values (

      'D', 'E1', 'PACKAGE', '234860568', '971330431724');

       

      Insert into HR.SAMPLE (

      SEG1, SEG2, SEG3, SEG4, SEG5)

      Values (

      'D', 'B1', 'NAME', '234860568', '971330431731');

       

      Insert into HR.SAMPLE (

      SEG1, SEG2, SEG3, SEG4, SEG5)

      Values (

      'D', 'B2', 'NAME', '234860534', '971330431731');

       

      Insert into HR.SAMPLE (

      SEG1, SEG2, SEG3, SEG4, SEG5)

      Values (

      'D', 'C1', 'AIRBILL', '234860568', '971330431731');

       

      Insert into HR.SAMPLE (

      SEG1, SEG2, SEG3, SEG4, SEG5)

      Values (

      'D', 'C2', 'AIRBILLEXT', '234860568', '971330431731');

       

      Insert into HR.SAMPLE (

      SEG1, SEG2, SEG3, SEG4, SEG5)

      Values (

      'D', 'D1', 'DELIVERY', '234860568', '971330431731');

       

      Insert into HR.SAMPLE (

      SEG1, SEG2, SEG3, SEG4, SEG5)

      Values (

      'D', 'E1', 'PACKAGE', '234860568', '971330431731');

       

      Insert into HR.SAMPLE (

      SEG1, SEG2, SEG3, SEG4, SEG5)

      Values (

      'H', 'A1', 'INVOICE', '234860568', null);

       

       

      could you please help me ,this is kind of looping logic i need to apply.

       

      thanks a lot

       

      Regards

      Sumanth

        • 1. Re: Need SQL logic for  scenerio
          Chris Hunt

          The first thing you need to do is add some kind of sequence number to your source data. Rows in an Oracle table don't have any particular order, so if your process depends on reading records between one A1 and the "next" A1, you need to have something you can order by to get the records in the correct sequence.

           

          Beyond that, I'd consider writing a PL/SQL procedure to do your validation, which writes its output into another table.

          • 2. Re: Need SQL logic for  scenerio
            S_Kulkarni

            Hi Chris ,

             

            Thank you for your reply , Yes even i thought of adding seq numbers to the table .

            could you please provide some  logic in PLSQL procedure .

             

            Thanks and Regards

            Sumanth

            • 3. Re: Need SQL logic for  scenerio
              Etbin

              Brute force solution needing final touch

               

              with

              sample_table as

              (select 1 seq,'H' seg1,'A1' seg2,'INVOICE' seg3,'234989253' seg4,null seg5 from dual union all

              select 2,'H','A2','INVOICE','234989255',null from dual union all

              select 3,'H','A2','NAME','234989253',null from dual union all

              select 4,'H','A6','INVSUMMARY','234989253', null from dual union all

              select 5,'D','B1','NAME','234989253','575660203748' from dual union all

              select 6,'D','B2','NAME','234989253','575660203748' from dual union all

              select 7,'D','C1','AIRBILL','234989253','575660203748' from dual union all

              select 8,'D','C2','AIRBILLEXT','234989253','575660203748' from dual union all

              select 9,'D','D1','DELIVERY','234989253','575660203748' from dual union all

              select 10,'D','E1','PACKAGE','234989253','575660203748' from dual union all

              select 11,'H','A1','INVOICE','234860568',null from dual union all

              select 12,'H','A2','NAME','234860568',null from dual union all

              select 13,'H','A6','INVSUMMARY','234860568',null from dual union all

              select 14,'D','B1','NAME','234860568','971330431724' from dual union all

              select 15,'D','B2','NAME','234860568','971330431724' from dual union all

              select 16,'D','C1','AIRBILL','234860568','971330431724' from dual union all

              select 17,'D','C2','AIRBILLEXT','234860568','971330431724' from dual union all

              select 18,'D','D1','DELIVERY','234860568','971330431724' from dual union all

              select 19,'D','E1','PACKAGE','234860568','971330431724' from dual union all

              select 20,'D','B1','NAME','234860568','971330431731' from dual union all

              select 21,'D','B2','NAME','234860534','971330431731' from dual union all

              select 22,'D','C1','AIRBILL','234860568','971330431731' from dual union all

              select 23,'D','C2','AIRBILLEXT','234860568','971330431731' from dual union all

              select 24,'D','D1','DELIVERY','234860568','971330431731' from dual union all

              select 25,'D','E1','PACKAGE','234860568','971330431731' from dual union all

              select 26,'H','A1','INVOICE','234860568',null from dual

              )

              select 'H' seg1,'A1' seg2,'INVOICE' seg3,

                     seg4 || case when case_1_2 = 2

                                  then '||' ||seg5

                             end seg4,

                     nvl2(max(reason),'Not valid','Valid') status,

                     max(reason) reason,

                     max(substr(gr || '-',1,instr(gr || '-','-') - 1)) gr

                from (select seq,seg1,seg2,seg3,seg4,seg5,gr,case_1_2,

                             last_value(reason ignore nulls) over (partition by gr,seg4) reason

                        from (select seq,seg1,seg2,seg3,seg4,seg5,case_1_2,

                                     case when case_1_2 = 2 and seg1 = 'D'

                                          then to_char(gr)||'-'||seg5

                                          else to_char(gr)

                                     end gr,

                                     case when case_1_2 = 1

                                          then case when seg1 = 'H' and cnt_a2 > 1

                                                    then 'two A2 records'

                                                    when seg1 = 'H' and same_seg4 > 1

                                                    then 'A2 records seg4 different'

                                                    when seg1 = 'D'

                                                    then case when cnt_b1 = 0

                                                              then 'B1 record missing'

                                                              when cnt_b2 = 0

                                                              then 'B2 record missing'

                                                              when cnt_c1 = 0

                                                              then 'C1 record missing'

                                                         end

                                               end

                                          else case when seg1 = 'D' and same_seg4 > 1

                                                    then 'different seg4 for B1 and B2 records'

                                                    /* add check for missing records */

                                               end

                                     end reason

                                from (select seq,seg1,seg2,seg3,seg4,seg5,gr,

                                             count(distinct seg5) over (partition by gr) case_1_2,

                                             count(case when seg2 = 'A2' then 1 end) over (partition by gr,seg5) cnt_a2,

                                             count(case when seg2 = 'B1' then 1 end) over (partition by gr,seg5) cnt_b1,

                                             count(case when seg2 = 'B2' then 1 end) over (partition by gr,seg5) cnt_b2,

                                             count(case when seg2 = 'C1' then 1 end) over (partition by gr,seg5) cnt_c1,

                                             count(case when seg2 = 'D1' then 1 end) over (partition by gr,seg5) cnt_d1,

                                             count(case when seg2 = 'E1' then 1 end) over (partition by gr,seg5) cnt_e1,

                                             count(case when seg2 = 'F1' then 1 end) over (partition by gr,seg5) cnt_f1,

                                             count(distinct seg4) over (partition by gr,seg5) same_seg4

                                        from (select seq,seg1,seg2,seg3,seg4,seg5,

                                                     last_value(w ignore nulls) over (order by seq) gr

                                                from (select seq,seg1,seg2,seg3,seg4,seg5,

                                                             case when seg2 = 'A1'

                                                                  then seq

                                                             end w

                                                        from sample_table

                                                     )

                                             )

                                     )

                             )

                     )

              where seg5 is not null

              group by seg4 || case when case_1_2 = 2

                                     then '||' ||seg5

                                end

               

              SEG1SEG2SEG3SEG4STATUSREASONGR
              HA1INVOICE234860534||971330431731Not validdifferent seg4 for B1 and B2 records11
              HA1INVOICE234860568||971330431724Valid-11
              HA1INVOICE234860568||971330431731Not validdifferent seg4 for B1 and B2 records11
              HA1INVOICE234989253Not validtwo A2 records1

               

              Regards

               

              Etbin

              • 4. Re: Need SQL logic for  scenerio
                Peter vd Zwan

                Hi,

                 

                First you should give valid test data. The table you show has 23 rows and the create table and insert statements create 26 rows.

                 

                I do not understand case 2 but for case 1 you could do something like this:

                 

                CREATE

                  TABLE sample

                  (

                    id   NUMBER

                  , SEG1 VARCHAR2(1 BYTE)

                  , SEG2 VARCHAR2(2 BYTE)

                  , SEG3 VARCHAR2(12 BYTE)

                  , SEG4 VARCHAR2(9 BYTE)

                  , SEG5 VARCHAR2(12 BYTE)

                  ) ;

                INSERT INTO SAMPLE

                  ( id, SEG1, SEG2, SEG3, SEG4, SEG5

                  ) VALUES

                  ( 1, 'H', 'A1', 'INVOICE', '234989253', NULL

                  );

                INSERT INTO SAMPLE

                  ( id, SEG1, SEG2, SEG3, SEG4, SEG5

                  ) VALUES

                  ( 2, 'H', 'A2', 'INVOICE', '234989255', NULL

                  );

                INSERT INTO SAMPLE

                  ( id, SEG1, SEG2, SEG3, SEG4, SEG5

                  ) VALUES

                  ( 3, 'H', 'A2', 'NAME', '234989253', NULL

                  );

                INSERT INTO SAMPLE

                  ( id, SEG1, SEG2, SEG3, SEG4, SEG5

                  ) VALUES

                  ( 4, 'H', 'A6', 'INVSUMMARY', '234989253', NULL

                  );

                INSERT INTO SAMPLE

                  ( id, SEG1, SEG2, SEG3, SEG4, SEG5

                  ) VALUES

                  ( 5, 'D', 'B1', 'NAME', '234989253', '575660203748'

                  );

                INSERT INTO SAMPLE

                  ( id, SEG1, SEG2, SEG3, SEG4, SEG5

                  ) VALUES

                  ( 6, 'D', 'B2', 'NAME', '234989253', '575660203748'

                  );

                INSERT INTO SAMPLE

                  ( id, SEG1, SEG2, SEG3, SEG4, SEG5

                  ) VALUES

                  ( 7, 'D', 'C1', 'AIRBILL', '234989253', '575660203748'

                  );

                INSERT INTO SAMPLE

                  ( id, SEG1, SEG2, SEG3, SEG4, SEG5

                  ) VALUES

                  ( 8, 'D', 'C2', 'AIRBILLEXT', '234989253', '575660203748'

                  );

                INSERT INTO SAMPLE

                  ( id, SEG1, SEG2, SEG3, SEG4, SEG5

                  ) VALUES

                  ( 9, 'D', 'D1', 'DELIVERY', '234989253', '575660203748'

                  );

                INSERT INTO SAMPLE

                  ( id, SEG1, SEG2, SEG3, SEG4, SEG5

                  ) VALUES

                  ( 10, 'D', 'E1', 'PACKAGE', '234989253', '575660203748'

                  );

                INSERT INTO SAMPLE

                  ( id, SEG1, SEG2, SEG3, SEG4, SEG5

                  ) VALUES

                  ( 11, 'H', 'A1', 'INVOICE', '234860568', NULL

                  );

                INSERT INTO SAMPLE

                  ( id, SEG1, SEG2, SEG3, SEG4, SEG5

                  ) VALUES

                  ( 12, 'H', 'A2', 'NAME', '234860568', NULL

                  );

                INSERT INTO SAMPLE

                  ( id, SEG1, SEG2, SEG3, SEG4, SEG5

                  ) VALUES

                  ( 13, 'H', 'A6', 'INVSUMMARY', '234860568', NULL

                  );

                INSERT INTO SAMPLE

                  ( id, SEG1, SEG2, SEG3, SEG4, SEG5

                  ) VALUES

                  ( 14, 'D', 'B1', 'NAME', '234860568', '971330431724'

                  );

                INSERT INTO SAMPLE

                  ( id, SEG1, SEG2, SEG3, SEG4, SEG5

                  ) VALUES

                  ( 15, 'D', 'B2', 'NAME', '234860568', '971330431724'

                  );

                INSERT INTO SAMPLE

                  ( id, SEG1, SEG2, SEG3, SEG4, SEG5

                  ) VALUES

                  ( 16, 'D', 'C1', 'AIRBILL', '234860568', '971330431724'

                  );

                INSERT INTO SAMPLE

                  ( id, SEG1, SEG2, SEG3, SEG4, SEG5

                  ) VALUES

                  ( 17, 'D', 'C2', 'AIRBILLEXT', '234860568', '971330431724'

                  );

                INSERT INTO SAMPLE

                  ( id, SEG1, SEG2, SEG3, SEG4, SEG5

                  ) VALUES

                  ( 18, 'D', 'D1', 'DELIVERY', '234860568', '971330431724'

                  );

                INSERT INTO SAMPLE

                  ( id, SEG1, SEG2, SEG3, SEG4, SEG5

                  ) VALUES

                  ( 19, 'D', 'E1', 'PACKAGE', '234860568', '971330431724'

                  );

                INSERT INTO SAMPLE

                  ( id, SEG1, SEG2, SEG3, SEG4, SEG5

                  ) VALUES

                  ( 20, 'D', 'B1', 'NAME', '234860568', '971330431731'

                  );

                INSERT INTO SAMPLE

                  ( id, SEG1, SEG2, SEG3, SEG4, SEG5

                  ) VALUES

                  ( 21, 'D', 'B2', 'NAME', '234860534', '971330431731'

                  );

                INSERT INTO SAMPLE

                  ( id, SEG1, SEG2, SEG3, SEG4, SEG5

                  ) VALUES

                  ( 22, 'D', 'C1', 'AIRBILL', '234860568', '971330431731'

                  );

                INSERT INTO SAMPLE

                  ( id, SEG1, SEG2, SEG3, SEG4, SEG5

                  ) VALUES

                  ( 23, 'D', 'C2', 'AIRBILLEXT', '234860568', '971330431731'

                  );

                INSERT INTO SAMPLE

                  ( id, SEG1, SEG2, SEG3, SEG4, SEG5

                  ) VALUES

                  ( 24, 'D', 'D1', 'DELIVERY', '234860568', '971330431731'

                  );

                INSERT INTO SAMPLE

                  ( id, SEG1, SEG2, SEG3, SEG4, SEG5

                  ) VALUES

                  ( 25, 'D', 'E1', 'PACKAGE', '234860568', '971330431731'

                  );

                INSERT INTO SAMPLE

                  ( id, SEG1, SEG2, SEG3, SEG4, SEG5

                  ) VALUES

                  ( 26, 'H', 'A1', 'INVOICE', '234860568', NULL

                  );

                commit;

                 

                WITH A AS
                (select
                  SUM(case WHEN seg2 = 'A1' THEN 1 ELSE 0 END) OVER (ORDER BY ID)  REC_NO
                  ,sample.*

                from
                  sample
                )
                ,B AS
                (
                SELECT
                  A.*
                  ,SUM(CASE WHEN SEG2 = 'A2' THEN 1 ELSE 0 END) OVER (PARTITION BY REC_NO) COUNT_A2                                         -- SHOULD BE 1
                  ,SUM(CASE WHEN SEG2 = 'B1' THEN 1 ELSE 0 END) OVER (PARTITION BY REC_NO) COUNT_B1                                         -- SHOULD BE >= 1
                  ,SUM(CASE WHEN SEG2 = 'B2' THEN 1 ELSE 0 END) OVER (PARTITION BY REC_NO) COUNT_B2                                         -- SHOULD BE >= 1
                  ,SUM(CASE WHEN SEG2 = 'C1' THEN 1 ELSE 0 END) OVER (PARTITION BY REC_NO) COUNT_C1                                         -- SHOULD BE >= 1
                  ,COUNT(DISTINCT CASE WHEN SEG2 = 'A1' OR SEG2 = 'A2' THEN SEG4 ELSE NULL END) OVER (PARTITION BY REC_NO) COUNT_A1_A2      -- SHOULD BE 1
                FROM
                  A
                )
                ,C AS
                (
                SELECT
                  REC_NO
                  ,CASE WHEN MAX( COUNT_A2 ) > 1 THEN 'two A2 records with insingle A1'
                        WHEN MAX( COUNT_B1 ) = 0 THEN 'NO B1 ROWS'
                        WHEN MAX( COUNT_B2 ) = 0 THEN 'NO B2 ROWS'
                        WHEN MAX( COUNT_C1 ) = 0 THEN 'NO C1 ROWS'
                        WHEN MAX( COUNT_A1_A2 ) = 0 THEN 'a1 AND a2 NOT SAME SEG4 VALUE'
                        ELSE NULL END REASON

                FROM
                  B

                GROUP BY
                  REC_NO

                SELECT
                  REC_NO
                  ,CASE WHEN REASON IS NULL THEN 'VALID' ELSE 'NOT VALID' END STATUS
                  ,REASON

                FROM
                  C
                ;

                 

                REC_NO STATUS    REASON                       
                ------ --------- -------------------------------
                     1 NOT VALID two A2 records with insingle A1
                     2 VALID                                    
                     3 NOT VALID NO B1 ROWS                     

                 

                Modify the above to your needs.

                About the PL/SQL solution; if you can do it in SQL then don't use PL/SQL.

                 

                Regards,

                 

                Peter

                • 5. Re: Need SQL logic for  scenerio
                  S_Kulkarni

                  Hi Etbin ,

                   

                  Apologies for the delay ,

                  Thanks a lot for the logic .

                  I initially find hard time understanding the code ,but after splitting into pieces it made me  easy to understand .

                   

                  Thank you .

                  Regards,

                  Sumanth Kulkarni

                  • 6. Re: Need SQL logic for  scenerio
                    S_Kulkarni

                    Hi Peter ,

                    Apologies for delay in reply .

                    Thanks a lot for the reply and sql .

                    my second case will be something like this
                    Consider the below data

                    IDseg1seg2seg3seg4
                    1HA11
                    2HA21
                    3DB112
                    4DB212
                    5DC112
                    6DC212
                    7DB113
                    8DB213
                    9DC113
                    10DC213
                    11DB114
                    12DB224
                    13DC114
                    14DC214

                     

                    Here since there are multiple seg4 values within one record of A1,so i need the concatenated value of seg3 and seg4 value .

                    and since Segment 3 of B2 is not matching with segement 3 of B1, i m making it as invalid

                    My output

                     

                    Seg1Seg2Seg3StatusReason
                    HA11||2Valid
                    HA11||3Valid
                    HB21||4Not ValidSegment 3 of B2 is not matching with B1

                     

                     

                    Thanks and Regards,

                    Sumanth