Example: LEAD with IGNORE NULLS - Analytics Database - Teradata Vantage

SQL Functions, Expressions, and Predicates

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-01-12
dita:mapPath
obm1628111499646.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
kby1472250656485
lifecycle
latest
Product Category
Teradata Vantageā„¢

ANSI style syntax

SELECT empno, empname, job, sal,
       LEAD(sal, 1, 0) IGNORE NULLS
       OVER (PARTITION BY job ORDER BY empno) AS sal_next
FROM   emp
ORDER BY job, empno;

Result:

 EMPNO      EMPNAME      JOB       SAL      SAL_NEXT
---------- ---------- --------- ---------- ----------
   12        PAUL       ANALYST    ?          3000
   13        GRACE      ANALYST    3000       0
   1         JOHN       CLERK      800        950
   2         ERIC       CLERK      950        1300
   3         KURT       CLERK      ?          1300
   6         JULIE      CLERK      1300       0
   9         NICHOLAS   MANAGER    2450       2850
   10        NOVAK      MANAGER    ?          2850
   11        ROGER      MANAGER    2850       0
   14        RICH       PRESIDENT  5000       0
   4         KENT       SALESMAN   1250       1500
   5         LYNN       SALESMAN   ?          1500
   7         TERESA     SALESMAN   1500       1600
   8         MATTHEW    SALESMAN   1600       0

Teradata style syntax

SELECT empno, empname, job, sal,
       LEAD(sal IGNORE NULLS, 1, 0) 
       OVER (PARTITION BY job ORDER BY empno) AS sal_next
FROM   emp
ORDER BY job, empno;

Result:

 EMPNO      EMPNAME      JOB       SAL      SAL_NEXT
---------- ---------- --------- ---------- ----------
   12        PAUL       ANALYST    ?          3000
   13        GRACE      ANALYST    3000       0
   1         JOHN       CLERK      800        950
   2         ERIC       CLERK      950        1300
   3         KURT       CLERK      ?          1300
   6         JULIE      CLERK      1300       0
   9         NICHOLAS   MANAGER    2450       2850
   10        NOVAK      MANAGER    ?          2850
   11        ROGER      MANAGER    2850       0
   14        RICH       PRESIDENT  5000       0
   4         KENT       SALESMAN   1250       1500
   5         LYNN       SALESMAN   ?          1500
   7         TERESA     SALESMAN   1500       1600
   8         MATTHEW    SALESMAN   1600       0