Re: Running multiple queries in ACS Run SQL Scripts -- MIDRANGE-L

A CTE exists only till the statement ends...

So yeah, after the

select * From temp1;

the CTEs are gone and the other 2 statements would fail.

It's important to note that the CTE is in memory only, whereas the global
temporary table is an actual DB table; thus I'd expect the temporary table
to take longer to build.

Generally speaking, you should only build a temporary table if you need to
run more than one query against it.
In your example, you could simply do:

with TEMP1 as (...) select * from TEMP1;
with TEMP2 as (...) select * from TEMP2;
with TEMP3 as (...) select * from TEMP3;

Lastly, you should be able to specify DECLARE GLOBAL TEMPORARY TABLE .... WITH
REPLACE rather than having to manually DROP the table.

HTH,
Charles

On Wed, Feb 18, 2026 at 9:59 AM Stephen Landess <steve_landess@xxxxxxxxxxx>
wrote:

All -

I'm hoping this is helpful to someone out there.

Besides using STRSQL on the green screen for ad-hoc queries, I often use
ACS Run SQL Scripts.

In Run SQL Scripts (but not with STRSQL), you can do this:

Select * from MYLIB/FILEA;
Select * from MYLIB/FILEB:
Select * from MYLIB/FILEC;

(The above example is greatly simplified - I might also have multiple
joins and where clauses in each query to select specific rows.)

Then you can click on the Run All icon in the menu bar and it will run all
three queries and produce three result sets (in separate windows, if
configured to do so). This is very handy when you are analyzing data for
problem determination purposes.

With STRSQL, you would need to have three separate green screen sessions.

However, the rules for using CTE's are such that if your query look like
this the query fails...

Example:

With TEMP1 as
(
Select
IMITM, IMLITM, IMDSC1, IMSTKT, IMSRP1, IMSRP2, IMSRP3, IMSRP4
from BEHPROD.F4101
where IMLITM = '1169091'
) ,

TEMP2 as
(Select IBITM, IBMCU, IBLITM, IBSTKT, IBSRP1, IBSRP2, IBSRP3, IBSRP4
from BEHPROD.F4102
where IBLITM = '1169091'
) ,

TEMP3 as
(
select LIITM, LIMCU, IMLITM, LIPQOH
from BEHPROD.F41021
join BEHPROD.F4101
on LIITM = IMITM
where IMLITM = '1169091'
)

select * From temp1;
select * from temp2;
select * from temp3;

When running this query, it displays the result set for TEMP1, but it
indicates two errors:
TEMP2 in MYLIB type *FILE not found.
TEMP3 in MYLIB type *FILE not found.

Solution:
In Run SQL Statements, use DECLARE GLOBAL TEMPORARY TABLE instead of using
CTE's :

(I'm using an old version of ACS, I get errors when I specify DROP TABLE
IF EXISTS, so to avoid errors on the DROP statements, I had to uncheck Stop
On Error under Options in the menu bar)

Example:

--- ( The field list after DECLARE GLOBAL TEMPORARY TABLE is optional )

DROP TABLE QTEMP.TEMP1;
DROP TABLE QTEMP.TEMP2;
DROP TABLE QTEMP.TEMP3;

DECLARE GLOBAL TEMPORARY TABLE TEMP1
( IMITM, IMLITM, IMDSC1, IMSTKT, IMSRP1, IMSRP2, IMSRP3, IMSRP4 )
as
(
Select
IMITM, IMLITM, IMDSC1, IMSTKT, IMSRP1, IMSRP2, IMSRP3, IMSRP4
from BEHPROD.F4101
where IMLITM = '1169091'
) WITH DATA ;

DECLARE GLOBAL TEMPORARY TABLE TEMP2
( IBITM, IBMCU, IBLITM, IBSTKT, IBSRP1, IBSRP2, IBSRP3, IBSRP4 )
as
(Select
IBITM, IBMCU, IBLITM, IBSTKT, IBSRP1, IBSRP2, IBSRP3, IBSRP4
from BEHPROD.F4102
where IBLITM = '1169091'
) WITH DATA ;

DECLARE GLOBAL TEMPORARY TABLE TEMP3
( LIITM, LIMCU, IMLITM, LIPQOH )
as
(
select
LIITM, LIMCU, IMLITM, LIPQOH
from BEHPROD.F41021
join BEHPROD.F4101
on LIITM = IMITM
where IMLITM = '1169091'
) WITH DATA ;

select * From qtemp.temp1;
select * from qtemp.temp2;
select * from qtemp.temp3;

In the final select statements, be sure to qualify the tables names to
QTEMP, since DECLARE GLOBAL TEMPORARY TABLE creates the file in the job's
QTEMP library.

( Use the Run all option to run the above query )

Best Regards,
Steve Landess
(512) 289-0387
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.