Can you use the
CREATE TABLE qtemp/xxxyyy as
( with n1 as
select ...
...
...
) WITH DATA
Format?
Eric DeLong
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Young
Sent: Thursday, January 15, 2009 2:02 PM
To: midrange-l@xxxxxxxxxxxx
Subject: SQL Question
I am attempting to create a temporary table in QTEMP using an SQL statement that starts with a CTE and then selects data based on the CTE.
The base SQL statement works fine, but when I wrap a Declare global tempory table expression around it, I get a Column List Required error.
My SQL statement is :
declare global temporary table assignment as ( with trans as ( SELECT BBCMP, BBPCPK, BBPCPKD, BBASSIGN, bbcode,min(bbcctd) bbcctd
,min(bbcctt) bbcctt from voctrack where (bbcctd = 20090114 and bbcctt >= 060000 or bbcctd = 20090115 and bbcctt <= 060000) and bbcode = 'STR' GROUP BY BBCMP, BBPCPK, BBPCPKD, BBASSIGN, BBCODE) SELECT a.BBCMP, a.BBPCPK, a.BBPCPKD, a.BBASSIGN, a.bbcode,a.bbcctd start_date, a.bbcctt start_time, b.bbcode, b.bbcctd end_date,b.bbcctt end_time FROM trans a join voctrack b on a.bbcmp = b.bbcmp and a.bbpcpk = b.bbpcpk and a.bbpcpkd = b.bbpcpkd and a.bbassign = b.bbassign WHERE a.bbcode = 'STR' and b.bbcode = 'END'
and (a.bbcctd = 20090114 and a.bbcctt >= 060000 or a.bbcctd =
20090115 and a.bbcctt <= 060000) ORDER BY BBASSIGN, BBPCPK, a.BBCCTT ) with data
I am on V5R3.
Any suggestions will be appreciated.
Thanks,
Jeff Young
Sr. Programmer Analyst
IBM -e(logo) server Certified Systems Exper - iSeries Technical Solutions V5R2 IBM Certified Specialist- e(logo) server i5Series Technical Solutions Designer V5R3 IBM Certified Specialist- e(logo)server i5Series Technical Solutions Implementer V5R3
--
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.
midrange.com
