Re: SQL Question

Thanks Elvis,
That suggestion worked.
It is possible that the customer I am working at does not have the latest fix pack.

 
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: Elvis Budimlic <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Thursday, January 15, 2009 4:21:30 PM
Subject: RE: SQL Question

I've strained my eyes looking for an error in your statement, but can't see
one.  It ought to work as is. 
Are you current on database fixpacks?

What happens if you provide the column list:

declare global temporary table assignment
(BBCMP,BBPCPK,BBPCPKD,BBASSIGN,bbcode,start_date,start_time,bbcode,end_date,
end_time) as (....) with data

Hth, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com

-----Original Message-----
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