Retaining single quotes in passed parameters?
Posted: Wed Nov 12, 2008 12:35 pm
I'm guessing there has to be a simple answer for this, but after looking through the 8.0 documentation and going buggy-eyed from forum searches, I'm not finding anything.
In a nutshell, I want to pass SQL as an SQLPARM to a job, and have that show up in an odbc stage in the job in the select statement as #SQLPARM#.
The problem is I want to pass "Select to_char(APPCT_DATE,'YYYYMMDD') from X22.TABLE_NAME". If I look at the first entry in the job log, this is indeed getting passed to the job. But then Oracle dumps an error about bad sql, and displays "Select to_char(APPCT_DATE,YYYYMMDD) from X22.TABLE_NAME". So it seems by the time DataStage passes that parm to the actual stage, the single quotes have been interpreted as metacharacters, resulting in the bad sql syntax.
Any idea how I can retain them? In DS 7.5 preceding the single quote with a backslash ("\") did the trick. Not so in 8.0. We've tried every combination of single and double quotes that you can imagine, all to no avail. We tried putting quotes around the "#SQLPARM# - no luck.
Any clues, or suggestions?
Thanks! -> Richard
P.S. Before you ask, the reason we're not doing the reformatting in DataStage is because we're trying to use the same DS job for many many different data dumps, as not to have to maintain many distinct DS jobs.
In a nutshell, I want to pass SQL as an SQLPARM to a job, and have that show up in an odbc stage in the job in the select statement as #SQLPARM#.
The problem is I want to pass "Select to_char(APPCT_DATE,'YYYYMMDD') from X22.TABLE_NAME". If I look at the first entry in the job log, this is indeed getting passed to the job. But then Oracle dumps an error about bad sql, and displays "Select to_char(APPCT_DATE,YYYYMMDD) from X22.TABLE_NAME". So it seems by the time DataStage passes that parm to the actual stage, the single quotes have been interpreted as metacharacters, resulting in the bad sql syntax.
Any idea how I can retain them? In DS 7.5 preceding the single quote with a backslash ("\") did the trick. Not so in 8.0. We've tried every combination of single and double quotes that you can imagine, all to no avail. We tried putting quotes around the "#SQLPARM# - no luck.
Any clues, or suggestions?
Thanks! -> Richard
P.S. Before you ask, the reason we're not doing the reformatting in DataStage is because we're trying to use the same DS job for many many different data dumps, as not to have to maintain many distinct DS jobs.