× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

 


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.