Results 1 to 10 of 10
  1. #1
    Join Date
    May 2010
    Posts
    21

    Question Unanswered: If table not exists create else delete from

    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
                ;

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What database version and OS are you using? What is the error you are getting?

    Andy

  3. #3
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    why don't you delete the table, regardless, ignore the return if no table exists
    and
    then create the table?
    Dick Brenholtz, Ami in Deutschland

  4. #4
    Join Date
    May 2010
    Posts
    21
    Quote Originally Posted by dbzTHEdinosaur View Post
    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.

  5. #5
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    The create takes longer than delete on our system. I would prefer the faster solution.
    that makes a lot of sense.
    Dick Brenholtz, Ami in Deutschland

  6. #6
    Join Date
    May 2010
    Posts
    21
    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.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by jkuyken View Post
    Anyone know of a try/catch equivalent on db2?
    Check out condition handlers in the DB2 manual.

  8. #8
    Join Date
    May 2010
    Posts
    21

    That must be part of my problem

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

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by jkuyken View Post
    No manual! I will look into acquiring one. :-)
    Consider it acquired: IBM DB2 9.7 for Linux, UNIX and Windows Information Center

  10. #10
    Join Date
    May 2010
    Posts
    21

Tags for this Thread

Posting Permissions

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