Communities
A collaborative platform to connect and grow with like-minded Informaticans across the globe
Product Communities
Connect and collaborate with Informatica experts and champions
Discussions
Have a question? Start a Discussion and get immediate answers you are looking for
User Groups
Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
Get Started
Community Guidelines
Knowledge Center
Troubleshooting documents, product guides, how to videos, best practices, and more
Knowledge Base
One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
Support TV
Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
Documentation
Information library of the latest product documents
Velocity (Best Practices)
Best practices and use cases from the Implementation team
Learn
Rich resources to help you leverage full capabilities of our products
Trainings
Role-based training programs for the best ROI
Certifications
Get certified on Informatica products. Free, Foundation, or Professional
Product Learning Paths
Free and unlimited modules based on your expertise level and journey
Resources
Library of content to help you leverage the best of Informatica products
Tech Tuesdays Webinars
Most popular webinars on product architecture, best practices, and more
Product Availability Matrix
Product Availability Matrix statements of Informatica products
SupportFlash
Monthly support newsletter
Support Documents
Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
Product Lifecycle
End of Life statements of Informatica products
Ideas
Events
Change Request Tracking
Marketplace
English
  • Data Integration
  • All Products

Table of Contents

Search

  1. Preface
  2. Function reference
  3. Constants
  4. Operators
  5. Dates
  6. Functions
  7. System variables
  8. Datatype reference

Function Reference

Function Reference

Back Next

LEAD

LEAD

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
 )
The following table describes the arguments for this command:
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: 
LEAD ( Sales_Quota, 2, 0 ) - Sales_Quota
0 COMMENTS
We’d like to hear from you!