Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2009
    Posts
    3

    Unanswered: IF NOT EXIST syntax

    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.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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 ";".
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jul 2009
    Posts
    3
    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.

  4. #4
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    did you this use syntax to run this code

    >db2 -td@ -f mysql.db2

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

  5. #5
    Join Date
    Jul 2009
    Posts
    3
    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.

  6. #6
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    --#set Terminator $

    since this line on the terminator will be $

  7. #7
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    --#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

  8. #8
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    For me the logic "IF --- END IF" is extra. You have to use:

    INSERT INTO ABC.table1 ("field1")
    select 1100200
    from sysibm.sysdummy1
    where
    NOT EXISTS (select * from ABC.table1 where field1 = 1100200)
    Lenny K.

  9. #9
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    good idea

    thanks

  10. #10
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    No problem !

Posting Permissions

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