Re: SQL Question

Eric,
Same error with Create Table.

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
  
 






________________________________
From: "DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Thursday, January 15, 2009 3:42:07 PM
Subject: RE: SQL Question

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.