Parameterising a WHERE clause

thebird · Post by **thebird** » Fri Jul 15, 2005 3:33 am

Hi,

I want to pass an SQL query as a parameter in a job, but I am getting a fatal error when I do so. I believe that DS is not accepting the quotes that I give to define WHERE clause.

My where clause is :
WHERE CNSM_END_DT = TO_DATE('2999-12-31','YYYY-MM-DD')

The fatal error that I get is :

ConsumerDbTgt: GenericQuery:esqlErrorHandler
Prepare failed for: GenericStmt_1
query is: SELECT CNSM_ID,SRC_CNSM_CD AS src_cnsm_cd,SRC_APPL_CD AS src_appl_cd FROM CONSUMER WHERE CNSM_END_DT = TO_DATE(2999-12-31, YYYY-MM-DD) ORDER BY src_appl_cd ASC,src_cnsm_cd ASC
sqlcode is: -904
esql complaint: ORA-00904: "DD": invalid identifier

I can see in this error message that the quotes that I had given for TO_DATE('2999-12-31','YYYY-MM-DD') is missing.

Is there a way that I can accomplish this?

Regards,

The Bird.

chulett · Post by **chulett** » Fri Jul 15, 2005 6:01 am

You are right, the quotes in String parameter values get stripped.

Do you need to pass the entire phrase? Can you just pass the date?

ArndW · Post by **ArndW** » Fri Jul 15, 2005 6:08 am

Can't you just do WHERE CNSM_END_DT = TO_DATE(\'2999-12-31\',\'YYYY-MM-DD\') ?

thebird · Post by **thebird** » Fri Jul 15, 2005 6:42 am

ArndW wrote:Can't you just do WHERE CNSM_END_DT = TO_DATE(\'2999-12-31\',\'YYYY-MM-DD\') ?

Hi,

Thanks Craig, Arnd for your quick responses... Arnd's solution worked. Thanks a lot.

But can you tell me how the quotes get recognised by giving a "" before it? I dont know about this.

Regards,
The Bird.

ArndW · Post by **ArndW** » Fri Jul 15, 2005 6:45 am

the \' construct tells it not to parse the quote but accept it unparsed. This is commonly used both at UNIX level and also in Oracle SQL.

chulett · Post by **chulett** » Fri Jul 15, 2005 6:59 am

It's called 'escaping' and the back-slash is the default escape character. It is usually used to tell something to ignore a metacharacter and (as Arnd notes) accept it 'unparsed'. In this case it also works for quotes.

I didn't mention it because in my experience with the Server product it doesn't help. So... either my memories are faulty, it only actually works in the PX product or... they've managed to fix that.

Glad you got it working.

thebird · Post by **thebird** » Fri Jul 15, 2005 7:45 am

Got it Craig,Arnd.

Thanks once again.

Regards,

The Bird.