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?