SQL Syntax help for AS400 iSeries Proc

Syntax help please ... AS400 iSeries SQL

NOTICE: we've upgraded from V5R3 to V5R4

Attempt to compile errors at line:  DECLARE C1 CURSOR for

SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token C1 was not valid. Valid tokens: GLOBAL. Cause . . . . . :   A syntax error was detected at token C1.  Token C1 is not a valid token.  A partial list of valid tokens is GLOBAL.  This list assumes that the statement is correct up to the token.  The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery  . . . :   Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token C1. Correct the statement.  The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.

===============================
--  Generate SQL
--  Version:                        V5R4M0 060210
--  Generated on:                   10/27/08 13:44:13
--  Relational Database:            STEEL
--  Standards Option:               DB2 UDB iSeries
 
SET PATH "QSYS","QSYS2","CISFGV" ;
CREATE PROCEDURE CSIDEV.MILLBUILD ( )
     DYNAMIC RESULT SETS 1
     LANGUAGE SQL
     SPECIFIC CSIDEV.MILLBUILD
     NOT DETERMINISTIC
     MODIFIES SQL DATA
     CALLED ON NULL INPUT
     SET OPTION  ALWBLK = *ALLREAD ,
     ALWCPYDTA = *OPTIMIZE ,
     COMMIT = *NONE ,
     DECRESULT = (31, 31, 00) ,
     DFTRDBCOL = *NONE ,
     DYNDFTCOL = *NO ,
     DYNUSRPRF = *USER ,
     SRTSEQ = *HEX  
     P1 : BEGIN

     DECLARE AtEnd INTEGER;
     Declare MILL varchar(3);
     Declare DESCRIPT varchar(30);
     Declare VENDORS varchar(30);

     DECLARE GLOBAL TEMPORARY TABLE MILLS (
          tMILL VARCHAR ( 3 ) ,
          tDESCRIPT VARCHAR ( 30 ),
          tVEND VARCHAR ( 50 )
          ) WITH REPLACE NOT LOGGED ;

     DECLARE C1 CURSOR for
          SELECT    
               substr(JBSV11DB.TPTBLLS.TABLE_CODE_TL,5,3),
               JBSV11DB.TPTBLLS.TABLE_DESCRIPT_TL,
               JBSV11DB.TPTBLLS.TABLE_STATUS_TL
          FROM JBSV11DB.TPTBLLS
          WHERE     (JBSV11DB.TPTBLLS.TABLE_NUMBER_TL = 8220);

     DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
          set AtEnd = 1;

     OPEN C1;

          FETCH C1 INTO MILL, DESCRIPT, VENDORS;

          WHILE (AtEnd = 0) DO
               INSERT INTO SESSION.MILLS(tMILL, tDESCRIPT, tVEND)
                    VALUES(MILL, DESCRIPT, VENDORS);
                    -- (SELECT ITM FROM TABLE ( CSIDEV.SSV2VCHAR ( VENDORS ) ) AS MTF1) );
               FETCH C1 INTO MILL, DESCRIPT, VENDORS;
          END WHILE;
CLOSE C1;

     DECLARE C2 CURSOR FOR
          SELECT * from FROM SESSION . MILLS ;
     OPEN C2;

END P1  ;
LVL 5
volkingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenData Warehouse Architect / DBACommented:
Hi Fred,

On the surface, everything looks fine.  (Pretty good effort on your part!)

DB2 can be picky about the order of the types of DECLARE statements.  You might try moving the DECLARE GLOBAL TEMPORARY TABLE statement down a bit.


Kent
0
volkingAuthor Commented:
@Kent ... ok moved it down but now it errors at line:  DECLARE C2 CURSOR FOR SELECT * from FROM SESSION.MILLS ;
with error
==============================
SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token C2 was not valid. Valid tokens: GLOBAL. Cause . . . . . :   A syntax error was detected at token C2.  Token C2 is not a valid token.  A partial list of valid tokens is GLOBAL.  This list assumes that the statement is correct up to the token.  The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery  . . . :   Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token C2. Correct the statement.  The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.
================================

--  Generate SQL
--  Version:                         V5R4M0 060210
--  Generated on:                    10/27/08 13:44:13
--  Relational Database:             STEEL
--  Standards Option:                DB2 UDB iSeries
SET PATH "QSYS","QSYS2","CISFGV" ;
CREATE PROCEDURE CSIDEV.MILLBUILD ( )
     DYNAMIC RESULT SETS 1
     LANGUAGE SQL
     SPECIFIC CSIDEV.MILLBUILD
     NOT DETERMINISTIC
     MODIFIES SQL DATA
     CALLED ON NULL INPUT
     SET OPTION  ALWBLK = *ALLREAD ,
     ALWCPYDTA = *OPTIMIZE ,
     COMMIT = *NONE ,
     DECRESULT = (31, 31, 00) ,
     DFTRDBCOL = *NONE ,
     DYNDFTCOL = *NO ,
     DYNUSRPRF = *USER ,
     SRTSEQ = *HEX  
P1 : BEGIN
     DECLARE AtEnd INTEGER;
     Declare MILL varchar(3);
     Declare DESCRIPT varchar(30);
     Declare VENDORS varchar(30);
     DECLARE C1 CURSOR for
          SELECT    
               substr(JBSV11DB.TPTBLLS.TABLE_CODE_TL,5,3),
               JBSV11DB.TPTBLLS.TABLE_DESCRIPT_TL,
               JBSV11DB.TPTBLLS.TABLE_STATUS_TL
          FROM JBSV11DB.TPTBLLS
          WHERE (JBSV11DB.TPTBLLS.TABLE_NUMBER_TL = 8220);
     DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
          set AtEnd = 1;
     DECLARE GLOBAL TEMPORARY TABLE SESSION.MILLS (
          tMILL VARCHAR ( 3 ) ,
          tDESCRIPT VARCHAR ( 30 ),
          tVEND VARCHAR ( 50 )
          ) WITH REPLACE NOT LOGGED ;
     OPEN C1;
     FETCH C1 INTO MILL, DESCRIPT, VENDORS;
     WHILE (AtEnd = 0) DO
          INSERT INTO SESSION.MILLS(tMILL, tDESCRIPT, tVEND)
               VALUES(MILL, DESCRIPT, VENDORS);
          FETCH C1 INTO MILL, DESCRIPT, VENDORS;
     END WHILE;
     CLOSE C1;
     DECLARE C2 CURSOR FOR SELECT * from FROM SESSION.MILLS ;
     OPEN C2;
END P1  ;
0
volkingAuthor Commented:
@Kent ... success! .... sort of ... sigh

Below code now compiles and creates the SP, but
Attempts to call it fail with error ...
====================================
SQL State: 42704
Vendor Code: -204
Message: [SQL0204] FROM in CISFGV type *FILE not found. Cause . . . . . :   FROM in CISFGV type *FILE was not found. If the member name is *ALL, the table is not partitioned. If this is an ALTER TABLE statement and the type is *N, a constraint or partition was not found. If this is not an ALTER TABLE statement and the type is *N, a function, procedure, trigger or sequence object was not found. If a function was not found, FROM is the service program that contains the function.  The function will not be found unless the external name and usage name match exactly.  Examine the job log for a message that gives more details on which function name is being searched for and the name that did not match. Recovery  . . . :   Change the name and try the request again.  If the object is a node group, ensure that the DB2 Multisystem product is installed on your system and create a nodegroup with the CRTNODGRP CL command. If an external function was not found, be sure that the case of the EXTERNAL NAME on the CREATE FUNCTION statement exactly matches the case of the name exported by the service program.

========================================
--  Generate SQL
--  Version:                  V5R4M0 060210
--  Generated on:            10/27/08 13:44:13
--  Relational Database:              STEEL
--  Standards Option:            DB2 UDB iSeries
SET PATH "QSYS","QSYS2","CISFGV" ;
CREATE PROCEDURE CSIDEV.MILLBUILD ( )
      DYNAMIC RESULT SETS 1
      LANGUAGE SQL
      SPECIFIC CSIDEV.MILLBUILD
      NOT DETERMINISTIC
      MODIFIES SQL DATA
      CALLED ON NULL INPUT
      SET OPTION  ALWBLK = *ALLREAD ,
      ALWCPYDTA = *OPTIMIZE ,
      COMMIT = *NONE ,
      DECRESULT = (31, 31, 00) ,
      DFTRDBCOL = *NONE ,
      DYNDFTCOL = *NO ,
      DYNUSRPRF = *USER ,
      SRTSEQ = *HEX  
P1 : BEGIN
      DECLARE AtEnd INTEGER;
      DECLARE MILL VARCHAR(3);
      DECLARE DESCRIPT VARCHAR(30);
      DECLARE VENDORS VARCHAR(30);
P2 : BEGIN
      DECLARE C1 CURSOR for
            SELECT      
                SUBSTR(JBSV11DB.TPTBLLS.TABLE_CODE_TL,5,3),
                  JBSV11DB.TPTBLLS.TABLE_DESCRIPT_TL,
                  JBSV11DB.TPTBLLS.TABLE_STATUS_TL
            FROM JBSV11DB.TPTBLLS
            WHERE (JBSV11DB.TPTBLLS.TABLE_NUMBER_TL = 8220);
      DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
            SET AtEnd = 1;
P3 : BEGIN
      DECLARE GLOBAL TEMPORARY TABLE MILLS (
            tMILL VARCHAR ( 3 ) ,
            tDESCRIPT VARCHAR ( 30 ),
            tVEND VARCHAR ( 50 )
            ) WITH REPLACE NOT LOGGED ;
P4: BEGIN
      OPEN C1;
      FETCH C1 INTO MILL, DESCRIPT, VENDORS;
      WHILE (AtEnd = 0) DO
            INSERT INTO MILLS(tMILL, tDESCRIPT, tVEND)
                  VALUES(MILL, DESCRIPT, VENDORS);
            FETCH C1 INTO MILL, DESCRIPT, VENDORS;
      END WHILE;
      CLOSE C1;
P5:BEGIN
      DECLARE C2 CURSOR for SELECT tMILL, tDESCRIPT, tVEND from FROM MILLS ;
      OPEN C2;
END P5 ;
END P4 ;
END P3 ;
END P2 ;
END P1 ;
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Gary PattersonVP Technology / Senior Consultant Commented:
DECLARE C2 CURSOR for SELECT tMILL, tDESCRIPT, tVEND from FROM MILLS ;DECLARE C2 CURSOR for SELECT tMILL, tDESCRIPT, tVEND from FROM MILLS ;

"FROM from" is your problem.

- Gary Patterson

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kent OlsenData Warehouse Architect / DBACommented:

Good Eyes, Gary!

0
Gary PattersonVP Technology / Senior Consultant Commented:
Since the error message said it was looking for and object named "FROM", a *FILE, I searched for "FROM" and the repeated word was obvious then.  Didn't see it on first read-though, though.

- Gary
0
volkingAuthor Commented:
Yet another lesson of ... READ THE FREAKING ERROR MESSAGE! (grin)
Thanks gary!

0
Gary PattersonVP Technology / Senior Consultant Commented:
:-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Operating Systems

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.