WHERE condition as a parameter

[Terala]

Hi,

I have a requirement to extract data from source tables - for that i am using ODBC EE start. requirement is to pass the where condition as a parameter . I am getting SQL0401N when i pass where condition using IN but when i hard code the same query using IN statement it works fine.

Any workaround to overcome this issues?

Thanks

[ray.wurlod]

A Search of DSXchange will suggest that what you want to do is not easily possible, though it IS possible to pass the entire SQL statement as a parameter and to use that as user-defined SQL.

[Terala]

Hi Ray,

I did not search before posting it but i could find only one posting with the same error message.

I tried to provide entire sql statement as a parameter by defining User defined SQL in ODBC section, i am still getting same error message.

Here is sample of sql
select * from table where col1 in ('a', 'b', 'c')

Thanks,

[chulett]

The issue is always the same when people feel the need to do this - DataStage strips the quotes from the parameter. You need to 'escape' them to try to preserve them.

[Terala]

can you guide me how to 'escape' them?

[chulett]

A search would turn up all the various techniques people have tried, what does (and doesn't) work. First thing to try - a "backslash" before each one.

[Terala]

It worked.. with below modification in SQL

select * from table where col1 in (\'a\', \'b\', \'c\')

Thanks,

[Terala]

I ran in another issues.
In my where condition parameter i have <= and > conditions.
passing <= or >= is not taking and job throwing error message "
SQL0104N An unexpected token "'<='" was found"

how can i over come this? I tried escaping them but no luck.

Thanks,

[anbu]

Add another job parameter COND

Code: Select all

(#COND# = 'LT' AND COL1 < COL2) OR
(#COND# = 'GT' AND COL1 > COL2) OR