Regarding LEAD (analytical function) function in TERADATA

Database

Regarding LEAD (analytical function) function in TERADATA

Hi,

Can you please let me know how the following functionality can be handeled in Teradata, as I haven't found that there is no LEAD function in teradata.

SELECT last_name, hire_date,
LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS "NextHired"
FROM employees WHERE department_id = 30;

LAST_NAME HIRE_DATE NextHired
--------------------- --------- ---------
Raphaely 07-DEC-94 18-MAY-95
Khoo 18-MAY-95 24-JUL-97
Tobias 24-JUL-97 24-DEC-97
Baida 24-DEC-97 15-NOV-98
Himuro 15-NOV-98 10-AUG-99
Colmenares 10-AUG-99
4 REPLIES
Teradata Employee

Re: Regarding LEAD (analytical function) function in TERADATA

Hello,

No, so far there is no equivalent function in Teradata, though you can do that as follows:

DROP TABLE Table1;

CREATE VOLATILE TABLE Table1 (Last_Name VARCHAR(255), Hire_Date DATE) ON COMMIT PRESERVE ROWS;

INSERT Table1 VALUES('Baida', '1997-12-24');
INSERT Table1 VALUES('Colmenares', '1999-08-10');
INSERT Table1 VALUES('Himuro', '1998-11-15');
INSERT Table1 VALUES('Khoo', '1995-05-18');
INSERT Table1 VALUES('Raphaely', '1994-12-07');
INSERT Table1 VALUES('Tobias', '1997-07-24');

SELECT a.Last_Name, a.Hire_Date, b.Hire_Date FROM
(
SELECT Last_Name, Hire_Date, RANK(Hire_Date ASC) AS RHD
FROM Table1
)a
LEFT OUTER JOIN
(
SELECT Last_Name, Hire_Date, RANK(Hire_Date ASC) - 1 AS RHD
FROM Table1
)b
ON a.RHD = b.RHD
ORDER BY a.Last_Name, a.Hire_Date;

HTH.

Regards,

Adeel
N/A

Re: Regarding LEAD (analytical function) function in TERADATA

Hi Koushik,
it's easy to rewrite LEAD/LAG:
SELECT last_name, hire_date,
--LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS "NextHired"
MIN(hire_date) OVER (ORDER BY hire_date rows between 1 following and 1 following) AS "NextHired"
FROM employees WHERE department_id = 30;

Dieter
Teradata Employee

Re: Regarding LEAD (analytical function) function in TERADATA

Great! :)

Re: Regarding LEAD (analytical function) function in TERADATA

This was very useful, my colleagues call this a sliding window joint and had written a 30 line code to make this happen. Extremely useful.