RE: Compile problems with SQL Stored Procedures

Alan, I could be wrong but I don't think you can have executable statements
preceding declares in the routine body, on System i at least.
As an alternative, you may want to create an additional stored procedure
that calls your first procedure that sets the library list followed by a
call to 2nd procedure to create and open the result set.

I tried this in Run Sql Scripts and believe it would work for your case:

/* start sample script */

CREATE PROCEDURE QGPL/PROC1()
LANGUAGE SQL
BEGIN
CALL QCMDEXC('ADDLIBLE SYSIBM', 0000000015.00000);
END;

CL: ADDLIBLE SYSIBM;

CREATE PROCEDURE QGPL/PROC2()
RESULT SET 1
LANGUAGE SQL
BEGIN
DECLARE MYCURSOR1 CURSOR WITH RETURN TO CLIENT FOR
SELECT * FROM SYSDUMMY1;
OPEN MYCURSOR1;
END;

CL: RMVLIBLE SYSIBM;

CREATE PROCEDURE QGPL/FINALPROC1()
RESULT SETS 1
LANGUAGE SQL
BEGIN
CALL QGPL/PROC1();
CALL QGPL/PROC2();
END;

CALL QGPL/FINALPROC1();

/* end sample script */

Click on Run->All to execute the entire script.

Hope that helps.

Elvis

Celebrating 10-Years of SQL Performance Excellence

-----Original Message-----
Subject: Compile problems with SQL Stored Procedures

I like SQL Stored Procedures but they have the quirkiest behaviors.

I have this stored procedure. I see plenty of examples of the same thing
but when I compile I get a:

SQL0104 30 20 Position 13 Token C1 was not valid. Valid tokens:
GLOBAL.

If I comment out the call statement, it will compile but not with it in
there.

I have run into this before where SQL will not compile something before
the declare statement even though the books have all kinds of examples.

Anybody got an idea what the devil is going on here?

Create Procedure MAPDBA/MRPGETSTATUS2
(In InItemNumber Varchar(15),
In InProgramEnv Char(1),
In InFileEnv Char(1))
Dynamic Result Sets 1
Language SQL
Not Deterministic
Reads SQL Data

Set Option DbgView = *Source, DatFmt = *ISO, DatSep = '-'

Begin
Call SetLibraryList(InProgramEnv,InFileEnv);

Declare C1 Asensitive Scroll Cursor With Return For
Select VarChar(a.FITEM,15,037) As ItemNumber,
iDate(a.SSTDT) As StartDate,
iDate(a.ODUDT) As DueDate,
VarChar(a.ORDNO,7,037) As OrderNumber,
VarChar(c.OPSEQ,4,037) As OperationsSequence,
VarChar(c.OPDSC,20,037) As OperationsDescription,
VarChar(b.ITDSC,30,037) As ItemDescription,
VarChar(c.WKCTR,5,037) As WorkCenter,
VarChar(c.OPSTC,2,037) As OperationStatus,
a.DOSCH As DaysOffSchedule,
VarChar(a.JOBNO,12,037) As Notes
From MOMAST a
Inner Join ITMRVA b
On b.STID = a.FITWH And
b.ITNBR = a.FITEM
Inner Join MOROUT c
On c.ORDNO = a.ORDNO And
c.WKCTR <> '0000'
Where a.FITEM = InItemNumber And
a.OSTAT <= '40'
Order By a.FITWH,
iDate(a.ODUDT),
iDate(a.SSTDT),
a.ORDNO,
c.OPSEQ;

Open C1;
End;