Re: SQL row processing order for UDF

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