Archive

Posts Tagged ‘ListToTable’

DB2 – User defined function to convert list to table ListToTable

March 13, 2011 Leave a comment

Continuing from my previous post SSIS – Pass a list of values to a data flow source.

Step 3: create a user defined function on DB2 to convert the list of the Status IDs into rows.

Now I need to have a function on DB2 to convert the list of the Status IDs into rows.

Actually I really need 2 user defined functions.

First, I need one to create a table to tell me where each comma starts and ends.

image

Run this query.

SELECT * FROM TABLE(mySchema.ListToTable_IDX(‘1,101,203’)) AS A

Here is the result telling me where each comma starts and ends.

clip_image002

Second, I  need a function to return me back the actual values as rows in a table.

image

Run this query.

SELECT * FROM TABLE(mySchema.ListToTable(‘1,101,203’)) AS A

Here is the result telling me what each value is as rows in a table.

clip_image002[4]

Here is an example how you can call the user defined function in DB2.

image

Now if I need to use this user defined function in a data source of a data flow step from my SSIS package, taking a parameter, I would write the query link this:

image

 

(The script)

%d bloggers like this: