IF NOT EXIST syntax

xaphann · #1 (**permalink**) 07-22-09, 11:51

Hi

I am new to DB2 and have a simple problem. Trying to get an IF NOT EXIST statement work. Here is my code;

Code:

IF NOT EXISTS (select * from ABC.table1 where field1 = 1100200)
	THEN
		INSERT INTO ABC.table1 ("field1") VALUES (1100200);
END IF;

Toad keeps telling me there is an error at the first IF. My googling tells if not exist does work I just can't figure out what I am doing wrong.

n_i · #2 (**permalink**) 07-22-09, 12:05

IF cannot be used standalone in that context. Try wrapping your code in BEGIN ATOMIC ... END. Make sure you use the outer statement delimiter other than ";".

xaphann · #3 (**permalink**) 07-22-09, 12:11

hehe odd, I just tried;

Code:

BEGIN ATOMIC
IF NOT EXISTS (select * from ABC.table1 where field1 = 1100200)
	THEN
		INSERT INTO ABC.table1 ("field1") VALUES (1100200);
END IF;
END;

Although that goes on for what seems forever. Doing this;

Code:

BEGIN ATOMIC
IF NOT EXISTS (select * from ABC.table1 where field1 = 1100200)
	THEN
		INSERT INTO ABC.table1 ("field1") VALUES (1100200);
END IF;
END@

Returns the result of;

Code:

DB2 Database Error: ERROR [42601] [IBM][DB2/NT] SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "2,0);  END IF;  END@".  Expected tokens may include:  "JOIN <joined_table>".  LINE NUMBER=7.  SQLSTATE=42601

So still not sure what I am doing wrong.

DBFinder · #4 (**permalink**) 07-22-09, 16:07

did you this use syntax to run this code

>db2 -td@ -f mysql.db2

You need to tell DB2 about the terminator you used !!

xaphann · #5 (**permalink**) 07-22-09, 16:40

ahhh that worked, but not quite the solution I was looking for. Is there a way to set the terminator in the actual script?

Problem is that this script will be past to multiple people that will execute differently. Some will use Toad, some will use the Command line editor. Having them manually do this will just cause me more problem (your script doesn't work etc...).

Basically all I want is for the user load the script hit the execute button and it works.

DBFinder · #6 (**permalink**) 07-22-09, 16:42

--#set Terminator $

since this line on the terminator will be $

DBFinder · #7 (**permalink**) 07-22-09, 16:45

--#SET TERMINATOR $
This will ser the terminator to $
To change the terminator in the CLP on the fly, the following syntax will set it back:
--#SET TERMINATOR