This discussion is archived
5 Replies Latest reply: Jan 5, 2013 10:58 AM by 933417 RSS

creating dynamic window in SQL

933417 Newbie
Currently Being Moderated
This is my dataset:

col1     col2     col3
A     1     
A     2     
B     3     
B     4     
C     5     xyz
A     6     
A     7     
B     8     
B     9     
C     10     abc

SQL to create above is :

select 'A' col1 , 1 col2 ,'' col3 from dual union all
select 'A' col1 , 2 col2 ,'' col3 from dual union all
select 'B' col1 , 3 col2 ,'' col3 from dual union all
select 'B' col1 , 4 col2 ,'' col3 from dual union all
select 'C' col1 , 5 col2 ,'xyz' col3 from dual union all
select 'A' col1 , 6 col2 ,'' col3 from dual union all
select 'A' col1 , 7 col2 ,'' col3 from dual union all
select 'B' col1 , 8 col2 ,'' col3 from dual union all
select 'B' col1 , 9 col2 ,'' col3 from dual union all
select 'C' col1 , 10 col2 ,'abc' col3 from dual

The output I need is

col1     col2     col3     col4
A     1          
A     2          xyz
B     3          
B     4          xyz
C     5     xyz     xyz
A     6          
A     7          abc
B     8          
B     9          abc
C     10     abc     abc




The logic how col4 needs to be populated is "A and B has to be tied up with nearest C.C will be having non null value for col3 ,which will be poulated to its nearest A and B."
Nearest direction should be forward pointed ,in the sense A and B should be tied up with C ,which is not only the nearest but also the one with highest rank (i.e value in col2)"
  • 1. Re: creating dynamic window in SQL
    BluShadow Guru Moderator
    Currently Being Moderated
    Your description of your logic is a bit sketchy, but I managed to get your output like this...
    SQL> ed
    Wrote file afiedt.buf
    
      1  with t as (select 'A' col1 , 1 col2 ,'' col3 from dual union all
      2             select 'A' col1 , 2 col2 ,'' col3 from dual union all
      3             select 'B' col1 , 3 col2 ,'' col3 from dual union all
      4             select 'B' col1 , 4 col2 ,'' col3 from dual union all
      5             select 'C' col1 , 5 col2 ,'xyz' col3 from dual union all
      6             select 'A' col1 , 6 col2 ,'' col3 from dual union all
      7             select 'A' col1 , 7 col2 ,'' col3 from dual union all
      8             select 'B' col1 , 8 col2 ,'' col3 from dual union all
      9             select 'B' col1 , 9 col2 ,'' col3 from dual union all
     10             select 'C' col1 , 10 col2 ,'abc' col3 from dual
     11            )
     12  --
     13  select col1, col2
     14        ,case when nvl(lead(col1) over (order by col2),'!') != col1 then last_value(col3 ignore nulls) over (order by col2 desc) else null end as col3
     15        ,col3 as col4
     16  from   t
     17* order by col2
    SQL> /
    
    C       COL2 COL COL
    - ---------- --- ---
    A          1
    A          2 xyz
    B          3
    B          4 xyz
    C          5 xyz xyz
    A          6
    A          7 abc
    B          8
    B          9 abc
    C         10 abc abc
    
    10 rows selected.
    
    SQL>
  • 2. Re: creating dynamic window in SQL
    933417 Newbie
    Currently Being Moderated
    Thank you so much and thats a intelligent coding
  • 3. Re: creating dynamic window in SQL
    933417 Newbie
    Currently Being Moderated
    Sorry for the late update,..I think the query you suggested is still not taking up the nearest A,B values for C in all the possible scenarios.

    Consider the below changes in the data set and suggest me how to go .

    select 'A' col1 , 10 col2 ,'' col3 from dual union all
    select 'A' col1 , 20 col2 ,'' col3 from dual union all
    select 'B' col1 , 30 col2 ,'' col3 from dual union all
    select 'A' col1 , 40 col2 ,'' col3 from dual union all
    select 'B' col1 , 50 col2 ,'' col3 from dual union all
    select 'C' col1 , 60 col2 ,'xyz' col3 from dual union all
    select 'A' col1 , 70 col2 ,'' col3 from dual union all
    select 'A' col1 ,80 col2 ,'' col3 from dual union all
    select 'B' col1 , 90 col2 ,'' col3 from dual union all
    select 'A' col1 , 100 col2 ,'' col3 from dual union all
    select 'B' col1 , 110 col2 ,'' col3 from dual union all
    select 'C' col1 , 120 col2 ,'abc' col3 from dual
  • 4. Re: creating dynamic window in SQL
    BluShadow Guru Moderator
    Currently Being Moderated
    So, you need to 'group' things based on 'C' being the last thing in a group and all preceding rows (in order of col2) are part of the same group.

    Once you have a group based on 'C' you can find the last 'A' and 'B' (in order of col2) within that group.
    SQL> ed
    Wrote file afiedt.buf
    
      1  with t as (select 'A' col1 , 10 col2 ,'' col3 from dual union all
      2             select 'A' col1 , 20 col2 ,'' col3 from dual union all
      3             select 'B' col1 , 30 col2 ,'' col3 from dual union all
      4             select 'A' col1 , 40 col2 ,'' col3 from dual union all
      5             select 'B' col1 , 50 col2 ,'' col3 from dual union all
      6             select 'C' col1 , 60 col2 ,'xyz' col3 from dual union all
      7             select 'A' col1 , 70 col2 ,'' col3 from dual union all
      8             select 'A' col1 ,80 col2 ,'' col3 from dual union all
      9             select 'B' col1 , 90 col2 ,'' col3 from dual union all
     10             select 'A' col1 , 100 col2 ,'' col3 from dual union all
     11             select 'B' col1 , 110 col2 ,'' col3 from dual union all
     12             select 'C' col1 , 120 col2 ,'abc' col3 from dual)
     13  --
     14  select col1
     15        ,col2
     16        ,case
     17           when col2 in (max(decode(col1,'A',col2)) over (partition by c_group),
     18                         max(decode(col1,'B',col2)) over (partition by c_group))
     19                or col1 = 'C' then
     20             max(col4) over (partition by c_group)
     21         else
     22           null
     23         end as col3
     24        ,col4
     25  from (
     26        select col1
     27              ,col2
     28              ,last_value(decode(col1,'C',col2,null) ignore nulls) over (order by col2 desc) as c_group
     29              ,col3 as col4
     30        from   t
     31       )
     32* order by col2
    SQL> /
    
    C       COL2 COL COL
    - ---------- --- ---
    A         10
    A         20
    B         30
    A         40 xyz
    B         50 xyz
    C         60 xyz xyz
    A         70
    A         80
    B         90
    A        100 abc
    B        110 abc
    C        120 abc abc
    
    12 rows selected.
  • 5. Re: creating dynamic window in SQL
    933417 Newbie
    Currently Being Moderated
    Thank you soooo much ,that solved my problem permanently ...thanks again for such a brilliant solution

Legend

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