This discussion is archived
6 Replies Latest reply: Oct 6, 2013 11:51 AM by Lalit Kumar B RSS

Need help on query

S_Kulkarni Newbie
Currently Being Moderated

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

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

  • 2. Re: Need help on query
    S_Kulkarni Newbie
    Currently Being Moderated

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

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

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

    Hi Solomon ,

     

    Thanks a lot ,Brilliant Solution!!

     

    Regards

    Sumanth

  • 6. Re: Need help on query
    Lalit Kumar B Explorer
    Currently Being Moderated

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

     

    Regards,

    Lalit

Legend

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