Hierarchy of Calculations

  • Thread starter Thread starter jerrycreedon
  • Start date Start date
J

jerrycreedon

I have been trying to manipulate data from a fairly complex database. I am
creating a report that is counting a specified field once it has gone through
a query and a couple of filters. The SQL statement became too long for the
complete calculation. I have now decided the break up the operation and then
calculate the final result from these smaller operations. I am however
nervous as to the hierarchy between the first set of operations and the final
operation which brings them all together. How can i be sure that the initial
operations will be completed before the subsequent operation calculates the
final answer?
 
I do not follow what seems to bother you. If a query does calculations and
is used in a second query it will have completed before the second query does
any calculations.
Maybe you can explain your preceieved problem in another way so I may
understand.
 
In Jet, they are from left to right; in MS SQL Server, you cannot use alias
on alias:



SELECT f1+f2 AS f3, f3+f4 AS f5 FROM....


f3 is computed before being used to compute f5. (Sure, here, it is trivial
arithmetic, but there are cases where it may be important).



UPDATE tableName SET f1=f2, f2=f1

in Jet, will end with columns f1 and f2 having the same value (the values
originally in f2); in MS SQL Server, the columns would have exchange their
values.



SELECT whatever FROM (SELECT .... ) WHERE ...


the inner SELECT (the sub-query) will be executed before the anything else
occur, since the order of execution of an SQL statement is LOGICALLY
equivalent to have the FROM clause done before the WHERE clause, itself
before the SELECT list clause is evaluated.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top