DB2 IN clause
Posted: Mon Oct 10, 2011 5:01 am
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'.
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'.