CODEWITHSUNDEEP
sqldb2
denes bastos
denes bastos

Reputation: 39

DB2 Create Table if not exists without columns

how is the simplest way to test if some column exists in DB2? i am just trying to run a script that add a column. I don't want a procedure. It is necessery to use Begin End or create a procedure?

IF (NOT EXISTS(
SELECT * FROM P1ADM549.T549RMOP WHERE VR_PGO ='VR_PGO' AND COLNAME = 'VR_PGO'))
THEN
    EXECUTE something...
END

Upvotes: 1

Views: 3096

Answers (4)

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

You can use a continue handler that "swallows" 42711:

#> db2 "create table t1 ( x int )"
DB20000I  The SQL command completed successfully.

#> db2 "begin DECLARE CONTINUE HANDLER FOR SQLSTATE '42711' BEGIN END; EXECUTE IMMEDIATE 'alter table t1 add column y int'; end"
DB20000I  The SQL command completed successfully.

#> db2 "begin DECLARE CONTINUE HANDLER FOR SQLSTATE '42711' BEGIN END; EXECUTE IMMEDIATE 'alter table t1 add column y int'; end"
DB20000I  The SQL command completed successfully.

Upvotes: 2

AngocA
AngocA

Reputation: 7693

In DB2 you have two option, that you can even combine:

First, you can define the IF ELSE in a anonymous block, which is defined by BEGIN END

BEGIN
 DECLARE EXIST BOOLEAN;
 IF (EXIST = TRUE) THEN
  ...
 ELSE
  ...
 END IF;
END @

More info at: https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/anonymous_block?lang=en

The second thing you can do is exploit the capabilities of condition compilation. However, you have to define the CC_FLAGS and this is more for scripting (http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/c0056403.html?cp=SSEPGG_10.5.0%2F2-12-7-3)

Upvotes: 0

Dan Guzman
Dan Guzman

Reputation: 46202

DB2 provides the ANSI standard INFORMATION_SCHEMA views so you can check for existence of a column with the technique below. I'm more of a T-SQL than a DB2 guy so you might need to tweak the control flow statements.

IF NOT EXISTS(
    SELECT * 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
        TABLE_SCHEMA = 'P1ADM549'
        AND TABLE_NAME = 'T549RMOP'
        AND COLUMN_NAME = 'VR_PGO'
    )
BEGIN
    --do something
END;

Upvotes: 0

Rahul
Rahul

Reputation: 77866

AFAIK, you can't use IF .. ELSE construct in normal SQL; you will have to include your IF .. ELSE condition inside a stored routine (procedure or function).

In your case, if EXECUTE something part is meant to executing a SQL query then probably you can use NOT EXISTS in your WHERE condition. Something like below but needs more clarification from your end.

your_select_query
where not exists
(
SELECT 1 
FROM P1ADM549.T549RMOP 
WHERE VR_PGO ='VR_PGO' 
AND COLNAME = 'VR_PGO')

Upvotes: 1

Related Questions