Recursive Sql and Db2

Hello everyone,

The other day I started a new job at a bank.  With a bank comes some pretty old school technology.  I now get the chance to expand my RDBMS knowledge pass SQL Server to DB2.  I created one sweet function the other day and thought I would pass it along.

I need to take a comma list and convert it into the a table using an UDF.  I needed this for a report I was making in SSRS.  In the report there would be a drop down menu with various items to pick.

I found this little blog to explains a cool recursive CTE in DB2.  It did not work 100% right, but I just had to tweak a few lines to make it work for me.

Here is my final version of the SQL:

WITH CTE_Items (ID,StartString,StopString) AS(
   SELECT
       1 AS ID
       ,1 AS StartString
       ,LOCATE('|', '2010-Q1|2011-Q1|2012-Q1|2013-Q1') AS StopString
   FROM SYSIBM.SYSDUMMY1
   WHERE LENGTH('|')>0
     AND LENGTH('2010-Q1|2011-Q1|2012-Q1|2013-Q1')>0

   UNION ALL

   SELECT
       ID + 1
       ,StopString + LENGTH('|')
       ,LOCATE('|', '2010-Q1|2011-Q1|2012-Q1|2013-Q1', StopString + LENGTH('|'))
   FROM
       CTE_Items
   WHERE
       StopString > 0
)
SELECT
     Id,
     substr('2010-Q1|2011-Q1|2012-Q1|2013-Q1',StartString,
                      CASE
                          WHEN StopString=0
                          THEN length('2010-Q1|2011-Q1|2012-Q1|2013-Q1')-startstring+1
                          ELSE StopString-StartString
                      END
                    )
FROM CTE_Items;

To put this into a UDF is pretty simple.  Just create a few variables and there you go...


CREATE FUNCTION [schemaname].UDF_Split(In_Data VARCHAR(32000), In_Delimiter VARCHAR(5) )RETURNS TABLE (ID INT,
                Value VARCHAR(5000)
                )
LANGUAGE SQL
DETERMINISTIC
RETURN
WITH CTE_Items (ID,StartString,StopString) AS(
   SELECT
       1 AS ID
       ,1 AS StartString
       ,LOCATE(In_Delimiter, In_Data) AS StopString
   FROM SYSIBM.SYSDUMMY1
   WHERE LENGTH(In_Delimiter)>0
     AND LENGTH(In_Data)>0

UNION ALL

   SELECT
       ID + 1
       ,StopString + LENGTH(In_Delimiter)
       ,LOCATE(In_Delimiter, In_Data, StopString + LENGTH(In_Delimiter))
   FROM
       CTE_Items
   WHERE
       StopString > 0
)
SELECT ID, 
         substr(In_Data,StartString,
                     CASE 
                           WHEN StopString=0 THEN length(In_Data)-startstring+1 
                           ELSE StopString-StartString
                        END
                )
FROM CTE_Items;COMMIT;

Let's test it out.

Example 1 ('2010-Q1|2011-Q1|2012-Q1|2013-Q1'):

Example 2 ('blue,green,red'):

As you can see you can use any type of delimiter to make the split pretty powerful.  In the store procedure it works perfect.  I query the UDF in the "In" clause of the Sql such as.


SELECT value FROM TABLE("Schemaname"."UDF_SPLIT"'blue,green,red', ',')) AS UDF


One Last issue.  In my SSRS report it was not passing the parameter along towards the store procedure in Db2 correctly.  After doing some googling I found this little blog explaining what you need to do.  Just a small tweak.

You need to go to the following location to change a setting.

Dataset --> Dataset Properties --> Parameters --> Pick parameter's expression --> Put this value in the box.

=join(Parameters!P3.Value,",")


Now the report will run like its suppose to.

Cheers and Go Brewers!

Robert