Passing sql as parameter

[wahi80]

Hi,

Im passing an Sql query as a parameter to the job
e.g select name where state in ('NY','NJ')
The query works fine in Oracle but when I pass it as a parameter to the job, it fails. On going through the log I saw that the query was not getting coverted properly.
The log was as follows :
Oracle_Enterprise_0: The provided query statement did not prepare correctly;
please verify that your statement is correct;
statement: SELECT name where state in (NY,NJ).

The quotes were removed from the in clause,hence the failure.

Can anyone point me to what should be done so that Datastage interprets the quotes.

The job runs fine if I pass a query with numeric data e.g: select name where empid=189

Regards
Wah

[BugFree]

Try with an escape character.

Code: Select all

select name where state in (\'NY\',\'NJ\')

[Kirtikumar]

Rather than going for IN clause and a parameterized approach there might be a simple approach to deal with this.

Is you table going to be constant in the SELECT stmt? If yes, you can go for something else in this case.

[wahi80]

[quote="BugFree"]Try with an escape character. [code]select name where state in (\'NY\',\'NJ\')[/code][/quote]

Hi,
I have tried the above query, but still an error is displayed:

[code]Oracle_Enterprise_0: The provided query statement did not prepare correctly;
please verify that your statement is correct;
statement: select name where state in(\NY\,\NJ\).[/code]

I think the above code works properly in 7.5, but fails in 8.0

As to the next question the table is going to be a constant but the where condition will keep changing.

Any other ideas??

Regards
Wah

[chulett]

I think the above code works properly in 7.5, but fails in 8.0

If that's the case then you need to involve support. That would be a bug that they'd need to fix. (or may already have fixed)