By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,473 Members | 3,505 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,473 IT Pros & Developers. It's quick & easy.

Compilation error, stored procedure, sql, iseries v5r4 (as400), urgent help plz.

P: n/a
When i try to run this SQL statements in iSeries Access for windows
(against my customers db2) i get:

SQL State: 42904
Vendor Code: -7032
Message: [SQL7032] SQL procedure, function, or trigger
GET_INVENTORY_SEQUENCE in BPCSAX_PRD not created. Cause . . . . . :
SQL procedure, function, or trigger GET_INVENTORY_SEQUENCE in
BPCSAX_PRD was not created. The compile was not successful. SQL creates
an SQL procedure, function, or trigger as a C program that contains
embedded SQL. Errors not found during the initial parsing of the
CREATE PROCEDURE, ALTER PROCEDURE, CREATE FUNCTION, or CREATE TRIGGER
statement can be found during the precompile. Recovery . . . : If a
compile error occurred, see the appropriate listing in QSYSPRT. If the
SQL precompile failed, there is always a listing with the error. If the
C compile failed, the listing is only created if requested. Specify SET
OPTION OUTPUT=*PRINT prior to the routine body in the CREATE statement
if listings are required.

Processing ended because the highlighted statement did not complete
successfully

The problem is that the very same procedure compiled/worked fine on
v5r3, but after they upgraded to v5r4 it wont compile.

Any ideas?

CREATE PROCEDURE BPCSAX_PRD.GET_INVENTORY_SEQUENCE ()
RESULT SETS 1
LANGUAGE SQL
BEGIN

-- Declare the custId variable that we will use for fetching a
customer
DECLARE pSequence NUMERIC (20,0) ;

-- Declare dummy statement variable
DECLARE pStmt CHAR (512) ;

-- Declare the result set cursors
DECLARE c1 CURSOR WITH RETURN FOR s1 ;

UPDATE BPCSPUF.P_ILI
SET ADB_L_DELIVERY = 'I'
WHERE ADB_L_DELIVERY = 'N'
AND TRIM(LWHS) = 'FI';

UPDATE BPCSPUF.P_ILI
SET ADB_L_DELIVERY = 'I'
WHERE ADB_L_DELIVERY = 'N'
AND TRIM(LWHS) = 'VV';

UPDATE BPCSPUF.P_ILI
SET ADB_L_DELIVERY = 'I'
WHERE ADB_L_DELIVERY = 'N'
AND TRIM(LWHS) = 'VT';

UPDATE BPCSPUF.P_ILI
SET ADB_L_DELIVERY = 'I'
WHERE ADB_L_DELIVERY = 'N'
AND TRIM(LWHS) = 'PB';

UPDATE BPCSPUF.P_ILI
SET ADB_L_DELIVERY = 'I'
WHERE ADB_L_DELIVERY = 'N'
AND TRIM(LWHS) = '07';

UPDATE BPCSPUF.P_ILI
SET ADB_L_DELIVERY = 'E'
WHERE ADB_L_DELIVERY = 'N'
AND TRIM(LLOT) = '';

SELECT ADB_SEQUENCE INTO pSequence
FROM BPCSPUF.P_ILI
WHERE ADB_L_DELIVERY = 'N'
AND TRIM(LLOT) <''
AND TRIM(LWHS) <'FI'
AND TRIM(LWHS) <'VV'
AND TRIM(LWHS) <'VT'
AND TRIM(LWHS) <'PB'
AND TRIM(LWHS) <'07'
FETCH FIRST 1 ROW ONLY;

-- ok now we should have a customer id in pCustId.
IF pSequence IS NULL THEN
return -1 ;
END IF ;

UPDATE BPCSPUF.P_ILI SET ADB_L_DELIVERY = 'R' WHERE ADB_SEQUENCE =
pSequence;

SET pStmt = 'SELECT ADB_SEQUENCE, ''SE'' as LOCATION from
BPCSPUF.P_ILI WHERE ADB_SEQUENCE = ?' ;
PREPARE s1 from pStmt ;
OPEN c1 USING pSequence ;

END;

Oct 23 '06 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.