All Forums Analytics
WALM 2 posts Joined 10/08
11 Nov 2008
Translate Oracle Syntax to Teradata

Hi Guys,I need help in translating the syntax below from Oracle to Teradata. Any assistance would be appreciated. My knowledge of sql especially Teradata functions is basic. select distinct to_char('20' ||case when to_char(.PAY_PERIOD_START_DATE,'MM' ) < 04 then LPAD((to_number(to_char(.PAY_PERIOD_START_DATE,'YY'))- 1),2,'0')else LPAD(to_number(to_char(.PAY_PERIOD_START_DATE,'YY')) ,2,'0') end) ||'/'|| to_char('20' ||LPAD((to_char(to_number(case when to_char(.PAY_PERIOD_START_DATE,'MM' ) < 04 then LPAD((to_number(to_char(.PAY_PERIOD_START_DATE,'YY'))- 1),2,'0') else LPAD(to_number(to_char(.PAY_PERIOD_START_DATE,'YY')) ,2,'0') end))+1),2,'0')) ||' Period ' || LPAD(.PAY_PERIOD_NUM,2,'0' )||' '||to_char(.PAY_PERIOD_START_DATE, 'Mon') PAY_PERIOD_NAMEThe columns on the table are shown in the attached excel file with the output of the query in Oracle.Thanks for your time.Regards,

Adeel Chaudhry 773 posts Joined 04/08
12 Nov 2008

Hello,Following is your solution, next time try doing it yourself, and ask what issue you are facing, not for the solution itself:DROP TABLE table1;CREATE VOLATILE TABLE Table1 (Pay_Period_Num INT, PAY_PERIOD_START_DATE DATE FORMAT'DD-MM-YYYY', PAY_PERIOD_NAME VARCHAR(30)) ON COMMIT PRESERVE ROWS;INSERT table1 VALUES(1,'01-04-2007','1 2007 Calendar Month');INSERT table1 VALUES(2,'01-05-2007','2 2007 Calendar Month');INSERT table1 VALUES(3,'01-06-2007','3 2007 Calendar Month');INSERT table1 VALUES(4,'01-07-2007','4 2007 Calendar Month');INSERT table1 VALUES(5,'01-08-2007','5 2007 Calendar Month');INSERT table1 VALUES(6,'01-09-2007','6 2007 Calendar Month');INSERT table1 VALUES(7,'01-10-2007','7 2007 Calendar Month');INSERT table1 VALUES(8,'01-11-2007','8 2007 Calendar Month');INSERT table1 VALUES(9,'01-12-2007','9 2007 Calendar Month');INSERT table1 VALUES(10,'01-01-2008','10 2008 Calendar Month');INSERT table1 VALUES(11,'01-02-2008','11 2008 Calendar Month');INSERT table1 VALUES(12,'01-03-2008','12 2008 Calendar Month');INSERT table1 VALUES(1,'01-04-2008','1 2008 Calendar Month');INSERT table1 VALUES(2,'01-05-2008','2 2008 Calendar Month');INSERT table1 VALUES(3,'01-06-2008','3 2008 Calendar Month');INSERT table1 VALUES(4,'01-07-2008','4 2008 Calendar Month');INSERT table1 VALUES(5,'01-08-2008','5 2008 Calendar Month');INSERT table1 VALUES(6,'01-09-2008','6 2008 Calendar Month');INSERT table1 VALUES(7,'01-10-2008','7 2008 Calendar Month');INSERT table1 VALUES(8,'01-11-2008','8 2008 Calendar Month');INSERT table1 VALUES(9,'01-12-2008','9 2008 Calendar Month');INSERT table1 VALUES(10,'01-01-2009','10 2009 Calendar Month');INSERT table1 VALUES(11,'01-02-2009','11 2009 Calendar Month');INSERT table1 VALUES(12,'01-03-2009','12 2009 Calendar Month');INSERT table1 VALUES(1,'01-04-2009','1 2009 Calendar Month');INSERT table1 VALUES(2,'01-05-2009','2 2009 Calendar Month');INSERT table1 VALUES(3,'01-06-2009','3 2009 Calendar Month');INSERT table1 VALUES(4,'01-07-2009','4 2009 Calendar Month');INSERT table1 VALUES(5,'01-08-2009','5 2009 Calendar Month');INSERT table1 VALUES(6,'01-09-2009','6 2009 Calendar Month');INSERT table1 VALUES(7,'01-10-2009','7 2009 Calendar Month');INSERT table1 VALUES(8,'01-11-2009','8 2009 Calendar Month');INSERT table1 VALUES(9,'01-12-2009','9 2009 Calendar Month');INSERT table1 VALUES(10,'01-01-2010','10 2010 Calendar Month');INSERT table1 VALUES(11,'01-02-2010','11 2010 Calendar Month');INSERT table1 VALUES(12,'01-03-2010','12 2010 Calendar Month');INSERT table1 VALUES(1,'01-04-2010','1 2010 Calendar Month');INSERT table1 VALUES(2,'01-05-2010','2 2010 Calendar Month');INSERT table1 VALUES(3,'01-06-2010','3 2010 Calendar Month');INSERT table1 VALUES(4,'01-07-2010','4 2010 Calendar Month');INSERT table1 VALUES(5,'01-08-2010','5 2010 Calendar Month');INSERT table1 VALUES(6,'01-09-2010','6 2010 Calendar Month');INSERT table1 VALUES(7,'01-10-2010','7 2010 Calendar Month');INSERT table1 VALUES(8,'01-11-2010','8 2010 Calendar Month');INSERT table1 VALUES(9,'01-12-2010','9 2010 Calendar Month');INSERT table1 VALUES(10,'01-01-2011','10 2011 Calendar Month');INSERT table1 VALUES(11,'01-02-2011','11 2011 Calendar Month');INSERT table1 VALUES(12,'01-03-2011','12 2011 Calendar Month');INSERT table1 VALUES(1,'01-04-2011','1 2011 Calendar Month');INSERT table1 VALUES(2,'01-05-2011','2 2011 Calendar Month');INSERT table1 VALUES(3,'01-06-2011','3 2011 Calendar Month');INSERT table1 VALUES(4,'01-07-2011','4 2011 Calendar Month');INSERT table1 VALUES(5,'01-08-2011','5 2011 Calendar Month');INSERT table1 VALUES(6,'01-09-2011','6 2011 Calendar Month');INSERT table1 VALUES(7,'01-10-2011','7 2011 Calendar Month');INSERT table1 VALUES(8,'01-11-2011','8 2011 Calendar Month');INSERT table1 VALUES(9,'01-12-2011','9 2011 Calendar Month');INSERT table1 VALUES(10,'01-01-2012','10 2012 Calendar Month');INSERT table1 VALUES(11,'01-02-2012','11 2012 Calendar Month');INSERT table1 VALUES(12,'01-03-2012','12 2012 Calendar Month');INSERT table1 VALUES(-99999,'01-04-2012','1 2012 Calendar Month');INSERT table1 VALUES(1,'01-05-2012','2 2012 Calendar Month');INSERT table1 VALUES(2,'01-06-2012','3 2012 Calendar Month');INSERT table1 VALUES(3,'01-07-2012','4 2012 Calendar Month');INSERT table1 VALUES(4,'01-08-2012','5 2012 Calendar Month');SELECT Pay_Period_Num, Pay_Period_Start_Date, Pay_Period_Name, CAST(EXTRACT(YEAR FROM ADD_MONTHS(Pay_Period_Start_Date, -3)) AS VARCHAR(4)) || '/' ||CAST(EXTRACT(YEAR FROM ADD_MONTHS(Pay_Period_Start_Date, -3)) + 1 AS VARCHAR(4)) ||' Period ' ||CASE WHEN Pay_Period_Num 0 THEN '0' ELSE '' END || CAST(Pay_Period_Num AS VARCHAR(2)) || ' ' ||SUBSTR(CAST(CAST(Pay_period_start_date AS DATE FORMAT'DD-MMM-YYYY') AS VARCHAR(12)), 4,3) AS "Expected SQL Output"FROM table1 ORDER BY 2,1,3;Regards,Adeel

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

You must sign in to leave a comment.