DB2 IN clause
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 1044
- Joined: Wed Sep 29, 2004 3:30 am
- Location: Nottingham, UK
- Contact:
DB2 IN clause
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'.
Phil Hibbs | Capgemini
Technical Consultant
Technical Consultant
-
- Premium Member
- Posts: 1044
- Joined: Wed Sep 29, 2004 3:30 am
- Location: Nottingham, UK
- Contact:
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.
Phil Hibbs | Capgemini
Technical Consultant
Technical Consultant
-
- Premium Member
- Posts: 1044
- Joined: Wed Sep 29, 2004 3:30 am
- Location: Nottingham, UK
- Contact:
I'm fairly sure that DB2 won't allow double quotes in this context, it has to be single quotes.suse_dk wrote: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)
Phil Hibbs | Capgemini
Technical Consultant
Technical Consultant
Hi Phil,
try to use \' instead of ' , otherwise DataStage will probably erase the quotes from your parameter-string.
try to use \' instead of ' , otherwise DataStage will probably erase the quotes from your parameter-string.
Last edited by BI-RMA on Mon Oct 10, 2011 9:16 am, edited 1 time in total.
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
There are the grateful those are happy." Francis Bacon
-
- Premium Member
- Posts: 1044
- Joined: Wed Sep 29, 2004 3:30 am
- Location: Nottingham, UK
- Contact:
You mean, just put this into the job parameter?suse_dk wrote:Well... neither does SQL server - however this actually works on SQL server...
Try it out!
Code: Select all
"001","002","003"
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
Phil Hibbs | Capgemini
Technical Consultant
Technical Consultant
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 "/'".
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 "/'".
Last edited by BI-RMA on Mon Oct 10, 2011 9:16 am, edited 1 time in total.
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
There are the grateful those are happy." Francis Bacon
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.PhilHibbs wrote: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.
![Wink :wink:](./images/smilies/icon_wink.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers