6 Replies Latest reply: Oct 6, 2013 1:51 PM by Lalit Kumar B RSS

    Need help on query

    S_Kulkarni

      Hi Experts ,

       

      Could you please help me on query below

      I have below table

      col1 col2 col3 col4
      1A1
      2A11
      3B111
      4C111
      5D111
      6E111
      7A2
      8A12
      9B212
      10C212
      11D312
      12E212
      13F212
      14B313
      15C313
      16D313
      17E314
      18F315

       

      Here i m comparing each record (except A and A1 ) with B record .The comparsion is done with col3 and col4 of row B

      my out put is to show weather its valid record or not

      col1 col2 col3 col4Status
      1A1
      2A11
      3B111Valid
      4C111Valid
      5D111Valid
      6E111Valid
      7A2
      8A12
      9B212Valid
      10C212Valid
      11D312In Valid
      12E212Valid
      13F212Valid
      14B313Valid
      15C313Valid
      16D313Valid
      17E314In Valid
      18F315In Valid

       

      Oracle Version :10 g

      Table Details

      with t as

      (select 1  col1 ,'A'  col2 ,1  col3 ,null  col4 from dual

      union all

      select 2,'A1',1    ,null from dual

      union all

      select 3,'B',1,11 from dual

      union all

      select 4,'C',1,11 from dual

      union all

      select 5,'D',1,11 from dual

      union all

      select 6,'E',1,11 from dual

      union all

      select 7,'A',2,null from dual

      union all

      select 8,'A1',2,null from dual

      union all

      select 9,'B',2,12 from dual

      union all

      select 10,'C',2,12 from dual

      union all

      select 11,'D',3,12 from dual

      union all

      select 12,'E',2,12 from dual

      union all

      select 13,'F',2,12 from dual

      union all

      select 14,'B',3,13 from dual

      union all

      select 15,'C',3,13 from dual

      union all

      select 16,'D',3,13 from dual

      union all

      select 17,'E',3,14 from dual

      union all

      select 18,'F',3,15 from dual)

       

      select * from t;

       

      Could you please help me

       

      Thank you

        • 1. Re: Need help on query
          Pablolee

          You haven't stated the rules for valid/invalid.

          • 2. Re: Need help on query
            S_Kulkarni

            Hi Pablolee,

             

            I am comparing each record (except A and A1 ) with Bth record of col3 and col4  .The comparsion is done with col3 and col4 of row B

            If the record match the  values of col3 and col4 of B, than its valid record .otherwise invalid

            Ex:

            If you consider row 11 , 3 and 12 values of D is not matched with 2 and 12 of B so its invalid .

            I hope it now makes sense

            please let me know if there is any information required from my side .

             

            Thanks and Regards,

            Sumanth

            • 3. Re: Need help on query
              Lalit Kumar B

              1. You need to GROUP B,C,D,E by COL3 and COL4. You could use Analytic function.

              2. And then in each partition of the groupes formed, compare for values of COL3 and COL4 WHERE COL2 in( 'C','D','E' ) with COL2 = 'B'.

              3. You need to use CASE CONSTRUCT for point 2.

              4. WHEN VALUES MATCH THEN 'VALID' ELSE 'INVALID'.

               

              Regards,

              Lalit

              • 4. Re: Need help on query
                Solomon Yakobson

                with t as (

                           select 1 col1,'A' col2,1  col3,null col4 from dual union all

                           select 2, 'A1',1,null from dual union all

                           select 3, 'B', 1,11 from dual union all

                           select 4, 'C', 1,11 from dual union all

                           select 5, 'D', 1,11 from dual union all

                           select 6, 'E', 1,11 from dual union all

                           select 7, 'A', 2,null from dual union all

                           select 8, 'A1',2,null from dual union all

                           select 9, 'B', 2,12 from dual union all

                           select 10,'C', 2,12 from dual union all

                           select 11,'D', 3,12 from dual union all

                           select 12,'E', 2,12 from dual union all

                           select 13,'F', 2,12 from dual union all

                           select 14,'B', 3,13 from dual union all

                           select 15,'C', 3,13 from dual union all

                           select 16,'D', 3,13 from dual union all

                           select 17,'E', 3,14 from dual union all

                           select 18,'F', 3,15 from dual

                          )

                select  t.*,

                        case

                          when col2 not in ('A','A1')

                            then case

                                   when     col3 = last_value(case col2 when 'B' then col3 end ignore nulls) over(order by col1)

                                        and

                                            col4 = last_value(case col2 when 'B' then col4 end ignore nulls) over(order by col1)

                                     then 'Valid'

                                   else 'Invalid'

                                 end

                        end status

                  from  t

                  order by col1

                /

                 


                      COL1 CO       COL3       COL4 STATUS
                ---------- -- ---------- ---------- -------
                         1 A           1
                         2 A1          1
                         3 B           1         11 Valid
                         4 C           1         11 Valid
                         5 D           1         11 Valid
                         6 E           1         11 Valid
                         7 A           2
                         8 A1          2
                         9 B           2         12 Valid
                        10 C           2         12 Valid
                        11 D           3         12 Invalid

                      COL1 CO       COL3       COL4 STATUS
                ---------- -- ---------- ---------- -------
                        12 E           2         12 Valid
                        13 F           2         12 Valid
                        14 B           3         13 Valid
                        15 C           3         13 Valid
                        16 D           3         13 Valid
                        17 E           3         14 Invalid
                        18 F           3         15 Invalid

                18 rows selected.

                SQL>

                 

                SY.

                • 5. Re: Need help on query
                  S_Kulkarni

                  Hi Solomon ,

                   

                  Thanks a lot ,Brilliant Solution!!

                   

                  Regards

                  Sumanth

                  • 6. Re: Need help on query
                    Lalit Kumar B

                    ALAS! I thought, hints are better than spoon fed answers.

                     

                    Regards,

                    Lalit