Parameterizing where clause in db2 connector
Posted: Mon Jul 02, 2012 3:53 am
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
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