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
Parameterizing where clause in db2 connector
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 50
- Joined: Tue Jan 19, 2010 4:14 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
In your post I see double-quote in the error message.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Re: Parameterizing where clause in db2 connector
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
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
regards
Michael
Michael
-
- Participant
- Posts: 50
- Joined: Tue Jan 19, 2010 4:14 am