Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2009
    Posts
    7

    Unanswered: Drop Table in If-Else loop

    hi all,
    my aim is check if object exists then drop him
    i've query:
    --#SET TERMINATOR @
    BEGIN ATOMIC
    IF EXISTS (SELECT name FROM sysibm.systables WHERE name = 'TBL_NAME) THEN DROP TABLE 'TBL_NAME';
    END IF;
    END@

    The error is:
    DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command.
    SQL0104N An unexpected token "drop table" was found following "= 'tbl_name')
    THEN ". Expected tokens may include: "<signal_stmt_head>". LINE NUMBER=3.
    SQLSTATE=42601

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You cannot use a variable in a DDL .. YOu have to use dynamic sql

    An alternative is to drop and ignore the warning if the table does not exists ..
    Pseudocode:
    Code:
    CREATE PROCEDURE DROPT
    
    P1: BEGIN
    -- Declare variable
    DECLARE v_sqlstate_test CHAR(5); 
    DECLARE v_sqlcode_test INT;
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE SQLCODE INT DEFAULT 0;
    DECLARE v_TABLE_NOT_FOUND INT DEFAULT 0;
    DECLARE c_TABLE_NOT_FOUND CONDITION FOR SQLSTATE '56098';
    
    DECLARE CONTINUE HANDLER FOR c_TABLE_NOT_FOUND 
    SET v_TABLE_NOT_FOUND = 1; 
    
    DROP TABLE T1 ; 
    IF (v_TABLE_NOT_FOUND ==1 ) tHEN "GO TO NEXT STEP" ELSE "gO TO NEXT STE" 
    END IF
    END IF;
    END P1
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jul 2009
    Posts
    7
    Thank you for your quick answer.
    Maybe can you tell me how can i print text in DB2 (like dbms_output in oracle)

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Unless you are using DB2 9.7 - you have to use udfs ..

    Making Operating System Calls from SQL

    The above article (by Stolze, who freqents this forum) gives you the examples

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Jul 2009
    Posts
    7
    hi,
    I tied to run your code, but didn't succeed
    Can you explain your code
    thanks

  6. #6
    Join Date
    Jul 2009
    Posts
    7
    What's wrong in this query ?

    --#SET TERMINATOR @
    BEGIN ATOMIC
    --DECLARE TXT VARCHAR(100);
    IF EXISTS (SELECT name FROM sysibm.systables WHERE name = 'QA_TBL') THEN
    SELECT 'DROP TABLE ' || NAME AS TXT FROM sysibm.systables WHERE name = 'QA_TBL' ;
    EXECUTE IMMEDIATE = TXT;
    END IF;
    END @

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    ELECT 'DROP TABLE ' || NAME INTO TXT FROM sysibm.systables WHERE name = 'QA_TBL' ;
    EXECUTE IMMEDIATE TXT;
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Jul 2009
    Posts
    7
    SELECT 'DROP TABLE ' || NAME INTO TXT FROM sysibm.systables WHERE name = 'QA_TBL' ;

    wrong syntax

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I know !! sorry, i knew ...

    Visit the new-look IDUG Website , register to gain access to the excellent content.

  10. #10
    Join Date
    Jul 2009
    Posts
    7
    Quote Originally Posted by sathyaram_s
    I know !! sorry, i knew ...

    so what can i do ?

  11. #11
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Read the error message and the corresponding explanation in the manual ..

    If you have a clarification, post here
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  12. #12
    Join Date
    Jul 2009
    Posts
    7
    I've solution
    1. Create SP(IN TBL VARCHAR(100)) that drop table
    2. IF EXISTS (SELECT name FROM sysibm.systables WHERE name = 'QA_TBL') THEN CALL SP(TBL)

Posting Permissions

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