Re: Dynamically creating Global Temporary Table ?

I did check using With Data and With No Data, and got the same problem, if
you use any Function in Select Query, it does not work, you can check
yourself please .

 Declare Global Temporary Table crTab as
 (Select InvNo,Sum(Amount) as Amt From Trans group by InvNo) with Data

Best Regards,

Luqman

<rob@xxxxxxxxx> wrote in message
news:OFED2EFAE8.06531695-ON05257132.00479BD5-05257132.0047BE25@xxxxxxxxxxxx
> With Definition is a no-no.
>
> It's either
> With Data
> or
> With no data
>
>
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmstdgtt.htm#dgtt
>
> Rob Berendt
> -- 
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> "Luqman" <luqman@xxxxxxxxxxxxxxxx>
> Sent by: midrange-l-bounces@xxxxxxxxxxxx
> 03/14/2006 02:20 PM
> Please respond to
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
>
>
> To
> midrange-l@xxxxxxxxxxxx
> cc
>
> Subject
> Re: Dynamically creating Global Temporary Table ?
>
>
>
>
>
>
> Hi,
>
> I even tried with the word 'as' but it still gives following error.
>
> [SQL0901] SQL system error. Cause . . . . . :   An SQL system error has
> occurred.  The current SQL statement cannot be completed successfully. The
>
> error will not prevent other SQL statements from being processed. Previous
>
> messages may indicate that there is a problem with the SQL statement and
> SQL
> did not correctly diagnose the error. The previous message identifier was
> .
> Internal error type 4239 has occurred. If precompiling, processing will
> not
> continue beyond this statement. Recovery  . . . :   See the previous
> messages to determine if there is a problem with the SQL statement. To
> view
> the messages, use the DSPJOBLOG command if running interactively, or the
> WRKJOB command to view the output of a precompile.  An application program
>
> receiving this return code may attempt further SQL statements.  Correct
> any
> errors and try the request again.
>
> It seems, Global Temporary Table does not support use of Functions in
> Select
> query.
>
> Any idea please ?
>
> Best Regards,
>
> Luqman
>
>
>
> <rob@xxxxxxxxx> wrote in message
> news:OF105CA89C.271C0752-ON05257131.00488492-05257131.00489729@xxxxxxxxxxx
> .
> > Perhaps you meant
> > Declare Global Temporary Table crTab as
> > (Select InvNo,Sum(Amount) as Amt From Trans group by InvNo) with
> > Definition
> >
> > I added the word "as".
> >
> > Rob Berendt
> > -- 
> > Group Dekko Services, LLC
> > Dept 01.073
> > PO Box 2000
> > Dock 108
> > 6928N 400E
> > Kendallville, IN 46755
> > http://www.dekko.com
> >
> >
> >
> >
> >
> > "Luqman" <luqman@xxxxxxxxxxxxxxxx>
> > Sent by:
> > midrange-l-bounces+rob@o­> EÞ?ÙyéLÙwn
> > 03/14/2006 06:13 AM
> > Please respond to
> > Midrange Systems Technical Discussion
> > <midrange-l@xxxxxxxxxxxx>
> >
> >
> > To
> > midrange-l@xxxxxxxxxxxx
> > cc
> >
> > Subject
> > Dynamically creating Global Temporary Table ?
> >
> >
> >
> >
> >
> >
> > When I run following query in Navigator, it runs successfully.
> >
> > Declare Global Temporary Table crTab as
> > (Select InvNo,Amount From Trans) with Definition
> >
> > However the following query using Sum Function does not run.
> >
> > Declare Global Temporary Table crTab as
> > (Select InvNo,Sum(Amount) Amt From Trans group by InvNo) with Definition
> >
> > Any idea please ?
> >
> > Best Regards,
> >
> > Luqman
> >
> >
> >
> > -- 
> > 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.
> >
> >
> > -- 
> > 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.
> >
> >
>
>
>
> -- 
> 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.
>
>
>
> -- 
> 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.
>
>