Home > SSIS Best Practices > DB2 – User defined function to convert list to table ListToTable

DB2 – User defined function to convert list to table ListToTable

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)

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: