SP: Timestamp as Host variable

[TSchmolzi]

I'm writing a stored procedure to move the data from the DBQL tables to another table for storage.
I'm using three variable to allow me to separate the logs by year.
This is the first Stored Procedure I've ever written and I am fairly new to Teradata as well.
Here is the definition of the SP:

CREATE PROCEDURE mydatabase.Proc1
(
arc_year char(4)
,tstamp1 TIMESTAMP
,tstamp2 TIMESTAMP
)
BEGIN
DECLARE table_year CHAR(4);
DECLARE stamp1 CHAR(19);
DECLARE stamp2 CHAR(19);

SET table_year = arc_year;
SET stamp1 = tstamp1;
SET stamp2 = tstamp2;

call dbc.sysexecsql('INSERT INTO logarchive.TB_DBQLObjTbl_' || table_year ||
' SELECT * FROM DBC.DBQLObjTbl WHERE CollectTimeStamp BETWEEN TIMESTAMP ' || stamp1 || ' AND TIMESTAMP ' || stamp2 || ';');
END;

It seems pretty straight forward to me, but when I execute the SP within SQL Assistant it blows off with a 3707.
Here's my execution call.

call mydatabase.Proc1 ('2007','2007-01-01 00:00:00','2007-12-31 11:59:59')

Are Timestamps not allowed as host variables?

Any help would be greatly appreciated.

Thanks,

Tim Schmolzi
Missouri Office of Administration
ITSD-DOR Database

[NetFx]

Did you try this?

call dbc.sysexecsql('INSERT INTO logarchive.TB_DBQLObjTbl_' || table_year ||
' SELECT * FROM DBC.DBQLObjTbl WHERE CollectTimeStamp BETWEEN TIMESTAMP TIMETSTAMP'' ' || stamp1 || ''' AND TIMESTAMP TIMESTAMP'' ' || stamp2 || ''';');

In another word, TIMESTAMP Literal syntax is

TIMESTAMP'x'

It starts with TIMESTAMP, single quote, timestamp value YYYY-MM-DD hh:mi:ss and followed by another single quote. I assume

[NetFx]

There is an extra space in my previous post; It should be

call dbc.sysexecsql('INSERT INTO logarchive.TB_DBQLObjTbl_' || table_year ||
' SELECT * FROM DBC.DBQLObjTbl WHERE CollectTimeStamp BETWEEN TIMESTAMP TIMETSTAMP''' || stamp1 || ''' AND TIMESTAMP TIMESTAMP''' || stamp2 || ''';');

[TSchmolzi]

Thanks for the idea, but sadly it didn't work. Here is the message it returned:

3707: SP_LoadDBQLogTbl:Syntax error, expected something like a string between the 'TIMESTAMP' keyword and the word 'TIMETSTAMP'.
Output directed to Answerset window

My execution was:

call database.SP_LoadDBQLogTbl ('01_Jan','2008-01-01 00:00:00','2008-01-31 23:59:59')

Thanks for any additional help.

Tim

[dnoeth]

Hi Tim,
too many TIMESTAMPs:

call dbc.sysexecsql('INSERT INTO logarchive.TB_DBQLObjTbl_' || table_year ||
' SELECT * FROM DBC.DBQLObjTbl WHERE CollectTimeStamp BETWEEN TIMESTAMP ''' || stamp1 || ''' AND TIMESTAMP ''' || stamp2 || ''';');

Btw, if you assign the string to an output parameter, you can easily spot those syntax errors.

Dieter

[NetFx]

Sorry for the typos; as Dieter mentioned already there is one too many TIMESTAMP keyword.

[TSchmolzi]

Thank you guys. You have been a great help. I found out that one of my problems was that I wasn't passing the TIMESTAMP all of the precision that it was expecting. Of course my other problem was my syntax. You guys are the BOMB!

-Tim