|
I've never know STRSQL to accept "programming-like" commands. I've never tried it, but what about RUNSQLSTM? -Walden ------------ Walden H Leverich III President Tech Software (516) 627-3800 x11 (208) 692-3308 eFax WaldenL@xxxxxxxxxxxxxxx http://www.TechSoftInc.com Quiquid latine dictum sit altum viditur. (Whatever is said in Latin seems profound.) -----Original Message----- From: Hall, Philip [mailto:phall@xxxxxxxx] Sent: Tuesday, October 07, 2003 2:38 PM To: Midrange Systems Technical Discussion 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 > > _______________________________________________ > 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. > > _______________________________________________ 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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.