Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

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

Aketi JyuuzouJun 22 2009 — edited Sep 8 2009
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?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 6 2009
Added on Jun 22 2009
16 comments
6,679 views