Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2002
    Posts
    11

    Unanswered: Control Flow Error

    I'm trying to do a simple control flow statement and I am continuing to get errors. I've tried a number of different things and still an unable to get this "what seems to be simple procedure" to work. Can anyone point me in the right direction. Thanks

    Here is the SQL

    BEGIN ATOMIC
    FOR row AS
    SELECT valueid FROM MED.DMVALUES where valuedid = 1000
    DO
    IF row.valueid is NULL
    THEN Insert Into Med.dmvalues values (1000,'Test',1,1,1,'System','2005-1-20 04:12:12',1,1,1000) ;
    END IF;
    END FOR;
    END

    Here is the error...

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "END-OF-STATEMENT" was found following
    "04:12:12',1,1,1000)". Expected tokens may include: "<delim_semicolon>".
    LINE NUMBER=6. SQLSTATE=42601

    END IF
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END IF".
    Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    END FOR
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END
    FOR". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    END
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END".
    Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END". Expected tokens may include: "JOIN <joined_table> ".

    Explanation:

    A syntax error in the SQL statement was detected at the specified
    token following the text "<text>". The "<text>" field indicates
    the 20 characters of the SQL statement that preceded the token
    that is not valid.

    As an aid to the programmer, a partial list of valid tokens is
    provided in the SQLERRM field of the SQLCA as "<token-list>".
    This list assumes the statement is correct to that point.

    The statement cannot be processed.

    User Response:

    Examine and correct the statement in the area of the specified
    token.

    sqlcode : -104

    sqlstate : 42601

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    It is most likely a line termination character problem. How do you run the statement? In CLP?

    Besides, you could accomplish what you wanted with a single insert statement:
    Code:
    Insert Into Med.dmvalues 
      select 1000,'Test',1,1,1,'System','2005-1-20 04:12:12',1,1,1000 
      FROM MED.DMVALUES 
      where valuedid = 1000 and valueid is NULL

  3. #3
    Join Date
    Sep 2002
    Posts
    11

    CF Error

    I've tried it through CLP, command editer, Auqa Data (Third Party). Same results unfortunately.

    I've tried your suggestion and it doesn't seem to do anything. Maybe I am misunderstanding it but it will never do anything since valueid will never be both 1000 and null. Maybe I'm missing something.

    I'm just basically trying to insert a value into a table if and only if it doesn't already exists.

    Thanks

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by dseifried
    I've tried your suggestion and it doesn't seem to do anything. Maybe I am misunderstanding it but it will never do anything since valueid will never be both 1000 and null.
    Well, then neither will your own code:
    Code:
    FOR row AS
    SELECT valueid FROM MED.DMVALUES where valuedid = 1000
    DO
    IF row.valueid is NULL
    I figured that valuedid and valueid are different fileds; apperently it's just a typo. In that case your code will never execute the IF statement anyway.

    What you need to do is this:
    Code:
    if not exists (SELECT valueid FROM MED.DMVALUES where valuedid = 1000)
    then
      insert ... ;
    end if;
    Save your code into a file, say ins.sql; add a "#" after the last line, and run it like this:"db2 -td# -f ins.sql".

    The above tells db2 to parse statements terminated by "#", not by ";" - other wise it gets confused as to where a statement ends, hence the errors you've mentioned.

    Hope this helps.

  5. #5
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    test this:
    -------------------
    BEGIN ATOMIC
    FOR row AS
    SELECT valueid FROM MED.DMVALUES where valueid not in (select valueid from another_table)
    DO
    Insert Into Med.dmvalues values ( mycolumns) ;

    END IF;
    END FOR;
    END
    -----------------
    or
    -----------------
    declare continue handler for sqlexceptio, sqlwarning, not found;
    Insert Into Med.dmvalues values (1000,'Test',1,1,1,'System','2005-1-20 04:12:12',1,1,1000) ;

    END
    -----------------

    the continue handler is for error control.

    abel.

  6. #6
    Join Date
    Sep 2002
    Posts
    11
    I apoligize.....there is a typo. Both fields should be valueid not one field being valuedid. However, the error is still happening. I will try a different termination through CLP and see what happens. I started with If Not Exists(select....) and couldn't get it to work either but the term char might have been the issue.

  7. #7
    Join Date
    Sep 2002
    Posts
    11
    I've put the following into a file called testscript.sql and ran this from the command line. db2 -td# -f testscript.sql

    Here is the error.

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "exists" was found following "if not ".
    Expected tokens may include: "JOIN". SQLSTATE=42601
    Here is my syntax

    connect to medical#
    if not exists (SELECT valueid FROM MED.DMVALUES where valueid = 1000)
    then
    Insert Into Med.dmvalues values (1000,'Test',1,1,1,'System','2005-1-20 04:12:12',1,1,1000);
    end if;#

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think that you've lost the "BEGIN ATOMIC..." part somewhere...

  9. #9
    Join Date
    Sep 2002
    Posts
    11
    That did it. Thanks much for your help.

Posting Permissions

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