All Forums General
larun1616 29 posts Joined 09/14
01 Sep 2014
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

dnoeth 4628 posts Joined 11/04
01 Sep 2014

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;';

 

Dieter

larun1616 29 posts Joined 09/14
03 Sep 2014

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

dnoeth 4628 posts Joined 11/04
03 Sep 2014

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

Dieter

larun1616 29 posts Joined 09/14
04 Sep 2014

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

dnoeth 4628 posts Joined 11/04
04 Sep 2014

Simply change the code to get the expected result:

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

 

Dieter

larun1616 29 posts Joined 09/14
15 Sep 2014

Thanks a lot Dieter.. It solved the issue.

sujjichalla 8 posts Joined 11/10
18 Sep 2014

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

You must sign in to leave a comment.