Parameterizing where clause in db2 connector

[nikhil_bhasin]

I am trying to paramterize the arguments of where clause in Db2 connector stage like below:-

Select col1,col2 from schema.tabname1 where col2 IN ('#var1#')

Now the above query works well if i pass only 1 value e.g var1='N', but does not pull any data when i pass more than 1 e.g. var1='N','D'

I am assuming the above behaviour is due to single quotes placed while giving the paramter '#var1#' . But if i remove them, datastage treats values as column names and abort giving below error:-

DB2_Connector_1,0: DB2 reported: SQLSTATE = 42S22: Native Error Code = -206: Msg = [IBM][CLI Driver][DB2/AIX64] SQL0206N "N" is not valid in the context where it is used. SQLSTATE=42703

Please let me know if there is way to pass multiple values to a job paramter

[ray.wurlod]

This has been posted a number of times, with the consensus seeming to be that you can't make a list a parameter. However, you can make the entire SQL statement (with its list) a parameter.

[ArndW]

Are you certain that your var1 string contains the single quotes around the values? If so, then just doing ...IN (#var1#)... in the SELECT should work. I don't have DB2 here, but tested it with other connectors and that functioned correctly.

In your post I see double-quote in the error message.

[MT]

Hi nikhil_bhasin

see also

http://www-01.ibm.com/support/docview.w ... wg1JR29181


and make sure to lookup any further problem descriptions based on
APT_OSL_PARAM_ESC_SQUOTE

[nikhil_bhasin]

Got the answer, we need to include escape char \ for every single qoute that we are passing in the value of parameter
eg var1 = \'A\',\'N\' so on...

[chulett]

Actually, you don't. The link MT posted shows that you can set an APT variable so that happens automatically.