AW: SQL newbie question

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

Hi,

>>(dammit, who needs journaling or commitment control 
>>on a temporary file?!?!) 

Instead of creating a table with CREATE TABLE in the QTEMP library, 
you should use the SQL-Command DECLARE GLOBAL TEMPORARY TABLE,
which is the SQL way to create a temporary table.

DECLARE GLOBAL TEMPORARY TABLE WTGRPW 
as (select   CID from WTCONT CO
       where CO.U01010 = '1')             
WITH DATA
WITH REPLACE

WITH REPLACE will delete an existing table with the same name in the QTEMP
and CREATE a new table.
Specifying WITH DATA will fill the table. If DEFINITION ONLY is specified
instead, an empty table is created in the QTEMP. To fill this temporary
table, you can use an insert statement like you did before, but WITH NC is
not required.

>>In one of my experiments, I tried a "FOR READ ONLY" clause 
>>on the SELECT, knowing that's supposed to speed up the 
>>"open" operation, and it bombed with a message to the 
>>effect that such clauses could not be used on embedded 
>>SELECTs.

The select statement in an insert statement is READ ONLY by nature.
There will never be an UPDATE for this statement.
That's why specifying FOR READ ONLY it's not only not necessary, but not
allowed.

The FOR READ ONLY or FOR FETCH ONLY clauses will only affect cursors.
Depending on updateable or non updateable cursors, blocked reads or fetch
may be performed. For insert statements blocked reads will automatically
performed.
                          
Birgitta

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von James H H Lampert
Gesendet: Freitag, 11. November 2005 21:01
An: Midrange Systems Technical Discussion
Betreff: Re: SQL newbie question

Okay. I've now got as far as pouring a single result set 
from a single programmatically-generated select into a 
file of the desired format, with this statement:

INSERT INTO QTEMP/WTGRPW  (GCID2) SELECT CID FROM
WTCONT CO  WHERE CO.U01010 = '1' WITH NC

and it seems to produce the desired results. It took a lot 
of futzing around to find that I needed the "WITH NC" to 
keep it from looking for a journal (dammit, who needs 
journaling or commitment control on a temporary file?!?!) 
and bombing.

In one of my experiments, I tried a "FOR READ ONLY" clause 
on the SELECT, knowing that's supposed to speed up the 
"open" operation, and it bombed with a message to the 
effect that such clauses could not be used on embedded 
SELECTs.

Which brought to mind the question:
How else would I pour a result set into my file without 
having to do it one-record-at-a-time? Is there another way 
besides what I already have? The message also said 
something about UNIONs not being available (which is kind 
of important here!)

--
JHHL