Results 1 to 9 of 9
  1. #1
    Join Date
    May 2013
    Posts
    4

    Question Unanswered: IF in SQL statements

    Hi All!

    I'm experienced in SQL server but new to DB/2.
    In SQL Server I can fire a SQL statement with conditions und multiple statements like this:

    if exists(Select * from customers where customerNr = '12')
    update customers set Name = 'Bla' where customerNr = '12'
    else
    insert into customers (customerNr, name) values('12','bla)

    I found out that there is a IF statement in DB/2 too, but I can't get it working.
    When I type in a similar statement (Data Studio) and fire it, I always get errors and he's unable to understand me. Are these IF statements only allowed in stored procedures? Isn't it possible to create a SQL statement that does exactly what is mentioned above in one go?
    Background: I need to call DB/2 from a .NET program but only have a chance for a single statement and unable to implement program logic on the .NET side.

    Thanks in advance for your help.

    Kind regards,

    Patrick Theobald

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    MERGE statement may be an answer.

  3. #3
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Code:
    BEGIN
    
    DECLARE V_EXISTS SMALLINT;
    SET V_EXISTS = (SELECT 1 FROM yourTable WHERE yourCondition FETCH FIRST 1 ROWS ONLY);
    IF (V_EXISTS IS NULL) THEN
      -- row does not exists, do something
    ELSE
      -- row exists, do something else
    END IF;
    END@
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  4. #4
    Join Date
    May 2013
    Posts
    4
    Hi All!

    @tonkuma: Things a little bit more complicated and MERGE won't do it. But thanks anyway.

    @Florin: Thanks. That's what I thought. I adjusted your suggestion to my need and get a lot of errors when firing the statement in Data Studio. I attached a screenshot. Or is it not possible at all doing such things in data studio?

    Thanks in advance to everyone.

    Cheers,

    Patrick

    PS: Just to make the goal clear: I want to create a table and drop the old one, if an old one exists....
    Attached Thumbnails Attached Thumbnails db2problem.png  

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Clive76 View Post
    get a lot of errors when firing the statement in Data Studio.
    Did you forget to change the statement terminator character to "@", by chance?
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    May 2013
    Posts
    4
    Hi!

    the terminator character did the trick. Thanks!
    But I still can get working what I try to do.
    I want to drop a table but only if the table is already there:

    Code:
    BEGIN
    
    DECLARE V_EXISTS SMALLINT;
    SET V_EXISTS = (select tabname from syscat.tables where  tabschema='myschema' and tabname='KUNDEN');
    IF (V_EXISTS IS NULL) THEN
      drop table KUNDEN;
    END IF;
    END@
    Could it be, that I can't mix up DDL and SQL commands here?
    With SQL server similiar statements are no problem, but a friend told me, that this is often doesn't with other DBs.

    Kind regards,

    Patrick

  7. #7
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Topic title is little misleading. So I want te emphasize:
    - "IF" is no valid SQL keyword (use WHEN instead)
    - "IF" is valid in the SQL-PL syntax
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  8. #8
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    You cannot mix DML and DDL. I usually do this with EXECUTE IMMEDIATE:

    IF (V_EXISTS IS NULL) THEN
    EXECUTE IMMEDIATE 'DROP TABLE KUNDEN';
    END IF
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  9. #9
    Join Date
    May 2013
    Posts
    4
    Hi All!

    Thank you very much for all your help!
    The EXECUTE IMMEDIATE was exactly what I was looking for.
    Everything works now!

    Kind regards,

    Patrick

Posting Permissions

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