Check if a table exists in System i and then drop it

Question

I'm trying to write an SQL statement that will check whether a table exists and, if so, dropping it in System i.

I assumed it would be a simple IF EXISTS statement but I'm having trouble with it and unsure how to proceed.

The statement in full:

IF EXISTS (SELECT TABLE_NAME FROM <<DatabaseName>>.VIEWS         
WHERE TABLE_NAME = 'TABLE')         
DROP VIEW <<DatabaseName>>.TABLE
GO

It comes back with the error

Keyword IF not expected

I'm a complete beginner with System i but I've read about procedures can get round this?

Answer 1

You can use an IF statement inside a stored procedure, so you could accomplish this with something like:

CREATE PROCEDURE CREATE_TABLE (
    IN IN_TABLE_NAME VARCHAR(128), 
    IN IN_TABLE_SCHEMA VARCHAR(128),
    IN IN_TABLE_DEF VARCHAR(4000))

    LANGUAGE SQL MODIFIES SQL DATA

    DECLARE CNT INT DEFAULT 0;
    DECLARE STMT VARCHAR(1000);
    SELECT COUNT(*) INTO CNT
      FROM QSYS2/SYSTABLES
      WHERE TABLE_NAME = IN_TABLE_NAME and TABLE_SCHEMA = IN_TABLE_SCHEMA

    CASE CNT
        WHEN 1 THEN
            SET TABLE_NAME = 'CORPDATA.DEPT_' CONCAT P_DEPT CONCAT '_T';
            SET STMT = 'DROP TABLE ' || IN_TABLE_SCHEMA || '/' || IN_TABLE_NAME;
            PREPARE S1 FROM STMT;
            EXECUTE S1;
    END CASE

    SET STMT = 'CREATE TABLE ' || IN_TABLE_SCHEMA || '/' || IN_TABLE_NAME || 
                IN_TABLE_DEF;
    PREPARE S1 FROM STMT;
    EXECUTE S1;

You could then call this proc passing in the table name, schema, and column definition:

CALL CREATE_TABLE('MYTABLE', 'MYSCHEMA', '(
    COL1 INTEGER NOT NULL, 
    COL2 VARCHAR(100))';

I haven't actually tested this, but this should give you a basic idea of what you need to do.