DB2 IN clause

[PhilHibbs]

I'm having trouble with a DB2 extract, I'm trying to paramterise an IN clause. It looks like this:

SELECT ...
FROM ...
WHERE DER_AUTH_CD IN ( #pLocalAuths# )

My parameter has a value such as '001','002','003','004'

When I run it this way, I get "DB_Extract_OCCUPANT: DB2 reported: SQLSTATE = 42818: Native Error Code = -401: Msg = [IBM][CLI Driver][DB2] SQL0401N The data types of the operands for the operation "" are not compatible or comparable. SQLSTATE=42818"

If I change the SQL to this:

SELECT ...
FROM ...
WHERE DER_AUTH_CD IN ( #pLocalAuths# )

and pass the parameter as 1,2,3,4 without any quotes, I get this:
"DB_Extract_OCCUPANT: DB2 reported: SQLSTATE = 42601: Native Error Code = -104: Msg = [IBM][CLI Driver][DB2] SQL0104N An unexpected token "." was found following "". Expected tokens may include: "( TABLE FINAL <IDENTIFIER> XMLTABLE". SQLSTATE=42601"

Any ideas?

*Update* I have narrowed it down to the quotes. If I have a simple = clause like this:

WHERE DER_AUTH_CD = '#Param#'

...then that works fine with a parameter of 001. But if I move the quotes into the parameter:

WHERE DER_AUTH_CD = #Param#

...that falls over with a parameter of '001'.

[PhilHibbs]

I think the second error might have been a mistake that I made when running the job. It seems to be working now with an unquoted list of numbers and INT() around the column. I'm lucky that this column happens to always contain numeric values - if I ever need to select with an IN list of character values, then I will still face this problem, so any solution or suggestions would be appreciated.

[suse_dk]

You can also have a list of sting values passed as a parameter, you just have to put each of them in "" (that is the double quote character and NOT just two adjendent single quotes)

[PhilHibbs]

You can also have a list of sting values passed as a parameter, you just have to put each of them in "" (that is the double quote character and NOT just two adjendent single quotes)

I'm fairly sure that DB2 won't allow double quotes in this context, it has to be single quotes.

[suse_dk]

Well... neither does SQL server - however this actually works on SQL server...
Try it out!

[BI-RMA]

Hi Phil,
try to use \' instead of ' , otherwise DataStage will probably erase the quotes from your parameter-string.

[PhilHibbs]

Well... neither does SQL server - however this actually works on SQL server...
Try it out!

You mean, just put this into the job parameter?

Code: Select all

"001","002","003"

That gives this:

Code: Select all

DB_Extract_OCCUPANT: DB2 reported: SQLSTATE = 42S22: Native Error Code = -206: Msg = [IBM][CLI Driver][DB2] SQL0206N  "001" is not valid in the context where it is used.  SQLSTATE=42703

[BI-RMA]

Hi Phil,

use SELECT ...
FROM ...
WHERE DER_AUTH_CD IN ( #pLocalAuths# )

with pLocalAuths = \'001\',\'002\',\'003\'

You can use the replace()-function in the sequence to replace "'" by "/'".

[suse_dk]

What can I say... using the ODBC connector stage and extracting from a SQL server database the it works.

p_List parameter defined in job properties: "001","002"

OCBD Connector:

SELECT ...
FROM ....
WHERE DER_AUTH_CD IN (#p_List#)

[chulett]

if I ever need to select with an IN list of character values, then I will still face this problem, so any solution or suggestions would be appreciated.

As noted, you should be able to find some mechanism that will work to escape the quotes. Sometimes a backslash works, sometimes just another single quote works. Experiment.

[ppgoml]

what's your DS version? I met this issue in 8.0.1 before.

and If you wrote sql like this

SELECT ...
FROM ...
WHERE DER_AUTH_CD IN ( '#pLocalAuths#' )

passing a value such as 001','002','003','004 should work

and I remember this issue fixed in a later patch.