Order of evaluation of functions in records & somewhat recursivefunction

[brwn.fred]

Hi,
Is the order of evaluation of records defined in a Jet SQL query. For
instance,
lets say I have a table T with field F. If I have a query:

SELECT F, vbaFun([F]) AS exp
FROM T
ORDER BY F;

will vbaFun() be executed on the Fs according to the sorting order?

If so, then could I add some type of static VBA variable to calculate
a function that depends on the value f on the previous piece of data
and the current data - a partially recursive function?

Thanks,
Fred Brown

 

[John Spencer]

I am not sure if the evaluation is in this order, but I think it MAY BE.

Select the Tables
Execute the WHERE Clause
Execute the ORDER BY Clause
Return specific fields requested in Order

However, if you are using the results of the vbaFun to order by then the
vbaFun obviously has to run before the ORDER by clause can execute.
So the whole order is not fixed, but is dependent on other factors.

HOWEVER, you might be able to use something like the following if the
values of F are unique.

SELECT F, vbaFun(DMax("F","T","F<" & T.F))
FROM T
ORDER BY F

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Michel Walsh]

No, but almost yes, well at least, with Jet 4.0, The evaluation of your VBA
function is 'delayed', and understand by that that the VBA function will be
only called at the last moment. See it as if you wrote a program and when
the end user want to see a 'row', then, and only then, you compute the
expression(s) in the SELECT clause that where not used elsewhere (like in
the WHERE clause, or in the FROM clause, etc).

Try the following function:

=============================================
Public Function ShowStatic(Optional arg As Variant) As Long

Static i As Long
If IsMissing(arg) Then
i = 0
ShowStatic=i
exit function
end if
i = i + 1
ShowStatic = i

End Function
==============================================

then, the query

SELECT Categories.CategoryName, Categories.Description, ShowStatic() AS
Expr1, ShowStatic([Description]) AS Expr2
FROM Categories
ORDER BY Categories.Description;


returns what you 'want', so that seems to indicate that yes, the vba
function is called as the order by... but that is only because records have
been 'created' sequentially. Always in Northwind, now, try the same on a
larger table:


SELECT OrderID,CustomerID, ShowStatic() AS Expr1, ShowStatic(CustomerID) AS
Expr2
FROM Orders
ORDER BY CustomerID;


and, in data view, move to the last record, then, to move back to the first
record, you will see that your vba function will be RECOMPUTED the second
time you see the first records, which indicate that, indeed, the expression
computation is 'delayed' and only computed when (absolutely) required.



Vanderghast, Access MVP

 

[brwn.fred]

Hi John,
Thanks for your response. I am not, though, asking about the
execution order of the SQL statement itself.
I am speaking about the evaluation order of the vba function vbaFun()
in my example above. Namely: since the output is ordered by F, if I
have two records containing F values f1 and f2 where f1 < f2, will
vbaFun(f1) be executed before vbaFun(f2) when I run the query.
Obviously, I could try it but I would like to know if this is
guaranteed to be true under all conditions and implementations.

Thanks again,
Fred Brown

I am not sure if the evaluation is in this order, but I think it MAY BE.

Select the Tables
Execute the WHERE Clause
Execute the ORDER BY Clause
Return specific fields requested in Order

However, if you are using the results of the vbaFun to order by then the
  vbaFun obviously has to run before the ORDER by clause can execute.
So the whole order is not fixed, but is dependent on other factors.

HOWEVER, you might be able to use something like the following if the
values of F are unique.

SELECT F, vbaFun(DMax("F","T","F<" & T.F))
FROM T
ORDER BY F

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2008
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================

Hi,
Is the order of evaluation of records defined in a Jet SQL query.  For
instance,
lets say I have a table T with field F.  If I have a query:

SELECT F, vbaFun([F]) AS exp
FROM T
ORDER BY F;

will vbaFun() be executed on the Fs according to the sorting order?

If so, then could I add some type of static VBA variable to calculate
a function that depends on the value f on the previous piece of data
and the current data - a partially recursive function?

Thanks,
Fred Brown- Hide quoted text -

- Show quoted text -

 

[John Spencer]

Not necessarily.

The function executes when the value returned by the function is needed. So
if the row with f1 is needed before the row with f2 then the function will
execute in the order f1, f2. If there you don't do something to cause that
order to occur, it will be random on which value is first used.

So your best bet is not to rely on the function doing the calculation in any
specific order when you use it in a query.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County