Re: SQL row processing order for UDF

[CRPence <CRPbottle@xxxxxxxxx>]

On 15-Jun-2010 08:29, Dennis Lovelady wrote:
> I created a UDF like a more complex version of this:
>
>   CREATE FUNCTION MTD(date, decimal(11,2)
>   RETURNS DECIMAL(13,2)
>   RETURNS NULL ON NULL INPUT
>
> The select would like like:
>
>   SELECT TRNDATE, AMOUNT, MTD(AMOUNT)
>   ORDER BY TRNDATE
>
> And ideally the result would be a running total of month-to-date
> values. Pretty slick.
>
> What I found, though, leads me to believe that the MTD function
> is being called before the ORDER BY, and that's giving me wildly
> incorrect results, even when the source table is already in that
> sequence.  When the source table is already in the correct
> sequence and I drop the ORDER BY, I *seem to* get the results
> that I want, but I doubt there's any guarantee of that; besides,
> that's a requirement that'll be hard to meet.  So my two-part
> question is:  Is this expected behavior?   Is there a way around
> this?

  The order of processing for the rows can not be predicted in the 
given SELECT.  The scalar UDF can be invoked against any row without 
any particular collation.  Hmmm, do the newer OLAP features give the 
ability to produce running totals?

  I am not positive, but IIRC there is documentation that states an 
ordered CTE always will be processed in arrival sequence; i.e. I do 
not have a link to any such documentation.  What I recall may even 
have been for an explanation of a /running totals/ example that 
utilized a CREATE FUNCTION EXTERNAL NAME UDF created\defined with a 
/SCRATCH PAD/.  If as I recall, then the following example SELECT 
should effect the desired result.

   With
     TrnDateOrder as
      ( SELECT TRNDATE, AMOUNT
        FROM theTable
        ORDER BY TRNDATE
      )
   SELECT TRNDATE, AMOUNT, MTD(AMOUNT)
   FROM TrnDateOrder
   ORDER BY TRNDATE

  Probably not necessary [additional level of temporary beyond 
above exampe], but I am even more confident that the following would 
always effect the desired:

   With
     TrnDateOrder as
      ( SELECT TRNDATE, AMOUNT
        FROM theTable
        ORDER BY TRNDATE
      )
   , TrnDateOrderAndUDF
     ( TRNDATE, AMOUNT, MTD_AMOUNT )
        SELECT TRNDATE, AMOUNT, MTD(AMOUNT)
        FROM TrnDateOrder
        ORDER BY TRNDATE
     )
   SELECT TRNDATE, AMOUNT, MTD_AMOUNT
   FROM TrnDateOrderAndUDF
   WHERE ...
   ORDER BY ...

  Again, I have no doc link to be sure that either of the above [so 
confidently as I allude] will effect the desired.

Regards, Chuck