Solved

SQL Syntax help for AS400 iSeries Proc

Posted on 2008-10-27
8
2,448 Views
Last Modified: 2013-12-06
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  ;
0
Comment
Question by:volking
  • 3
  • 3
  • 2
8 Comments
 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 100 total points
ID: 22815549
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
 
LVL 5

Author Comment

by:volking
ID: 22815753
@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
 
LVL 5

Author Comment

by:volking
ID: 22816137
@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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 34

Accepted Solution

by:
Gary Patterson earned 400 total points
ID: 22816338
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
 
LVL 45

Expert Comment

by:Kdo
ID: 22816467

Good Eyes, Gary!

0
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 22816642
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
 
LVL 5

Author Comment

by:volking
ID: 22816744
Yet another lesson of ... READ THE FREAKING ERROR MESSAGE! (grin)
Thanks gary!

0
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 22817291
:-)
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As the title indicates, I have done this before. It chills me everytime I update the OS on my phone, (http://www.experts-exchange.com/articles/18084/Upgrading-to-Android-5-0-Lollipop.html) because one time I did this and I essentially had a bricked …
Windows 10 is here and for most admins this means frustration and challenges getting that first working Windows 10 image. As in my previous sysprep articles, I've put together a simple help guide to get you through this process. The aim is to achiev…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question