If table not exists create else delete from

jkuyken · #1 (**permalink**) 06-02-10, 10:26

I am looking to check to see if a table exists. If it does, I want to delete every row in it. If the table does not exist, I want to create the table. This is all preparation to use the table.

When I put the pieces together, it does not work. As three separate statements, they work assuming that the table is created or is not created as needed. Together they fail. Any ideas? I must have something rather simple wrong.

Code:

IF 
    ( 
        ( 
        SELECT 
            COUNT(*) 
        FROM 
            SYSCAT.TABLES 
        WHERE 
            TABSCHEMA   = 'TEST_SCHEMA' 
            AND TABNAME = 'TEST_TABLE' 
        ) 
        = 0 
    ) 
    BEGIN 
        CREATE 
            TABLE TEST_SCHEMA.TEST_TABLE 
            ( 
                ID INT 
            ) 
        END 
    ELSE 
        BEGIN 
            DELETE 
            FROM 
                BISDCUST.ACE_GRADES_AUSTIN 
            END
            ;

ARWinner · #2 (**permalink**) 06-02-10, 10:31

What database version and OS are you using? What is the error you are getting?

Andy

dbzTHEdinosaur · #3 (**permalink**) 06-02-10, 11:38

why don't you delete the table, regardless, ignore the return if no table exists
and
then create the table?

jkuyken · #4 (**permalink**) 06-03-10, 10:23

Quote:

Originally Posted by dbzTHEdinosaur

why don't you delete the table, regardless, ignore the return if no table exists
and
then create the table?

The create takes longer than delete on our system. I would prefer the faster solution.

dbzTHEdinosaur · #5 (**permalink**) 06-03-10, 10:58

Quote:

The create takes longer than delete on our system. I would prefer the faster solution.

that makes a lot of sense.

jkuyken · #6 (**permalink**) 06-03-10, 11:48

Anyone know of a try/catch equivalent on db2?

I googled sql try catch, but the result is for mssql. Pretty cool actually. I tried it on our mssql box, but the db2 box didn't like it.

mssql:

Code:

BEGIN TRY
    CREATE TABLE myschema.DBO.TRYTHIS
    (
        ID VARCHAR(8)
    )
END TRY
BEGIN CATCH
    DROP TABLE myschema.DBO.TRYTHIS
END CATCH

A solution like this would speed many of our queries because sometimes subqueries need to be run twice such as

Code:

with sq as
(select value from sourcetable where cond)
select case when sq is null then 0 else sq from sourcetable2

if the query could be rewritten as

Code:

with sq as
(select value from sourcetable where cond)
select begin try sq end try begin catch 0 end catch from sourcetable2

The query would execute once instead of twice for every row.

n_i · #7 (**permalink**) 06-03-10, 12:17

Quote:

Originally Posted by jkuyken

Anyone know of a try/catch equivalent on db2?

Check out condition handlers in the DB2 manual.

jkuyken · #8 (**permalink**) 06-03-10, 23:53

No manual! I will look into acquiring one. :-)

n_i · #9 (**permalink**) 06-04-10, 10:28

Quote:

Originally Posted by jkuyken

No manual! I will look into acquiring one. :-)

Consider it acquired: IBM DB2 9.7 for Linux, UNIX and Windows Information Center

jkuyken · #10 (**permalink**) 07-20-10, 16:56

Condition Handlers heading in 'the manual'

Internet Services

Web Development

Digital Marketing

Consumer Tech