Invalid Time Literal in Teradata Dynamic SQL

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Invalid Time Literal in Teradata Dynamic SQL

The following stored procedure works fine when I use static sql to insert values into the DBCMNGR.ALERTREQUEST but does not work when trying to use the dynamic sql string.

I get invalid date literal and time literal when trying to call the below stored procedure.

In the DBCMNGR.ALERTREQUEST the DATE and time are defined as follows:

ReqDate DATE FORMAT 'YYYY/MM/DD' ReqTime INTEGER

REPLACE PROCEDURE TESTDB.ALERT_REQUEST_INSERT( IN p_JobName CHARACTER(60), IN p_JobDescription CHARACTER(120), IN p_ActionDestination CHARACTER(120), IN p_JobFullMessage CHARACTER(600) ) ) BEGIN DECLARE SQLERRCODE INTEGER DEFAULT 0; DECLARE SQLERRM VARCHAR(8000); DECLARE ERROR_MESSAGE VARCHAR(255); DECLARE SQLSTR VARCHAR(32000) DEFAULT ''; SET SQLSTR = 'INSERT INTO DBCMNGR.ALERTREQUEST ' || '(AlertRequest.ReqDate ' || ',AlertRequest.ReqTime ' || ',AlertRequest.JobName ' || ',AlertRequest.Description ' || ',AlertRequest.EventValue ' || ',AlertRequest.ActionCode ' || ',AlertRequest.RepeatPeriod ' || ',AlertRequest.Destination ' || ',AlertRequest.Message ' || ') ' || 'VALUES ( ' || ' DATE ' || ',TIME ' || ''',''' || TRIM(p_JobName) || ''',''' || TRIM(p_JobDescription) || ''',0 ' || ',''+'' ' || ',0 ' || ''',''' || TRIM(p_ActionDestination) || ',''' || TRIM(p_JobFullMessage) || ''');'; EXECUTE IMMEDIATE SQLSTR; END; CALL TESTDB.ALERT_REQUEST_INSERT('Test_JobName','Test_JobDescription', 'Test_ActionDestination','Test_JobFullMessage')

5 REPLIES
Enthusiast

Re: Invalid Time Literal in Teradata Dynamic SQL

Need assistance to get this working without any issues. 

 

I get the usual error related to invalid date literal and time literal.

Enthusiast

Re: Invalid Time Literal in Teradata Dynamic SQL

When I call the above stored procedure using the CALL statement below then I get an error message. 

Invalid time and date literal. 

CALL TESTDB.ALERT_REQUEST_INSERT('Test_JobName','Test_JobDescription', 'Test_ActionDestination','Test_JobFullMessage')

Ckp
Enthusiast

Re: Invalid Time Literal in Teradata Dynamic SQL

Can you please share the datatype of the parameters that you have defined in create statement of this procedure ?

Enthusiast

Re: Invalid Time Literal in Teradata Dynamic SQL

The CREATE or REPLACE procedure statement is as shown below ;

 

REPLACE PROCEDURE TESTDB.ALERT_REQUEST_INSERT( IN p_JobName CHARACTER(60), IN p_JobDescription CHARACTER(120), IN p_ActionDestination CHARACTER(120), IN p_JobFullMessage CHARACTER(600) ) )

 

BEGIN

SET SQLSTR = 'INSERT INTO DBCMNGR.ALERTREQUEST ' || '(AlertRequest.ReqDate ' || ',AlertRequest.ReqTime ' || ',AlertRequest.JobName ' || ',AlertRequest.Description ' || ',AlertRequest.EventValue ' || ',AlertRequest.ActionCode ' || ',AlertRequest.RepeatPeriod ' || ',AlertRequest.Destination ' || ',AlertRequest.Message ' || ') ' || 'VALUES ( ' || ' DATE ' || ',TIME ' || ''',''' || TRIM(p_JobName) || ''',''' || TRIM(p_JobDescription) || ''',0 ' || ',''+'' ' || ',0 ' || ''',''' || TRIM(p_ActionDestination) || ',''' || TRIM(p_JobFullMessage) || ''');'; EXECUTE IMMEDIATE SQLSTR; END; CALL TESTDB.ALERT_REQUEST_INSERT('Test_JobName','Test_JobDescription', 'Test_ActionDestination','Test_JobFullMessage')

 

The schema of the Target Table to insert the rows are  :

 

CREATE SET TABLE dbcmngr.AlertRequest ,FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      ReqDate DATE FORMAT 'YYYY/MM/DD',

      ReqTime INTEGER,

      JobName CHAR(60) CHARACTER SET UNICODE NOT CASESPECIFIC,

      Description CHAR(120) CHARACTER SET UNICODE NOT CASESPECIFIC,

      EventValue INTEGER,

      ActionCode CHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC,

      RepeatPeriod SMALLINT,

      Destination CHAR(120) CHARACTER SET UNICODE NOT CASESPECIFIC,

      Message VARCHAR(600) CHARACTER SET UNICODE NOT CASESPECIFIC)

PRIMARY INDEX ( ReqDate ,ReqTime );

Teradata Employee

Re: Invalid Time Literal in Teradata Dynamic SQL

The dynamic string inserts a spurious quote after the TIME built-in function, which makes the parser think it's a time literal: ...VALUES(DATE,TIME',...

|| 'VALUES ( ' || ' DATE ' || ',TIME ' || ''',''' || TRIM(p_JobName)