User defined sql(Stripping off quote char)

deep · Post by **deep** » Thu Feb 05, 2004 12:09 pm

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!

jreddy · Post by **jreddy** » Thu Feb 05, 2004 1:30 pm

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..

chulett · Post by **chulett** » Thu Feb 05, 2004 1:39 pm

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...

deep · Post by **deep** » Thu Feb 05, 2004 2:23 pm

I tried two single quotes ..,it removes both of them.
The querry works fine in db2 command center.

deep · Post by **deep** » Thu Feb 05, 2004 2:27 pm

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...

Do you know what escape char to use???

chulett · Post by **chulett** » Thu Feb 05, 2004 2:50 pm

No, sorry.

Have you tried putting two double-quotes around each single quote? Or possible a backslash (\) in front of each? Again, guessing...

raju_chvr · Post by **raju_chvr** » Thu Feb 05, 2004 2:59 pm

The double quotes around the single quote worked for me !

deep · Post by **deep** » Thu Feb 05, 2004 4:42 pm

I sent the date part as a separate parameter and it worked.
I think datastage does not like
PARAMVALUE=A.date < '2003-10-10'
but it is okay with
PARAM1= A.date<
PARAM2= #datevalue#

Thanks

chulett · Post by **chulett** » Thu Feb 05, 2004 5:12 pm

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.

deep · Post by **deep** » Thu Feb 05, 2004 5:25 pm

Yeah..,Thazz wat i did..,But dont think its a bug.
There should be a way to pass quote(') right!!

Inquisitive · Post by **Inquisitive** » Sun Feb 29, 2004 8:15 am

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...

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 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# and would like to set COUNTRY_LIST to ( 'USA','RUSSIA')

chulett · Post by **chulett** » Sun Feb 29, 2004 9:18 am

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.

Inquisitive · Post by **Inquisitive** » Mon Mar 01, 2004 4:46 am

Hi Craig,
I tried putting double quotes around the single quotes and it still doesnot work. Could you give me a solution.

Not only that, I need a generic solution. I just gave a sample query to explain my requirement.
Thanks... I would appreciate if some could give me a solution

Regards

chulett · Post by **chulett** » Mon Mar 01, 2004 10:00 am

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#

Ok, we need more information...

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.

Inquisitive · Post by **Inquisitive** » Tue Mar 02, 2004 9:18 pm

Hi Craig,
I work using DB2 API Stage using PX version 7.0 . View data fires the sql taking the job parameter. However while running the job, it ignores the singles quotes in the job parameter. Is it a bug or is there a way to escape the single quotes and pass it to the SQL

Rgds