USING DDMMYYYY INSIDE DBC.SYSEXECSQL() in Stored Procedure

General
Enthusiast

USING DDMMYYYY INSIDE DBC.SYSEXECSQL() in Stored Procedure

When i execute the below code in a STORED PROCEDURE i get an syntax error saying "Expects somthing like an EXCEPT keyword UNION keyword or MINUS keyword between a between a String and YYYYMMDD". Please help me fix this syntax error. Thanks.

Replace Procedure Sample_Proc(

BEGIN

DBC.SYSEXECSQL('Select CAST( (Event_Date (FORMAT 'YYYYMMDD') ) AS  CHAR(10) ) AS EVNT_DATE;');

END

);

Regards

Arun

7 REPLIES
Senior Apprentice

Re: USING DDMMYYYY INSIDE DBC.SYSEXECSQL() in Stored Procedure

Hi Arun,

you pass a string to SysExecSQL, so you need double any single quote within your string:

CALL DBC.SYSEXECSQL('Select CAST( (Event_Date (FORMAT ''YYYYMMDD'') ) AS  CHAR(10) ) AS EVNT_DATE;';
Enthusiast

Re: USING DDMMYYYY INSIDE DBC.SYSEXECSQL() in Stored Procedure

Thanks a lot Deiter. But when i use LIKE '%str%' in statements as below i am unable to execute this. Here i am passing Teradata as a string and using it in the procedure. Please help me how to handle LIKE and % here.

 

CALL DB.proc('Teradata');

 

Replace DB.proc

(IN str VARCHAR(20)

)

BEGIN

DBC.SYSEXECSQL('Select * FROM DB.Table_Name where brand LIKE ''''%'||str||'%'''' ;' ) AS EVNT_DATE;');

END

Senior Apprentice

Re: USING DDMMYYYY INSIDE DBC.SYSEXECSQL() in Stored Procedure

Hi Arun,

there are too many quotes:

SELECT 'teradata' AS str,
'Select * FROM DB.Table_Name where brand LIKE ''''%'||str||'%'''' ; ) AS EVNT_DATE;'

results in:
Select * FROM DB.Table_Name where brand LIKE ''%teradata%'' ; ) AS EVNT_DATE;

The result of the previous query must be valid Teradata SQL

Enthusiast

Re: USING DDMMYYYY INSIDE DBC.SYSEXECSQL() in Stored Procedure

HI Dieter,

 Thanks for the reply. But when i pass this in procedure as above, i get an error saying "% is not a valid SQL Token". I am in a fix. I have put single quote additionally for every single quote.

Also the ouput has to be Select * FROM DB.Table_Name where brand LIKE '%teradata%' ; --(Only Single quote)

Please help.

CALL DB.proc('Teradata');

 

Replace DB.proc

(IN str VARCHAR(20)

)

BEGIN

DBC.SYSEXECSQL('Select * FROM DB.Table_Name where brand LIKE ''''%'||str||'%'''' ;' );

END

Senior Apprentice

Re: USING DDMMYYYY INSIDE DBC.SYSEXECSQL() in Stored Procedure

Simply change the code to get the expected result:

SELECT 'teradata' AS str,
'Select * FROM DB.Table_Name where brand LIKE ''%'||str||'%'' ;'
Enthusiast

Re: USING DDMMYYYY INSIDE DBC.SYSEXECSQL() in Stored Procedure

Thanks a lot Dieter.. It solved the issue.

Enthusiast

Re: USING DDMMYYYY INSIDE DBC.SYSEXECSQL() in Stored Procedure

Thank you Dieter. Escaping single quotes issue in SP is solved for me too.

Sujatha.