1 2 Previous Next 16 Replies Latest reply on Sep 8, 2009 2:05 PM by Aketi Jyuuzou

    emulate "ignore nulls" of Lead of DB2 V9.7

    Aketi Jyuuzou
      I am using Oracle10gR2.

      http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.sql.ref.doc/doc/r0023461.html
      In DB2 V9.7, there is new future which is "ignore nulls" of "lead" and "Lag"
      I want to emulate below DB2 V9.7 SQL.
      select SortKey,Val,
      Lag (Val,2,999,ignore nulls) over(order by SortKey) as Lag2,
      Lead(Val,2,999,ignore nulls) over(order by SortKey) as Lead2
        from W;
      Therefore I want below output.
      SortKey   Val  Lag2  Lead2
      -------  ----  ----  -----
            1     2   999      6
            2  null   999      6
            5     4   999      5
            9  null     2      5
           11     6     2      3
           12  null     4      3
           14  null     4      3
           16     5     4      4
           17  null     6      4
           20     3     6    999
           21  null     5    999
           22     4     5    999
      I like using "case expression" and "ignore nulls".
      But first study step should be easy.
      create table W as
      select  1 as SortKey,2 as Val from dual union all
      select  2,null from dual union all
      select  5,   4 from dual union all
      select  9,null from dual union all
      select 11,   6 from dual union all
      select 12,null from dual union all
      select 14,null from dual union all
      select 16,   5 from dual union all
      select 17,null from dual union all
      select 20,   3 from dual union all
      select 21,null from dual union all
      select 22,   4 from dual;
      
      select SortKey,Val,Lag2,Lead2,
      First_Value(Lag2 ignore nulls)
      over(order by SortKey rows between current row
                                     and Unbounded Following) as Lag3,
      Last_Value(Lead2 ignore nulls) over(order by SortKey) as Lead3
      from (select SortKey,Val,
            case when Val is not null then Lag (Val,2,999)
            over(partition by nvl2(Val,0,1) order by SortKey) end as Lag2,
            case when Val is not null then Lead(Val,2,999)
            over(partition by nvl2(Val,0,1) order by SortKey) end as Lead2
              from W)
      order by SortKey;
      Is there solution of Oracle more easy of my above solution?
        • 1. Re: emulate "ignore nulls" of Lead of DB2 V9.7
          Aketi Jyuuzou
          This is more difficult case 3th parameter of Lead and Lag is "null".
          select SortKey,Val,
          Lag (Val,2,null,'IGNORE NULLS') over(order by SortKey) as Lag2,
          Lead(Val,2,null,'IGNORE NULLS') over(order by SortKey) as Lead2
            from W;
          select SortKey,Val,
          case when aboveCnt >= 2
               then First_Value(Lag2 ignore nulls)
                    over(order by SortKey rows between current row
                                                   and Unbounded Following)
               end as Lag2,
          case when belowCnt >= 2
               then Last_Value(Lead2 ignore nulls)
                    over(order by SortKey)
               end as Lead2
          from (select SortKey,Val,
                case when Val is not null
                     then Lag (Val,2,null) 
                          over(partition by nvl2(Val,0,1) order by SortKey) end as Lag2,
                count(Val) over(order by SortKey rows between unbounded preceding
                                                          and 1 preceding) aboveCnt,
                case when Val is not null
                     then Lead(Val,2,null)
                          over(partition by nvl2(Val,0,1) order by SortKey) end as Lead2,
                count(Val) over(order by SortKey rows between 1 following
                                                          and unbounded following) belowCnt
                  from W)
          order by SortKey;
          
          SORTKEY   VAL  LAG2  LEAD2
          -------  ----  ----  -----
                1     2  null      6
                2  null  null      6
                5     4  null      5
                9  null     2      5
               11     6     2      3
               12  null     4      3
               14  null     4      3
               16     5     4      4
               17  null     6      4
               20     3     6   null
               21  null     5   null
               22     4     5   null
          Is there solution of Oracle more easy of my above solution?

          I thought model solution.
          But I did not make model solution.
          Below solution return wrong result.
          select *
            from W
           model
          dimension by(Row_Number() over(order by SortKey) as rn)
          measures(SortKey,Val,Val as Lag1,Val as Lag2)
          rules(
          Lag2[any] order by rn = nvl2(Val[cv()-1],Lag1[cv()-1],Lag2[cv()-1]),
          Lag1[any] order by rn = nvl (Val[cv()-1],Lag1[cv()-1]));
          Edited by: Aketi Jyuuzou on Jun 23, 2009 7:13 AM
          fixed above emulate SQL using OLAP and its resultSet.
          • 2. Re: emulate "ignore nulls" of Lead of DB2 V9.7
            SeánMacGC
            Hello Aketi,
            Have you tried the MODEL with IGNORE NAV?
            select *
              from W
             model
            dimension by(Row_Number() over(order by SortKey) as rn)
            measures(SortKey,Val,Val as Lag1,Val as Lag2)
            IGNORE NAV
            rules(
            Lag2[any] order by rn = nvl2(Val[cv()-1],Lag1[cv()-1],Lag2[cv()-1]),
            Lag1[any] order by rn = nvl (Val[cv()-1],Lag1[cv()-1]));
            1 person found this helpful
            • 3. Re: emulate "ignore nulls" of Lead of DB2 V9.7
              Boneist
              I'm not entirely sure I've got the right logic you're after here, as your results differ from what I expected (and yes, fully aware this is not particularly optimal, due to having to navigate the table twice):
              with t1 as (select  1 as SortKey,2 as Val from dual union all
                          select  2,null from dual union all
                          select  5,   4 from dual union all
                          select  9,null from dual union all
                          select 11,   6 from dual union all
                          select 12,null from dual union all
                          select 14,null from dual union all
                          select 16,   5 from dual union all
                          select 17,null from dual union all
                          select 20,   3 from dual union all
                          select 21,null from dual union all
                          select 22,   4 from dual)
              select t1.sortkey,
                     t1.val,
                     nvl(t2.lag2, last_value(t2.lag2 ignore nulls) over (order by t1.sortkey)) lag2_1,
                     nvl(t2.lead2, last_value(t2.lead2 ignore nulls) over (order by t1.sortkey)) lead2_1
              from   (select sortkey,
                             val,
                             Lag (Val,2,999) over(order by SortKey) as Lag2,
                             Lead(Val,2,999) over(order by SortKey) as Lead2
                      from   t1
                      where  val is not null) t2,
                      t1
              where  t1.sortkey = t2.sortkey (+)
              order by t1.sortkey;
              
                 SORTKEY        VAL     LAG2_1    LEAD2_1
              ---------- ---------- ---------- ----------
                       1          2        999          6
                       2                   999          6
                       5          4        999          5
                       9                   999          5
                      11          6          2          3
                      12                     2          3
                      14                     2          3
                      16          5          4          4
                      17                     4          4
                      20          3          6        999
                      21                     6        999
                      22          4          5        999
              If you're ignoring nulls from the lag and lead, then I'm not sure how you get your results. Perhaps you could enlighten me, please?
              1 person found this helpful
              • 4. Re: emulate "ignore nulls" of Lead of DB2 V9.7
                Nicolas Gasparotto
                What about :
                SQL> select sortkey, val,
                  2         nvl(last_value(val ignore nulls) over (order by rn range between unbounded preceding and nvl2(val,2,1) preceding),999) lag2,
                  3         nvl(first_value(val ignore nulls) over (order by rn range between 2 following and unbounded following),999) lead2
                  4  from   (select sortkey,val,count(val) over (order by sortkey) rn from w)
                  5  order by sortkey;
                
                   SORTKEY        VAL       LAG2      LEAD2
                ---------- ---------- ---------- ----------
                         1          2        999          6
                         2                   999          6
                         5          4        999          5
                         9                     2          5
                        11          6          2          3
                        12                     4          3
                        14                     4          3
                        16          5          4          4
                        17                     6          4
                        20          3          6        999
                        21                     5        999
                        22          4          5        999
                
                12 rows selected.
                Nicolas.

                PS : change sum(nvl2(val,1,0)) to count(val)

                Edited by: N. Gasparotto on Jun 22, 2009 6:31 PM
                1 person found this helpful
                • 5. Re: emulate "ignore nulls" of Lead of DB2 V9.7
                  Aketi Jyuuzou
                  If you're ignoring nulls from the lag and lead, then I'm not sure how you get your results. Perhaps you could >enlighten me, please?
                  sorry my SQL was wrong.
                  I have fixed it.
                  • 6. Re: emulate "ignore nulls" of Lead of DB2 V9.7
                    Aketi Jyuuzou
                    Thanks reply.
                    Have you tried the MODEL with IGNORE NAV?
                    But that returns below incorrect result.
                    select *
                      from W
                     model
                    dimension by(Row_Number() over(order by SortKey) as rn)
                    measures(SortKey,Val,Val as Lag1,Val as Lag2)
                    IGNORE NAV
                    rules(
                    Lag2[any] order by rn = nvl2(Val[cv()-1],Lag1[cv()-1],Lag2[cv()-1]),
                    Lag1[any] order by rn = nvl (Val[cv()-1],Lag1[cv()-1]));
                    
                           RN    SORTKEY        VAL       LAG1       LAG2
                    ---------  ---------  ---------  ---------  ---------
                            1          1          2          0          0
                            2          2  null               2          2
                            3          5          4          0          0
                            4          9  null               4          4
                            5         11          6          0          0
                            6         12  null               6          6
                            7         14  null               0          0
                            8         16          5          0          0
                            9         17  null               5          5
                           10         20          3          0          0
                           11         21  null               3          3
                           12         22          4          0          0
                    • 7. Re: emulate "ignore nulls" of Lead of DB2 V9.7
                      Aketi Jyuuzou
                      umm
                      I will be thinking logic of your solution about 1 day.
                      • 8. Re: emulate "ignore nulls" of Lead of DB2 V9.7
                        666352
                        Hi,
                        I found solution for lead, tomorrow i continue for lag.( i go sleeping :) )

                        Reagrds salim.
                        select SORTKEY , VAL , LEAD2,LAG2
                        from w
                        model
                        dimension by ( row_number()over( order by sortkey  ) rn)
                        measures(  case when val is not null then 1 end ind,sortkey,val,
                                          to_number(null) lead2,to_number(null) lag2,
                                          to_number(null) cpt_lead,
                          to_number(null) cpt_lag)
                        (cpt_lead[any]=sum(ind)[cv()<rn],
                        lead2[rn] = case when cpt_lead[cv()]>=2 and ind[cv()] is null then lead2[cv()-1]
                                                   when cpt_lead[cv()]>=2 then lead( val  , 2,999)over(partition by ind order by sortkey)  else 999 end,
                        cpt_lag[rn] =sum(ind)[cv()>rn],
                        lag2[any]   =  case --when cpt_lag[cv()]>=2 and ind[cv()] is null then lead(lag2)over(partition by ind order by sortkey ) 
                                                   when cpt_lag[cv()]>=2 then lag( val  , 2,999)over(partition by ind order by sortkey ) else 999  end 
                        )
                        /
                        SQL> select SORTKEY , VAL , LEAD2,LAG2
                          2  from w
                          3  model
                          4  dimension by ( row_number()over( order by sortkey  ) rn)
                          5  measures(  case when val is not null then 1 end ind,sortkey,val,
                          6                    to_number(null) lead2,to_number(null) lag2,
                          7                    to_number(null) cpt_lead,
                          8    to_number(null) cpt_lag)
                          9  (cpt_lead[any]=sum(ind)[cv()<rn],
                         10  lead2[rn] = case when cpt_lead[cv()]>=2 and ind[cv()] is null then lead2[cv()-1]
                         11                             when cpt_lead[cv()]>=2 then lead( val  , 2,999)over(partition by ind
                         order by sortkey)  else 999 end,
                         12  cpt_lag[rn] =sum(ind)[cv()>rn],
                         13  lag2[any]   =  case --when cpt_lag[cv()]>=2 and ind[cv()] is null then lead(lag2)over(partition
                         by ind order by sortkey ) 
                         14                             when cpt_lag[cv()]>=2 then lag( val  , 2,999)over(partition by ind o
                        rder by sortkey ) else 999  end 
                         15  )
                         16  /
                        
                           SORTKEY        VAL      LEAD2       LAG2
                        ---------- ---------- ---------- ----------
                                 1          2          6        999
                                 2                     6        999
                                 5          4          5        999
                                 9                     5        999
                                11          6          3          2
                                12                     3
                                14                     3
                                16          5          4          4
                                17                     4
                                20          3        999          6
                                21                   999
                                22          4        999          5
                        
                        12 rows selected.
                        • 9. Re: emulate "ignore nulls" of Lead of DB2 V9.7
                          Nicolas Gasparotto
                          Aketi Jyuuzou wrote:
                          umm
                          I will be thinking logic of your solution about 1 day.
                          Create a new ordered and no-hole list of row (the "rn" in my query) which can be used by the "range" of last_value/first_value... apart from that, this is very close to your solution.

                          Well, at least, this is a working solution, and so far, one of the only one :)

                          Nicolas.
                          • 10. Re: emulate "ignore nulls" of Lead of DB2 V9.7
                            Aketi Jyuuzou
                            Hi N. Gasparotto
                            Your solution is nice one.
                            I arranged your solution :-)
                            select SortKey,Val,
                            max(Val) over(order by aboveCnt range between 2 preceding
                                                                      and 2 preceding) as Lag2,
                            max(Val) over(order by belowCnt range between 2 preceding
                                                                      and 2 preceding) as Lead2
                            from (select SortKey,Val,
                                  count(Val) over(order by SortKey rows between unbounded preceding
                                                                            and 1 preceding) aboveCnt,
                                  count(Val) over(order by SortKey rows between 1 following
                                                                            and unbounded following) belowCnt
                                     from W)
                            order by SortKey;
                            
                            SORTKEY   VAL  Lag2  Lead2
                            -------  ----  ----  -----
                                  1     2  null      6
                                  2  null  null      6
                                  5     4  null      5
                                  9  null     2      5
                                 11     6     2      3
                                 12  null     4      3
                                 14  null     4      3
                                 16     5     4      4
                                 17  null     6      4
                                 20     3     6   null
                                 21  null     5   null
                                 22     4     5   null
                            • 11. Re: emulate "ignore nulls" of Lead of DB2 V9.7
                              Nicolas Gasparotto
                              I have to say, in my query the part "...+nvl2(val,2,1) preceding+..." could confuse the developer, yours is much clearer, but longer because of the two Count where I used only one...

                              You could also create your own aggregation function to take in account your needs.

                              Nicolas.
                              • 12. Re: emulate "ignore nulls" of Lead of DB2 V9.7
                                666352
                                Solution with model clause.

                                Regards salim.
                                 
                                SELECT sortkey, val, lead2, lag2
                                  FROM w
                                model
                                dimension by ( row_number()over( order by sortkey  ) rn)
                                measures(case when val is not null then 1 end ind,sortkey,val,
                                         to_number(null) lead2,to_number(null) lag2,
                                         to_number(null) cpt_lead,
                                         to_number(null) cpt_lag )
                                (cpt_lead[any]=sum(ind)[cv()<rn],
                                lead2[rn]   = case when cpt_lead[cv()]>=2 and ind[cv()] is null then lead2[cv()-1]
                                                 when cpt_lead[cv()]>=2 then lead( val,2)over(partition by ind order by sortkey) 
                                                 else 999 end,
                                cpt_lag[rn] =sum(ind)[cv()>rn],
                                lag2[rn]    =  case when cpt_lag[cv()]>=2 then lag( val,2)over(partition by ind order by sortkey) 
                                                   else 999  end,
                                lag2[rn]    =max(lag2)over(partition by cpt_lag) 
                                )
                                /  
                                SQL> select sortkey , val from w;
                                
                                   SORTKEY        VAL
                                ---------- ----------
                                         1          2
                                         2
                                         5          4
                                         9
                                        11          6
                                        12
                                        14
                                        16          5
                                        17
                                        20          3
                                        21
                                        22          4
                                
                                12 ligne(s) sélectionnée(s).
                                
                                SQL> SELECT sortkey, val, lead2, lag2
                                  2    FROM w
                                  3  model
                                  4  dimension by ( row_number()over( order by sortkey  ) rn)
                                  5  measures(case when val is not null then 1 end ind,sortkey,val,
                                  6           to_number(null) lead2,to_number(null) lag2,
                                  7           to_number(null) cpt_lead,
                                  8           to_number(null) cpt_lag )
                                  9  (cpt_lead[any]=sum(ind)[cv()<rn],
                                 10  lead2[rn]   = case when cpt_lead[cv()]>=2 and ind[cv()] is null then lead2[cv()-1]
                                 11                   when cpt_lead[cv()]>=2 then lead( val,2)over(partition by ind order by sortkey
                                ) 
                                 12                   else 999 end,
                                 13  cpt_lag[rn] =sum(ind)[cv()>rn],
                                 14  lag2[rn]    =  case when cpt_lag[cv()]>=2 then lag( val,2)over(partition by ind order by sortke
                                y) 
                                 15                     else 999  end,
                                 16  lag2[rn]    =max(lag2)over(partition by cpt_lag) 
                                 17  )
                                 18  / 
                                
                                   SORTKEY        VAL      LEAD2       LAG2
                                ---------- ---------- ---------- ----------
                                         1          2          6        999
                                         2                     6        999
                                         5          4          5        999
                                         9                     5          2
                                        11          6          3          2
                                        12                     3          4
                                        14                     3          4
                                        16          5          4          4
                                        17                     4          6
                                        20          3        999          6
                                        21                   999          5
                                        22          4        999          5
                                
                                12 ligne(s) sélectionnée(s).
                                
                                SQL>  
                                Edited by: Salim Chelabi on 2009-06-23 10:21
                                1 person found this helpful
                                • 13. Re: emulate "ignore nulls" of Lead of DB2 V9.7
                                  Aketi Jyuuzou
                                  Thanks everyone.
                                  I will study using its.
                                  ;-)
                                  • 14. Re: emulate "ignore nulls" of Lead of DB2 V9.7
                                    Nicolas Gasparotto
                                    Yeah, the MODEL clause is a nice challenge, and the easiest way... depending how you define this word... "easy" to understand ?

                                    Nicolas.
                                    1 2 Previous Next