User defined sql(Stripping off quote char)
Moderators: chulett, rschirm, roy
User defined sql(Stripping off quote char)
Hi
I am using the user defined sql option and i am passing the where clause a parameter to the job.
db is DB2
Select * from db2.datastage where #WHERECLAUSE#
In the above sql the parameter WHERECLAUSE is
date <'2003-10-20'
But when i am passing this to the sql it is coming out as
Select * from db2.datastage where date < 2003-10-20(without the quotes around the date) and the sql fails.
Guys do you know a way of retaining the quote char.
Thanks!
I am using the user defined sql option and i am passing the where clause a parameter to the job.
db is DB2
Select * from db2.datastage where #WHERECLAUSE#
In the above sql the parameter WHERECLAUSE is
date <'2003-10-20'
But when i am passing this to the sql it is coming out as
Select * from db2.datastage where date < 2003-10-20(without the quotes around the date) and the sql fails.
Guys do you know a way of retaining the quote char.
Thanks!
Are you sure that it was a SQL error corresponding to the '. What is the exact SQL error message. Because i used that with an ODBC stage (7.x) and it worked. Also a thing to check is that since you are doing a SELECT *, that you have as many columns defined as the number of columns that this query might return, and also if the column names are defined correctly (for eg: DATE in your query). ...wondering if you actually wanted to use SYSDATE OR if the date format that u used is not what the database understands, maybe u can use a to_Date before the date in quotes.
just my 2 cents..
just my 2 cents..
Just a guess, but have you tried using two single quotes per quote?
date < ''2003-10-20''
Note these aren't double-quotes, but two single quotes at each end of the date. Or something else to 'escape' the quotes so they are retained? If you are sure that is the problem, that is...
date < ''2003-10-20''
Note these aren't double-quotes, but two single quotes at each end of the date. Or something else to 'escape' the quotes so they are retained? If you are sure that is the problem, that is...
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Do you know what escape char to use???chulett wrote:Just a guess, but have you tried using two single quotes per quote?
date < ''2003-10-20''
Note these aren't double-quotes, but two single quotes at each end of the date. Or something else to 'escape' the quotes so they are retained? If you are sure that is the problem, that is...
I generally try to avoid passing in quotes as part of a parameter, instead leaving the quotes as part of the expression - if at all possible. Had issues much like yours early on and haven't done it since when it could be avoided.
I would suggest doing:
PARAM2= '#datevalue#'
and leave the quotes out of your value. That way your date parameter can be set to a type of 'Date', which means it will be a valid date and will be in a consistant format.
I would suggest doing:
PARAM2= '#datevalue#'
and leave the quotes out of your value. That way your date parameter can be set to a type of 'Date', which means it will be a valid date and will be in a consistant format.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Charter Member
- Posts: 88
- Joined: Tue Jan 13, 2004 3:07 pm
Hi All,
I am also facing a similar situation and which to escape the quote character. I tried all tricks like putting two single quotes together...
SELECT * from country where country_code in ( 'USA','RUSSIA')
My user defined query is
SELECT * from country where country_code in #COUNTRY_LIST# and would like to set COUNTRY_LIST to ( 'USA','RUSSIA')
I am also facing a similar situation and which to escape the quote character. I tried all tricks like putting two single quotes together...
I need to pass two values likeI would suggest doing:
PARAM2= '#datevalue#'
and leave the quotes out of your value. That way your date parameter can be set to a type of 'Date', which means it will be a valid date and will be in a consistant format.
SELECT * from country where country_code in ( 'USA','RUSSIA')
My user defined query is
SELECT * from country where country_code in #COUNTRY_LIST# and would like to set COUNTRY_LIST to ( 'USA','RUSSIA')
FYI... There's nothing about your query that requires it to be Custom SQL, it could be easily handled as Column Generated.
You mentioned trying two single quotes, did you try putting double-quotes around each single quote, which is what raju_chvr said worked for him? I don't have any way to check this at the moment, but could tomorrow if no solution presents itself today.
You mentioned trying two single quotes, did you try putting double-quotes around each single quote, which is what raju_chvr said worked for him? I don't have any way to check this at the moment, but could tomorrow if no solution presents itself today.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Charter Member
- Posts: 88
- Joined: Tue Jan 13, 2004 3:07 pm
Ok, we need more information...Inquistive wrote:I need to pass two values like SELECT * from country where country_code in ( 'USA','RUSSIA') My user defined query is
SELECT * from country where country_code in #COUNTRY_LIST#
What version of DataStage are you running? What is your source database and what Stage are you using? What versions of both would probably help as well.
FYI - I do this quite a bit with no issues. It works using DataStage 7.0.1 and the OCI9 stage setup exactly as you've quoted and I don't recall having issues doing this going back to at least 6. Doesn't matter if it is Custom or Column Generated SQL. FWIW, the issues I've had have been with outer quotes around parameters, not quotes inside one. Unless we are talking about an Execute Command stage in a Sequencer, it does seem to have issues and wants to pull all single quotes out of a command from what I recall. However, for a Server job parameter, this should be working fine for you unless (perhaps) there is a bug in your particular version.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Charter Member
- Posts: 88
- Joined: Tue Jan 13, 2004 3:07 pm