RE: Create a missing table in SQL ?

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

To make one more confession, I'm using the Java based interactive SQL tool that 
comes with the Windows version of UDB2.

So I wasn't really being fair, although I'd still like to know the answer, as 
this SQL code will still end up running in DB2 via a JDBC SQL statement, as the 
other two do today.

--phil

> -----Original Message-----
> From: Walden H. Leverich III [mailto:WaldenL@xxxxxxxxxxxxxxx]
> Subject: RE: Create a missing table in SQL ?
> 
> I've never know STRSQL to accept "programming-like" commands. 
> I've never
> tried it, but what about RUNSQLSTM? 
> 
> -Walden 
>  
> -----Original Message-----
> From: Hall, Philip [mailto:phall@xxxxxxxx] 
> Subject: RE: Create a missing table in SQL ?
> 
> 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