As most of us know, with LAG and LEAD or more generally, any analytic function that may extend “past” the boundary of window it is operating on, you can get null as a result.
Here’s a trivial example
SQL> create table t as
2 select rownum x
3 from dual
4 connect by level <= 10;
Table created.
SQL>
SQL> select x, lag(x) over ( order by x ) as lag_Test
2 from t;
X LAG_TEST
---------- ----------
1
2 1
3 2
4 3
5 4
6 5
7 6
8 7
9 8
10 9
10 rows selected.
We get null for the first row, because we cannot lag “below” x=1 because there is no such value. That is of course trivially solved with an NVL, for example:
SQL> select x, nvl(lag(x) over ( order by x ),999) as lag_Test
2 from t;
X LAG_TEST
---------- ----------
1 999
2 1
3 2
4 3
5 4
6 5
7 6
8 7
9 8
10 9
10 rows selected.
But a quick look at the documentation, shows that you don’t need the “clutter” of NVL, because lag, lead already provide for a default value.
SQL> select x, lag(x,1,999) over ( order by x ) as lag_Test
2 from t;
X LAG_TEST
---------- ----------
1 999
2 1
3 2
4 3
5 4
6 5
7 6
8 7
9 8
10 9
10 rows selected.
SQL>
Learning is not a spectator sport 
Leave a reply to Alex Nuijten Cancel reply