Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2002
    Posts
    11

    Unanswered: Unable to write compound SQL - using Toad for DA via iSeries ODBC driver

    Tried every possible combination of BEGIN, SET, DECLARE, etc. - with no results.

    My goal is to declare and set a variable - a date - and then run SELECT statement where that variable would be referenced a few times.

    Tried everything - no go. This is why I started thinking that perhaps it is a driver used to connect. The driver is iSeries ODBC driver for Windows and the editor I am using is Toad for Data Analysts (although that part is I am sure inconsequential).

    Would appreciate any and all ideas/suggestions/etc.

    Here are a few of the errors:
    Error 2/16/2011 9:04:35 AM 0:00:00.046 [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0199 - Keyword SQL not expected. Valid tokens: DECLARE. 55 0
    Error 2/16/2011 9:05:00 AM 0:00:00.062 [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0199 - Keyword SELECT not expected. Valid tokens: <END-OF-STATEMENT>. 55 0
    Error 2/16/2011 9:05:13 AM 0:00:00.062 [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token ; was not valid. Valid tokens: <END-OF-STATEMENT>. 55 0
    Error 2/16/2011 9:17:15 AM 0:00:00.046 [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0199 - Keyword SELECT not expected. Valid tokens: <END-OF-STATEMENT>. 55 0
    Error 2/16/2011 9:17:32 AM 0:00:00.109 [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0084 - SQL statement not allowed. 55 0

  2. #2
    Join Date
    Mar 2002
    Posts
    11

    Unable to write compound SQL

    My database says OS/400 5.4.0 (from the db2connect output).
    What I would like to do is something very basic, e.g.:

    BEGIN
    select current date from sysibm.sysdummy1;
    END;

    which gives me the following:
    Category Timestamp Duration Message Line Position
    Error 2/16/2011 10:26:25 PM 0:00:01.234 [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0199 - Keyword SELECT not expected. Valid tokens: DECLARE. 66 0

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by v8625 View Post
    something very basic, e.g.:

    BEGIN
    select current date from sysibm.sysdummy1;
    END;
    Compound SQL is subject to the same rules as SQL in stored procedures. In particular, you have to return the query result into something: SELECT CURRENT DATE INTO MYVAR_DATE FROM...

    Also, a compound statement does not end with a semicolon.

  4. #4
    Join Date
    Mar 2002
    Posts
    11

    Passing variables to SELECT statements

    My bad - I should not have used the word COMPOUND. What I was trying to do is to find a way to pass a variable to the SELECT statement - not INTO...

    BEGIN ATOMIC
    DECLARE zz INTEGER;
    SET zz = 1110217;
    SELECT * FROM table5 WHERE table5.field5 = zz;
    END

    to which it replies
    SQL0104 - Token ATOMIC was not valid. Valid tokens: DECLARE.

Posting Permissions

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