Help! inserting records and functions

[Jaraba]

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.Delimit erIndex ( 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(Delimite r, 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.RangeEl ements ( 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.Delimit erIndex(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_RE QUISITION_DETAI L_TEST (
IN
ac_req_number_r ange

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

-- ac_req_number_r ange = 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_r ange
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.TempLis t
(
RecordId varchar(255)
) ;
-- Insert record into tempList using function

insert into session.templis t
select *
from table (LX2P111.RangeE lements (ac_req_number_ range, ','))
;
--Insert elelements of array into separate fields

DECLARE GLOBAL TEMPORARY TABLE SESSION.TempLis t2
(
ParamName Varchar(25),
MinValue varchar(15),
Maxvalue varchar(15)
) ;
insert into session.templis t2
select 'ReqNo' ,
left(RecordId, (locate('-', RecordID, 1)-1)) ,
substr (RecordId, (locate('-', RecordId,
1)+1),(length

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

--Declare cursor
BEGIN
DECLARE MyCursor CURSOR WITH RETURN FOR
select RQH_REQ_LOCATIO N,
RQH_REQ_NUMBER,
RQH_CREATED,
RQH_REQUESTER,
RQH_DELIVER_TO
from lx2p111.requisi tion_detail rd, session.templis t2 t2
where rd.RQH_REQ_NUMB ER between cast(t2.MinValu e as int)

and cast(t2.MaxValu e as Int)
and rd.RLN_CREATED >= coalesce(adt_be gin, '01/01/1900')
;

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

[Serge Rielau]

Jaraba wrote:

DB2 SQL error: SQLCODE: -440, SQLSTATE: 42884, SQLERRMC:
RANGEELEMENTS;F UNCTION
Message: No authorized routine named "RANGEELEME NTS" of type
"FUNCTION" having compatible arguments was found.
I keep getting this error message.

Could it be the second argument is a CHAR(1)?
LX2P111.RangeEl ements (ac_req_number_ range, ',')
',' is a VARCHAR(1) and cannot be promoted to CHAR(1).
Try LX2P111.RangeEl ements (ac_req_number_ range, CHAR(','))

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Jaraba]

Thanks. I think that was most of the proble. I adjusted data types and
change few things. Now I am getting a privilege error message as
follows:

DB2 SQL error: SQLCODE: -551, SQLSTATE: 42501, SQLERRMC:
CD2KXG;SELECT;L X2P111.REQUISIT ION_DETAIL
"CD2KXG" does not have the privilege to perform operation "SELECT" on
object "LX2P111.REQUIS ITION_DETAIL".
Even thoug i can select records using the id in the command window

[Jaraba]

Everything basically works, but when I join the temp table to the
REQUISITION_DET AIL view, that is when I get the above error

[Serge Rielau]

Jaraba wrote:

Everything basically works, but when I join the temp table to the
REQUISITION_DET AIL view, that is when I get the above error

Now we are lacking the query :-)
If you post all the information together chances increase.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Jaraba]

The query is included in the nody of the stored proc:

BEGIN
DECLARE MyCursor CURSOR WITH RETURN FOR
select RQH_REQ_LOCATIO N,
RQH_REQ_NUMBER,
RQH_CREATED,
RQH_REQUESTER,
RQH_DELIVER_TO
from lx2p111.requisi tion_detail rd,
session.templis t2 t2
where rd.RQH_REQ_NUMB ER between
cast(t2.MinValu e as int)

[Knut Stolze]

Jaraba wrote:

The query is included in the nody of the stored proc:

BEGIN
DECLARE MyCursor CURSOR WITH RETURN FOR
select RQH_REQ_LOCATIO N,
RQH_REQ_NUMBER,
RQH_CREATED,
RQH_REQUESTER,
RQH_DELIVER_TO
from lx2p111.requisi tion_detail rd,
session.templis t2 t2
where rd.RQH_REQ_NUMB ER between
cast(t2.MinValu e as int)

Well, do what the user response of the SQL0551 error message tells you:
grant the necessary privileges on the lx2p111.requisi tion_detail table to
the user who wants to create the procedure. There are some cases where
routines don't accept group privileges. I don't remember the details, but
this might be such a case.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany