This discussion is archived
6 Replies Latest reply: Oct 3, 2013 3:44 AM by S_Kulkarni RSS

Need SQL logic for  scenerio

S_Kulkarni Newbie
Currently Being Moderated

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 Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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

Legend

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