Query Calculation: Left-to-Right? (AXP)

Topic

New Reply

Before I put together a test case…

A query I am about to develop produces calculated values. Some of the calculated fields rely on results “in other columns”. e.g.,
Field3 = Field1+Field2, Field4=2*Field3.

What if I change the field order, and write it like this:
Field3=2*Field4, Field4 = Field1+Field2 ?

Will either of those work?

Or do I need to write two queries, one to calc (the original) Field3, and a second query to calc Field4?

Reply | Quote

Replies

In general, as long as the field that depends on another calculated value is in a column to the right of the one it depends on, it will work. It seems to me there are some exceptions, but I don’t recall what they were – I think it may involve using functions in the first calculated value. Maybe someone else can dredge up what the issue was.

Reply | Quote

Why not use Field4=2*(Field1+Field2) and avoid any possibility of a problem? I don’t know about 2000 or XP, but in some older versions you could not use a calculated field as a parameter in the same query that calculated it.

Reply | Quote

If my situation was as simple as I stated it, I would do what you suggest. However, many of the values that are calculated are returned from functions. The fields and functions are scavenged from a spreadsheet, where the calculation order is “automatic”: cells are recalculated depending on whatever has changed.

Perhaps another question is pertinent here: my approach to a “complicated expression” is to encapsulate that calculation in a function, verify it outside of the query, and then expose the function interface to the world. Is this the recommended database practice?

Reply | Quote

——-
Perhaps another question is pertinent here: my approach to a “complicated expression” is to encapsulate that calculation in a function, verify it outside of the query, and then expose the function interface to the world. Is this the recommended database practice
——

For complicated expressions, that’s probably a good approach, though it may take a bit more resources during execution. For simple expressions using existing functions and simple IIF statements, we usually embed them in the query or the control.

Reply | Quote