DB2 - If table is empty for date X, insert, else go on

Question

--DB2 version 10 on AIX

I have a stored procedure, which I need to update. And want to check if there is data for a certain date. If data exists, go on, else run insert and then go on.

IF (SELECT COUNT(*)
    FROM SCHEMA1.TABLE1_STEP1 
    WHERE XDATE = '9/27/2014' < 1)
  THEN (INSERT INTO SCHEMA1.TABLE1_STEP1 (SELECT * FROM SCHEMA2.TABLE2 FETCH FIRST 2 ROWS ONLY))
END IF;

This errors-out.

DB2 Database Error: ERROR [42601] [IBM][DB2/AIX64] SQL0104N An unexpected token "(" was found following "/2014') < 1) THEN". Expected tokens may include: "". SQLSTATE=42601

Any thoughts on what's wrong?

bhamby bhamby · Accepted Answer · 2014-10-31T20:00:46

I'm guessing you probably want the less than sign outside of the parenthesis...

However, as an aside, you can also do this kind of statement without an IF (although, I don't have an AIX DB2 available to check for sure. It worked on DB2 for z/OS and LUW, however):

INSERT INTO SCHEMA1.TABLE1_STEP1
    SELECT *
    FROM SCHEMA2.TABLE2
    WHERE NOT EXISTS (
        SELECT *
        FROM SCHEMA1.TABLE1_STEP1 
        WHERE XDATE = '9/27/2014'
    )
    FETCH FIRST 2 ROWS ONLY

Also, you're not providing an ORDER BY on the SCHEMA2.TABLE2 select, so your results could come back in any order (whatever is "easiest" for the database engine)... order is not guaranteed unless you provide the ORDER BY statement.

DB2 - If table is empty for date X, insert, else go on

1 Answers