In advanced mode, the LEAD function returns data from the following row in an Expression transformation. Use this function to compare values in the current row with values in the following row.
To use the LEAD function, you must configure partition and order keys as window properties and in the Expression transformation.
Syntax
LEAD ( column_name, offset, default )
Argument
Required/
Optional
Description
column_name
Required
The target column or expression that the function operates on.
offset
Required
Integer data type. The number of rows after the current row from which to obtain a value.
default
Optional
The default value to be returned if the offset is outside the bounds of the partition or table. Default is NULL.
You can specify a default argument that is the same data type as the input value or goes with the offset argument.
You cannot specify a default argument that contains a complex data type or a SYSTIMESTAMP argument.
Return Value
The data type of the specified column_name.
Default if the return value is outside the bounds of the specified partition.
NULL if default is omitted or set to NULL.
Example 1
This example shows employee names and hire dates data for use in the LEAD function.
The following table shows employee information:
EMPLOYEE
HIRE_DATE
RETURN VALUE
Hynes
2012/12/07
2014/05/18
Williams
2014/05/18
2015/07/24
Pritchard
2015/07/24
2015/12/24
Snyder
2015/12/24
2016/11/15
Troy
2016/11/15
2017/08/10
Randolph
2017/08/10
NULL
For each employee, the following expression returns the date that the next employee was hired:
LEAD ( HIRE_DATE, 1, NULL )
There is no lead value available for the last row, so the function returns the default value of NULL.
Example 2
This example shows sales quota values for two calendar years to be used in the LEAD function.
You partition the data by year and order by quarter.
The following table shows sales quota data for use in the function:
YEAR
QUARTER
SALES_QUOTA
QUOTA_DIFF
2016
1*
300
7700
2016
2*
7000
0
2016
3
8000
0
2017
1
5000
4000
2017
2
400
0
2017
3
9000
0
*The lead values of the second and third quarter are outside the specified partition, so the function returns a value of "0."
The following expression returns the difference in sales quota values between the first quarter to the third quarter of two calendar years: