Re: AW: SQL row processing order for UDF

[CRPence <CRPbottle@xxxxxxxxx>]

On 15-Jun-2010 11:02, Birgitta Hauser wrote:
> Then you may try if an order by in a Sub-Select is allowed (DB2
> UDB for isupports it since 6.1)
>
> Select TRNADAT, Amount, MDT(TRNADAT)
> From (Select TRNADAT, Amount
>          From MyTable
>          Order BY TNRADAT) x

  In my experience the ORDER BY clause in a NTE was supported since 
at least v5r3.  However also in my experience, for an NTE versus a 
CTE, the effect was often composite versus materialized; i.e. the 
query rewrite would typically eliminate the redundant NTE in the 
quoted example, and the ordering of the result set for the outer 
SELECT remains indeterminate unless explicitly specified.

"View" implementation: Views, derived tables (nested table 
expressions or NTEs), and common table expressions (CTEs) are 
implemented by the query optimizer using one of two methods....
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/rzajq/viewopt.htm
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/rzajq/viewcomposite.htm
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/rzajq/viewmaterialize.htm

  And FWiW: DB2 UDB?  That moniker has long been eliminated from 
all DB2 naming, since well over two years now.?  Surely that naming 
has never been associated with the DB2 for i by\since the release of 
IBM i 6.1?

Regards, Chuck