Re: SQL Question

["Raul A. Jager W." <raul@xxxxxxxxxx>]

Sounds contradictory to (and maibe to the AS also): QTEMP   <-->  Global

Jeff Young wrote:

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