INLIST Routine/Function
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 24
- Joined: Fri Aug 26, 2005 3:52 pm
- Contact:
INLIST Routine/Function
Hi
I need help with INLIST function.
I searched in the forum but no luck.
It looks like it used to be there previously
I need to pass the multiple valued to a " select .... from ... IN ( val1,val2,val3,....,...,...) "
But i will get all values in one parameter as input.
the list of parms also get changed by every job.
Please post the INLIST function/routine code if you have in your archives.
Thanks
Raghu
I need help with INLIST function.
I searched in the forum but no luck.
It looks like it used to be there previously
I need to pass the multiple valued to a " select .... from ... IN ( val1,val2,val3,....,...,...) "
But i will get all values in one parameter as input.
the list of parms also get changed by every job.
Please post the INLIST function/routine code if you have in your archives.
Thanks
Raghu
If your values are in a string and formatted correctly for sql, just insert them into your custom SQL as a single value.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 24
- Joined: Fri Aug 26, 2005 3:52 pm
- Contact:
-
- Participant
- Posts: 24
- Joined: Fri Aug 26, 2005 3:52 pm
- Contact:
Hi
SQL i am reading from DB2 is
Select Code, Color_Desc, ColorPattarn from DW.CODECOLORS where Color_Desc IN (#ColorParams.ColorCodes#) with ur;
Parameter values ColorParams.ColorCodes = 'RED','BULE','YELLOW'.
number of values seperated by ',' will be changing from job to job
I can view data with the aboue SQL and Values that i provided
but when i tried to write the same values to a file then getting error below:
APT_CombinedOperatorController,0: Fatal Error: Fatal: [IBM][CLI Driver][DB2/LINUXXNNNN] SQL0007N The character "\" following "_Desc IN (RED" is not valid. SQLSTATE=42601
DSTAGE-DB2CLI-0027`:`SQLExecDirect: Error executing statement 'Select Code, Color_Desc, ColorPattarn from DW.CODECOLORS where Color_Desc IN (RED\, BLUE\, YELLOW) with ur'.
I think the issue is with parsing the values.
I tried again searching the INLIST Routine in the forum but no luck again.
Thanks
Raghu
SQL i am reading from DB2 is
Select Code, Color_Desc, ColorPattarn from DW.CODECOLORS where Color_Desc IN (#ColorParams.ColorCodes#) with ur;
Parameter values ColorParams.ColorCodes = 'RED','BULE','YELLOW'.
number of values seperated by ',' will be changing from job to job
I can view data with the aboue SQL and Values that i provided
but when i tried to write the same values to a file then getting error below:
APT_CombinedOperatorController,0: Fatal Error: Fatal: [IBM][CLI Driver][DB2/LINUXXNNNN] SQL0007N The character "\" following "_Desc IN (RED" is not valid. SQLSTATE=42601
DSTAGE-DB2CLI-0027`:`SQLExecDirect: Error executing statement 'Select Code, Color_Desc, ColorPattarn from DW.CODECOLORS where Color_Desc IN (RED\, BLUE\, YELLOW) with ur'.
I think the issue is with parsing the values.
I tried again searching the INLIST Routine in the forum but no luck again.
Thanks
Raghu
I would avoid the INLIST as that would require using a BASIC transform stage in your PX job. Are you 100% certain that the string is as you described (check the log for parameter values). You might also try using for the parameter value to see if that makes a difference.
Code: Select all
"'RED','BLUE','YELLOW'"
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 24
- Joined: Fri Aug 26, 2005 3:52 pm
- Contact:
It is, just not a good one for PX. So attempt to protect your quotes as noted - back-slash, double them up, etc. Or you could load your "inlist" values into a work table and join it into your query. Or (yuck) select everything and filter out what you don't want in the job.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers