AW: SQL Create Table and Host Variables

["Hauser, Birgitta" <Birgitta.Hauser@xxxxxxxxxxx>]

Hi Dave,

have you tried:
DECLARE GLOBAL TEMPORARY TABLE

This is the SQL-Way to create a temporary table, in other words creats a
file in the QTEMP.

An other way would be to use dynamic SQL to create your table:
Put the SQL-command in a String together and then execute it with EXECUTE
IMMEDIATE.

Dynamic SQL can be used in either embedded SQL or SQL/PL.

Birgitta

Mit freundlichen Grüßen
 
i.A. Birgitta Hauser
 
LUNZER + PARTNER GMBH
Unternehmensberatung
Carl-Zeiss-Straße 1
63755 Alzenau
 
Tel:         + 49 6023 951-255
Fax:        + 49 6023 951-111
Internet.  www.lp-gmbh.com
              www.rpg-schulung.de
 

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Smith, Dave
Gesendet: Dienstag, 20. September 2005 17:36
An: midrange-l@xxxxxxxxxxxx
Betreff: SQL Create Table and Host Variables

When I do a:

 

CREATE TABLE  QTEMP.TableName AS

(

SELECT            Fields

FROM               File

WHERE            Fielda = :Variable

) with Data

 

I get an error message that I can not use a host variable in this
statement.    I tried it in both embedded RPG and in a SQL procedure.

 

So instead I've been doing  

 

CREATE TABLE  QTEMP.TableName AS

(

SELECT            Fields

FROM               File

) with No Data;

 

INSERT INTO QTEMP.TABLENAME

SELECT            Fields

FROM               File

WHERE            Fielda = :Variable

 

Now I get my table with the data I need but I need to maintain my select
statement in two spots...On the CREATE TABLE and on the INSERT.
Anybody have a better method of using SQL to create a temporary file.
In this case I'm using an email package to email the file off in a CSV
file and then deleting the file. 

 

David Smith

IT Consultant