Solved

SQL Syntax help for AS400 iSeries Proc

Posted on 2008-10-27
8
2,413 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
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
In this article we will discuss all things related to StageFright bug, the most vulnerable bug of android devices.
This video discusses moving either the default database or any database to a new volume.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now