Translate Oracle Syntax to Teradata

Analytics
Fan

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_NAME

The columns on the table are shown in the attached excel file with the output of the query in Oracle.

Thanks for your time.

Regards,

1 REPLY
Teradata Employee

Re: Translate Oracle Syntax to Teradata

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