From version 11 onwards, the analytic functions LAG and LEAD have the extra option to RESPECT or IGNORE NULLS. This comes in handy when -for example- you don't want a regular next value like this:
rwijk@ORA11GR2> select empno
2 , comm
3 , lead(comm) over (order by empno) next_comm
4 from emp
5 order by empno
6 /
EMPNO COMM NEXT_COMM
---------- ---------- ----------
7369 300
7499 300 500
7521 500
7566 1400
7654 1400
7698
7782
7788
7839 0
7844 0
7876
7900
7902
7934
14 rows selected.
but a next value which ignore nulls, like this:
rwijk@ORA11GR2> select empno
2 , comm
3 , lead(comm ignore nulls) over (order by empno) next_comm
4 from emp
5 order by empno
6 /
EMPNO COMM NEXT_COMM
---------- ---------- ----------
7369 300
7499 300 500
7521 500 1400
7566 1400
7654 1400 0
7698 0
7782 0
7788 0
7839 0
7844 0
7876
7900
7902
7934
14 rows selected.
As you can see the LEAD function is now looking for the next non null value, instead of just the next value. For example, the next_comm value for empno 7521 is not NULL (of empno 7566), but 1400 (of empno 7654).
Last week I attended the ODTUG preview sessions at AMIS, and one of the sessions was Alex Nuijten's session about analytic functions. When he mentioned the new IGNORE NULLS option, he also showed a query how you can simulate the same functionality in versions prior to 11g. That query was a rather lengthy one, so I decided I'd try to come up with a shorter one. And this is the result:
rwijk@ORA11GR2> select empno
2 , comm
3 , max(comm) over (order by grp range between 1 following and 1 following) next_comm
4 from ( select empno
5 , comm
6 , sum(nvl2(comm,1,0)) over (order by empno) grp
7 from emp
8 )
9 order by empno
10 /
EMPNO COMM NEXT_COMM
---------- ---------- ----------
7369 300
7499 300 500
7521 500 1400
7566 1400
7654 1400 0
7698 0
7782 0
7788 0
7839 0
7844 0
7876
7900
7902
7934
14 rows selected.
And posting it here on my blog will surely mean someone will show up and post an even shorter one :-).
For completeness, doing the same for a LAG ... IGNORE NULLS query:
rwijk@ORA11GR2> select empno
2 , comm
3 , lag(comm ignore nulls) over (order by empno) prev_comm
4 from emp
5 order by empno
6 /
EMPNO COMM PREV_COMM
---------- ---------- ----------
7369
7499 300
7521 500 300
7566 500
7654 1400 500
7698 1400
7782 1400
7788 1400
7839 1400
7844 0 1400
7876 0
7900 0
7902 0
7934 0
14 rows selected.
This one can be simulated in versions prior to 11g with the following query:
rwijk@ORA11GR2> select empno
2 , comm
3 , max(comm) over (order by grp range between 1 following and 1 following) prev_comm
4 from ( select empno
5 , comm
6 , sum(nvl2(comm,1,0)) over (order by empno desc) grp
7 from emp
8 )
9 order by empno
10 /
EMPNO COMM PREV_COMM
---------- ---------- ----------
7369
7499 300
7521 500 300
7566 500
7654 1400 500
7698 1400
7782 1400
7788 1400
7839 1400
7844 0 1400
7876 0
7900 0
7902 0
7934 0
14 rows selected.
Well, on your sample data that will work without suquery (you can arrange window in that manner only for offset of 1 though).
ReplyDeleteselect empno,
ename,
job,
mgr,
comm,
lag(comm ignore nulls) over(order by empno) lag_ignore_null,
last_value(comm) over(order by nvl2(comm, empno, empno * 10000)
range between unbounded preceding
and nvl2(comm, empno, empno * 10000) - empno + 1 preceding) lag_ignore_null_virtual
from emp e
order by empno;
Best regards
Maxim
Thanks, Maxim.
ReplyDeleteThat's an ingenious way of splitting the null and non null values in your window!
Nice. I didn't know about 11g's feature of "ignore null" option... this post did the majic for me.
ReplyDeleteThank you for sharing!!
Hello,
ReplyDeleteit works fine as select but when I want use this in the procedure I get an error: "Found: 'LAG' Only FIRST_VALUE and LAST_VALUE are valid with IGNORE NULLS".
Do you have any solution for this?
Thank you for sharing
Thanks.. this will solve my problem until we upgrade...
ReplyDeleteWe Need Not InLineView Since Oracle10g.
ReplyDeleteBecause First_Value and Last_Value Support "ignore nulls".
select empno,comm,
Lead(comm ignore nulls) over (order by empno) as next_comm1,
First_Value(comm ignore nulls)
over(order by empno Rows BetWeen 1 Following
and Unbounded Following) as next_comm2,
Lag(comm ignore nulls) over (order by empno) as Prev_comm1,
Last_Value(comm ignore nulls)
over(order by empno Rows BetWeen Unbounded Preceding
and 1 Preceding) as Prev_comm2
from scott.emp
order by empno;