DB2 SQL referencing a list

[ray.wurlod]

The first format is easiest; the routine reads a line at a time and returns a comma-delimited list. You then use the result of the function as your "list of things" in a StartLoop activity in a job sequence.

[a2love]

Hey again Ray,

Well i appear to have set the value of the parameter correctly.

This is the value of the param passed in:

Code: Select all

ppFNDPolicyList='0136530000','0322430008','0414643009','0532516008'

So thats all good. But when I run the following code:

Code: Select all

SELECT CTR_POL_NUM, CTR_RSA_TOT_PREM

FROM  DMIDBO.CTR_TRANSACTION

WHERE 
CTR_SOURCE_SYSTEM IN ('HC' , 'HF') AND 
CTR_POL_NUM IN (#ppFNDPolicyList#)

GROUP BY CTR_POL_NUM

WITH UR
;

I end up recieving the following wonky error:

Code: Select all

APT_CombinedOperatorController,0: Fatal Error: Fatal: [IBM][CLI Driver][DB2] SQL0104N  An unexpected token " " was found following "".  Expected tokens may include:  "( + - ? : CASE CAST USER SELECT <FLOAT> CURRENT <DECIMAL> ".  SQLSTATE=42601
SQLExecDirect: Error executing statement 'SELECT CTR_POL_NUM, CTR_RSA_TOT_PREM
FROM  DMIDBO.CTR_TRANSACTION
WHERE 
CTR_SOURCE_SYSTEM IN ('HC' , 'HF') AND 
CTR_POL_NUM IN (6530000\,2430008\,!4643009\,+2516008\)
GROUP BY CTR_POL_NUM
WITH UR
'.  See following DB2 message for details.

Do you know why this may be occuring? or why it changed the value of my string input? The parameter is defined as a string.

Thanks,
Adam