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

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:

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

Well, then neither will your own code: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: 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.

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.

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.

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;#

I think that you've lost the "BEGIN ATOMIC..." part somewhere...

That did it. Thanks much for your help.