All Forums Database
Koushik Chnadra 27 posts Joined 05/08
03 Feb 2009
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-95Khoo 18-MAY-95 24-JUL-97Tobias 24-JUL-97 24-DEC-97Baida 24-DEC-97 15-NOV-98Himuro 15-NOV-98 10-AUG-99Colmenares 10-AUG-99

Adeel Chaudhry 773 posts Joined 04/08
03 Feb 2009

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 RHDFROM Table1)aLEFT OUTER JOIN(SELECT Last_Name, Hire_Date, RANK(Hire_Date ASC) - 1 AS RHDFROM Table1)bON a.RHD = b.RHDORDER BY a.Last_Name, a.Hire_Date;HTH.Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

dnoeth 4628 posts Joined 11/04
03 Feb 2009

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

Dieter

Adeel Chaudhry 773 posts Joined 04/08
03 Feb 2009

Great! :)

-- If you are stuck at something .... consider it an opportunity to think anew.

ahmedbintariq 1 post Joined 07/12
27 Sep 2012

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.

You must sign in to leave a comment.