RE: Create a missing table in SQL ?

["Hall, Philip" <phall@xxxxxxxx>]

Thanks Bill.

The problem I'm really having is that I can't seem to get the interactive SQL 
tool to accept any SQL that uses DECLARE xxx

In SQLServer & Oracle, I can type SQL as below into their respective 
interactive SQL tools (to test) and it gets executed.

For SQLServer the working SQL is;

if exists (select * from dbo.sysobjects where id = object_id(N'[0a0a48cc0018cb3a
000000f6433b730e8188]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
   TRUNCATE TABLE [0a0a48cc0018cb3a000000f6433b730e8188] 
ELSE 
   CREATE TABLE "0a0a48cc0018cb3a000000f6433b730e8188" 
   (    MODEL_ID nvarchar (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
        ENTY_ID varchar (255) NULL,
        ...
    )

For Oracle it's;

    declare n number;
    table_not_exist exception;
    pragma exception_init(table_not_exist,-00942);
    begin 
        execute immediate 'truncate table "$user$"."$tableName$"';
        commit; 
        exception 
        when table_not_exist then
            execute immediate 'CREATE TABLE 
                $tableName$ 
                ( MODEL_ID varchar (255) NULL, 
                ENTY_ID varchar (255) NULL, 
                ...
                ) TABLESPACE "SMS_DM_CRMCAT"';
        commit;
    end;

Both work as is in the interactive SQL tool.

What's up with the UDB2 tool ?

--phil

> -----Original Message-----
> From: Bill [mailto:billzbubb@xxxxxxxxxx]
> Sent: Tuesday, October 07, 2003 1:20 PM
> To: Midrange Systems Technical Discussion
> Subject: Re: Create a missing table in SQL ?
> 
> 
> 
> Hall, Philip wrote:
> > BEGIN ATOMIC
> >     DECLARE v_rc INT DEFAULT 0;
> >     SELECT COUNT(*) INTO v_rc
> >         FROM SYSCAT.TABLES
> >         WHERE TABNAME = 'NAMES' ;
> >     IF v_rc = 0 THEN
> >         CREATE TABLE NAMES........;
> >     ELSE
> >   DROP TABEL NAMES;
> >         CREATE TABLE NAMES........;
> >     END IF;
> > END
> 
> Just a suggestion:  Why not change the If statement to be <> 
> 0 then Drop
> the table.  Then Create the table.
> 
> Bill
> 
> _______________________________________________
> This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> 
>