5 Replies Latest reply: Jan 5, 2013 12:58 PM by 933417 RSS

    creating dynamic window in SQL

    933417
      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
          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
            Thank you so much and thats a intelligent coding
            • 3. Re: creating dynamic window in SQL
              933417
              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
                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
                  Thank you soooo much ,that solved my problem permanently ...thanks again for such a brilliant solution