About Oracle: Simulating LAG/LEAD IGNORE NULLS

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.

6 comments:

MaximJune 15, 2010 at 11:47 PM
Well, on your sample data that will work without suquery (you can arrange window in that manner only for offset of 1 though).

select 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
Rob van WijkJune 16, 2010 at 12:49 PM
Thanks, Maxim.
That's an ingenious way of splitting the null and non null values in your window!
UnknownSeptember 30, 2010 at 11:12 PM
Nice. I didn't know about 11g's feature of "ignore null" option... this post did the majic for me.

Thank you for sharing!!
abcdefOctober 18, 2011 at 12:11 PM
Hello,

it 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
mike pazdaAugust 1, 2014 at 4:05 PM
Thanks.. this will solve my problem until we upgrade...
UnknownMay 2, 2016 at 10:12 AM
We Need Not InLineView Since Oracle10g.
Because 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;

About Oracle

Pages

Tuesday, June 15, 2010

Simulating LAG/LEAD IGNORE NULLS

6 comments: