I am working in a project that I need to parse an arrayt an select
records based upon the values parsed. I used the functions developed by
Knut Stolze in his article 'Parsing Strings'. I am particulary having
problems inserting records using a function.

I am attaching the code for your review. Please, help.

=================================--Function
1--====================================

CREATE FUNCTION lx2p111.DelimiterIndex ( string varchar(8000),
Delimiter char(1) )
  RETURNS TABLE ( ordinal INTEGER, index INTEGER )

   -- string       = any string delimited by a character
   -- Delimiter    = the delimimeter character i.e. ',', -, *
   -- Description  This functions returns the begining and end
position for every array.
   --               supports RangeElements function

LANGUAGE SQL
  DETERMINISTIC
  NO EXTERNAL ACTION
  CONTAINS SQL
  RETURN
     WITH t(ordinal, index) AS
        ( VALUES ( 0, 0 )
          UNION ALL
          SELECT ordinal+1, COALESCE(NULLIF(
                    -- find the next delimiter ','
                    LOCATE(',', string, index+1), 0),
                    LENGTH(string)+1)
          FROM   t
          WHERE  ordinal < 10000 AND
          LOCATE(Delimiter, string, index+1) <> 0 )

     SELECT ordinal, index
     FROM   t
     UNION ALL
     -- add indicator for the end of the string
     SELECT MAX(ordinal)+1, LENGTH(string)+1
     FROM   t

=================================--Function
2--====================================

CREATE FUNCTION LX2P111.RangeElements ( string varchar (8000),
delimiter char (1) )
  RETURNS TABLE ( elements varchar(255) )

------------------------------------------------------------------------

   -- string       = any string delimited by a character
   -- Delimiter    = the delimimeter character i.e. ',', -, *
   -- Description  This functions returns every array element in the
list (i.e. ranges)

  LANGUAGE SQL
  DETERMINISTIC
  NO EXTERNAL ACTION
  CONTAINS SQL
  RETURN
     WITH t(ordinal, index) AS
        ( SELECT ordinal, index
          FROM   TABLE ( LX2P111.DelimiterIndex(string, Delimiter) )
AS x )
     SELECT SUBSTR(string, t1.index+1, t2.index - t1.index - 1)
         FROM   t AS t1 JOIN t AS t2 ON
               ( t2.ordinal = t1.ordinal+1 )

=================================--Stored
Proc--====================================

CREATE PROCEDURE LX2P111.LDBS_REQUISITION_DETAIL_TEST (
                                                       IN
ac_req_number_range

VARCHAR(8000),
                                                       IN adt_begin
DATE
                                                       )
   DYNAMIC RESULT SETS 1
   LANGUAGE SQL

   -- ac_req_number_range      = Requisition ranges (parse string, for
dsicrete values

min=max)
   -- adt_begin                   = The beginning date range to present
(single

value)
   -- Description  This procedure will parse the ai_req_number_range
and insert
   --              all rows into temp table.  It first checks to see
if there

P1: BEGIN
--Create temp table to hold for the parsed array element values

    DECLARE GLOBAL TEMPORARY TABLE SESSION.TempList
    (
        RecordId varchar(255)
    ) ;

-- Insert record into tempList using function

       insert into session.templist
       select *
       from table (LX2P111.RangeElements (ac_req_number_range, ','))
;

--Insert elelements of array into separate fields

           DECLARE GLOBAL TEMPORARY TABLE SESSION.TempList2
           (
                  ParamName Varchar(25),
                  MinValue  varchar(15),
                  Maxvalue  varchar(15)
           )   ;

           insert into session.templist2
           select  'ReqNo' ,
                   left(RecordId, (locate('-', RecordID, 1)-1)) ,
                       substr  (RecordId, (locate('-', RecordId,
1)+1),(length

(RecordId)-  (locate('-', RecordId, 1))))
               from SESSION.TEMPLIST;

--Declare cursor
       BEGIN
          DECLARE MyCursor CURSOR WITH RETURN FOR
                  select RQH_REQ_LOCATION,
                      RQH_REQ_NUMBER,
                      RQH_CREATED,
                      RQH_REQUESTER,
                      RQH_DELIVER_TO
                  from lx2p111.requisition_detail rd, session.templist2 t2
                  where  rd.RQH_REQ_NUMBER between cast(t2.MinValue as int)

and cast(t2.MaxValue as Int)
                  and    rd.RLN_CREATED >= coalesce(adt_begin, '01/01/1900')
                  ;

                -- Cursor left open for client application
              OPEN MyCursor;
    END;
END P1