Re: 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