Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2012
    Posts
    4

    Unanswered: SQL0104 Error - Token ; was not valid

    I tried to create DB2 Procedure with below SQL Statement, and got a SQL0104 error. Have searched for answers, but no resolution yet.

    Here is the SQL Statement:
    DECLARE GLOBAL TEMPORARY TABLE TEST3(TBLNM char(50),RECCNT int);
    INSERT INTO SESSION.TEST3 SELECT 'CLIENT.INFO', COUNT(*) FROM CLIENT.TABLE;
    SELECT * FROM SESSION.TEST3 ;


    Here is the Error Message:
    Token ; was not valid. Valid tokens: <END-OF-STATEMENT>.

    Message ID: SQL0104

    Cause . . . . . : A syntax error was detected at token ;. Token ; is not a valid token. A partial list of valid tokens is <END-OF-STATEMENT>. 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 ;. 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.


    Thank you in advance.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Token ; was not valid. Valid tokens: <END-OF-STATEMENT>.
    May be it's related to a statement-termination-character.
    If so, this is a FAQ.

    Try to specify a statement-termination-character other than a semicolon(";").

    The way to specify a statement termination character might be different by each client tools.
    There must be some threads in this forum on this topic.
    Or see links in second post from last in this thread.
    developerWorks : Information Management : IBM DB2 for Linux, Unix, and Windows Forum : Delete Script 250 rows at time ...

  3. #3
    Join Date
    Jul 2012
    Posts
    4
    I have also tried the statement-termination-character into the code, and failed:
    db2 -t
    DECLARE GLOBAL TEMPORARY TABLE TEST3(TBLNM char(50),RECCNT int);
    INSERT INTO SESSION.TEST3 SELECT 'CLIENT.INFO', COUNT(*) FROM CLIENT.TABLE;
    SELECT * FROM SESSION.TEST3;

    or

    db2 -td%
    --#SET TERMINATOR %
    DECLARE GLOBAL TEMPORARY TABLE TEST3(TBLNM char(50),RECCNT int)%
    INSERT INTO SESSION.TEST3 SELECT 'CLIENT.INFO', COUNT(*) FROM CLIENT.TABLE%
    SELECT * FROM SESSION.TEST3%


    I use IBM System i Navigator version 6.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try something like...

    db2 -td%
    --#SET TERMINATOR %
    BEGIN ATOMIC
    DECLARE GLOBAL TEMPORARY TABLE TEST3(TBLNM char(50),RECCNT int);
    INSERT INTO SESSION.TEST3 SELECT 'CLIENT.INFO', COUNT(*) FROM CLIENT.TABLE;
    SELECT * FROM SESSION.TEST3;
    END%

  5. #5
    Join Date
    Jul 2012
    Posts
    4
    I tried your code and still got error message:
    Token - was not valid. Valid tokens: :.

    Message ID: SQL0104

    Cause . . . . . : A syntax error was detected at token -. Token - is not a valid token. A partial list of valid tokens is :. 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 -. 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.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Where was "Token - " in your tried code?
    Find it and remove it(and may be some more related code).

    Note: I don't know System i Navigator. So, some more adjustments might be necessary.

  7. #7
    Join Date
    Jul 2012
    Posts
    4
    The "-" sign is within the statment "db2 -td%".

    Have removed and tried couple code changes, but still have error.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    "db2 -td%" is not the part of SQL code.
    It is part of command to execute SQL code.

    Or, it might be not a command on DB2 for i Series. It might be specific to DB2 for LUW.

    I don't know commands specific to DB2 for i to execute SQL code.
    Please ask to experts of DB2 for i Series,
    or read manuals IBM i information center


    I'm sorry not to be some help to you and might mislead you.
    Last edited by tonkuma; 07-12-12 at 19:31.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •