INLIST Routine/Function

Raghumreddy · Post by **Raghumreddy** » Sat May 16, 2009 9:50 pm

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

ArndW · Post by **ArndW** » Sun May 17, 2009 6:09 am

If your values are in a string and formatted correctly for sql, just insert them into your custom SQL as a single value.

Raghumreddy · Post by **Raghumreddy** » Sun May 17, 2009 7:10 am

ArndW wrote:If your values are in a string and formatted correctly for sql, just insert them into your custom SQL as a single value. ...

Tried the same .
I can see the viewdata but the job is failing the same to write to a file.

Thanks for your help.
I appriciate any more ideas.

Thanks
Raghu

chulett · Post by **chulett** » Sun May 17, 2009 8:58 am

First, search more better - the BASIC function INLIST is still out there, authored by 'kcbland'. And 'failing the same to write' tells us nothing, post your actual errors or problem details if you expect more detailed help.

Raghumreddy · Post by **Raghumreddy** » Sun May 17, 2009 1:16 pm

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

ArndW · Post by **ArndW** » Sun May 17, 2009 2:03 pm

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

Code: Select all

"'RED','BLUE','YELLOW'"

for the parameter value to see if that makes a difference.

chulett · Post by **chulett** » Sun May 17, 2009 4:26 pm

DataStage is stripping your quotes so try escaping them. Searched for "INLIST" with author "kcbland" and easily found it here and here but (as noted) why bring a BASIC gun to a PX gunfight?

Raghumreddy · Post by **Raghumreddy** » Sun May 17, 2009 4:29 pm

Hi
I thought INLIST is an option.
Please let me know if there is any workaround
Thanks in advance
Raghu

chulett · Post by **chulett** » Sun May 17, 2009 5:09 pm

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.