Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2008
    Posts
    6

    Question Unanswered: UDF or Stored Procedure able to split rows into multiple?

    Hi,

    I have a question regarding the functionality of UDFs/Stored Procedures and which one, if either, would be suitable to something I have to do.

    I have a table with rows in it that contain 3 columns and an id. First column is a comma separated list of names. Next column is a comma separated list of lengths. Next column is a list of bits.

    Basically, these columns all relate to each other. The list of names and lengths are used to determine the separation between the bits.

    Is it possible to separate a single row and values within a column into multiple rows that contain name/value pairs?

    In other words, In a single row:

    ID:500
    Names: Joe,Al,Jason
    Lengths: 5, 3, 7
    Bits: 110101111001111

    Would end up becoming:

    500 Joe 11010
    500 Al 111
    500 Jason 1001111

    So basically I just need to know if either an SQL Procedure or a UDF would be more useful for splitting up a row into multiple rows while at the same time, performing operations on specific columns.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You could do it in a stored procedure. You might be able to do it with a UDF if you use recursive SQL.

    Andy

  3. #3
    Join Date
    Sep 2008
    Posts
    6
    Thanks Andy,

    Is there something specific about stored procedures that makes this possible? Like a specific command?

    I'm kind new to either of these things.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What you are going to have to do is create a global temporary table to put the rows in while you are parsing the outer data. This is easier to do in a SP.

    Andy

  5. #5
    Join Date
    Sep 2008
    Posts
    6
    Thanks for the info.

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by jercole
    ID:500
    Names: Joe,Al,Jason
    Lengths: 5, 3, 7
    Bits: 110101111001111

    Would end up becoming:

    500 Joe 11010
    500 Al 111
    500 Jason 1001111
    The following does not need a stored proc; it just uses a recursive CTE:
    Code:
    WITH a(id,name,rname,pname,length,rlength,plength,bits,sbits,rbits,c) AS
    ( SELECT id, NULLIF('',''), names, locate(',',names), NULLIF('999','999'),
             lengths||',', locate(',',lengths), NULLIF('',''), 1, bits, 0 FROM Tbl
     UNION ALL
      SELECT id,
             CASE pname WHEN 0 THEN rname ELSE substr(rname, 1, pname-1) END,
             CASE pname WHEN 0 THEN NULLIF('','') ELSE substr(rname, pname+1) END,
             locate(',',substr(rname, pname+1)),
             CASE plength WHEN 0 THEN rlength ELSE substr(rlength, 1, plength-1) END,
             CASE plength WHEN 0 THEN NULLIF('','') ELSE substr(rlength,plength+1) END,
             locate(',',substr(rlength, plength+1)),
             CASE plength WHEN 0 THEN rbits
              ELSE substr(rbits, sbits, int(substr(rlength,1,plength-1))) END,
             sbits+
             CASE plength WHEN 0 THEN 0 ELSE int(substr(rlength,1,plength-1)) END,
             rbits,
             c+1
      FROM a WHERE c < 100 AND rname IS NOT NULL
    )
    SELECT id, name, bits FROM a
    WHERE  c > 0
    Last edited by Peter.Vanroose; 09-29-08 at 18:08.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Sep 2008
    Posts
    6
    Thanks. I was looking at recursive SQL earlier and trying to come up with something like this. I'll test it out in the morning. Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •