Solved

SQL Syntax help for AS400 iSeries Proc

Posted on 2008-10-27
8
2,510 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 45

Assisted Solution

by:Kent Olsen
Kent Olsen 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
Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

 
LVL 35

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:Kent Olsen
ID: 22816467

Good Eyes, Gary!

0
 
LVL 35

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 35

Expert Comment

by:Gary Patterson
ID: 22817291
:-)
0

Featured Post

Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

Question has a verified solution.

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

Many people tend to confuse the function of a virus with the one of adware, this misunderstanding of the basic of what each software is and how it operates causes users and organizations to take the wrong security measures that would protect them ag…
I don't know if many of you have made the great mistake of using the Cisco Thin Client model with the management software VXC. If you have then you are probably more then familiar with the incredibly clunky interface, the numerous work arounds, and …
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.

717 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