Syntax error due to Stored Proc Parameter

Analytics
Enthusiast

Syntax error due to Stored Proc Parameter

The following procedure can be called with no errors when the Procedure input parameter is not used in the WHERE clause, but gives the following Syntax error when the input parameter is used:

3706: Syntax error: expected something between a string or a Unicode character literal and the 'AT' keyword.

Can someone help?

REPLACE PROCEDURE Test(IN Months INTEGER)

SQL SECURITY INVOKER

BEGIN

CREATE VOLATILE TABLE Tbl2 AS (

SELECT

CAST(CAST(((Completed_Dt AT TIME ZONE 'gmt') AT TIME ZONE 'Australia Eastern') AS VARCHAR(10)) AS DATE) AS ActivityDate

FROM Tbl1

WHERE ActivityDate BETWEEN ADD_MONTHS(DATE - EXTRACT(DAY FROM DATE) + 1, -Months) AND DATE --Get Activations from start of prior month to current date

) WITH DATA

ON COMMIT PRESERVE ROWS;

END;

8 REPLIES
Enthusiast

Re: Syntax error due to Stored Proc Parameter

To work around this, I've changed the Create statement to a string and execute it via dbc.SysExecSQL().

Not ideal though.

I'd also be happy to learn of a more efficient method to convert a UTC timestamp to output in other timezone rather than the CAST method I'm using:

CAST(CAST(((Completed_Dt AT TIME ZONE 'gmt') AT TIME ZONE 'Australia Eastern') AS VARCHAR(10)) AS DATE) AS ActivityDate

Supporter

Re: Syntax error due to Stored Proc Parameter

Hi Zkuza,

 

Can you please share some data of the source table Tbl1 with the table structure?

 

Thanks,

Rohan Sawant

Junior Contributor

Re: Syntax error due to Stored Proc Parameter

When you check DBQL you'll find that a closing bracket is removed from the SQL code submitted within the SP, seems to be a strange parser bug.

What are you trying to do wtih all those CASTs?

Can you show the datatype of Completed_Dt, some example data and the expected result? 

Enthusiast

Re: Syntax error due to Stored Proc Parameter

Our data warehouse has timestamp data stored in UTC so I like to convert timestamps to AEST/AEDT as soon as possible in my scripts to avoid misinterpretation as some of these UTC timestamp fields would then need to be joined to date fields which are in AEST/AEDT timezone.

Possibly a misunderstanding of how timestamp functions work in Teradata on my part, I would appreciate any assistance you can give me in this regard.

Completed_Dt is TIMESTAMP(6).

Example data would be:

'2014-11-31 12:34:56+00:00'

'2014-11-31 13:34:56+00:00'

The above proc, when passing in the vaule 1, would would return the data for December and January using Australian Eastern timezone.

Therefore the second row would be returned as '2014-11-31 13:34:56+00:00' is '2014-12-01 00:34:56+11:00'. The first row would not be returned.

Junior Contributor

Re: Syntax error due to Stored Proc Parameter

This should work, assuming there's no nov. 31 down under und it should december instead :-)

SELECT
TIMESTAMP '2014-12-31 13:34:56+00:00' AS Completed_Dt,
Completed_Dt AT 'Australia Eastern',
CAST(Completed_Dt AS DATE AT 'Australia Eastern');

*** Query completed. One row found. 3 columns returned.

Completed_Dt Completed_Dt AT TIME ZONE 'Australia Eastern' Completed_Dt
------------------------- --------------------------------------------- ------------
2014-12-31 13:34:56+00:00 2015-01-01 00:34:56+11:00 15/01/01
Enthusiast

Re: Syntax error due to Stored Proc Parameter

Thanks Dieter. Really appreciate your help - I have revised the stored procedure with "cast as date at 'Australia Eastern' and no longer get the syntax error.

Enthusiast

Re: Syntax error due to Stored Proc Parameter

One last question: Is this 11am Australian time or midnight Australian time:

SELECT CAST('2015-01-10 00:00:00' AS TIMESTAMP(0)) AT 'Australia Eastern'

Result: 10/01/2015 11:00:00+11:00

If 11am, what is the most efficient SQL for generating a timezone specific timestamp representing midnight in that timezone?

Junior Contributor

Re: Syntax error due to Stored Proc Parameter

Time zones are always hard to deal with :-)

Additionally different systems might return different results based on some internal settings...

The easiest way should be changing the session time zone to 'Australia Eastern'. 

--Either permanently
MODIFY USER xxx AS TIME ZONE = 'Australia Eastern';
--or on a session level
SET TIME ZONE 'Australia Eastern';

Then it's a simple 

SELECT TIMESTAMP '2015-06-10 00:00:00' AT LOCAL;

This should be safe in a Stored Proc, as it will always use the current time zone setting when it was created. So other sessions might have different time zone, but the SP still uses 'Australia Eastern'.