When using the equals sign (=) in a query, alert or "iif" statement in a formula derived user data extension, why are elements not retrieved as expected?
What is the tolerance used for evaluating Queries and Alerts? For example for a pipe with calculated flow = 0, if the calculated flow is slightly different than zero (for example 0.000005 gpm), will the query or alert find this pipe?
When using the equals sign, the values must be equal. In some cases due to display precision, the values may appear equal but actually are not.
Physical_InvertElevation = Physical_RimElevation
The Elevation (Invert) = 145.55Elevation (Rim) = 145.55.
The assumption is that these should be equal, however, by adding more decimal precision (Units and Formatting) you may see that they are off by a very small amount. Right Click anywhere in the Properties and select Units and Formatting. Under Display precision change the number to ten. You may then notice that the values are slightly different such as Elevation (Invert) = 145.550000000. Elevation (Rim) = 145.550305000.
Additionally, depending on which feature you're using, mechanisms are used to account for slight "fuzziness" in the results (so there doesn't need to be an exact match), but the implementation is different between Queries, Alerts and Formula UDX. See below for more
Alerts currently use a fixed tolerance of 0.001, applied to the respective storage unit (gpm for flow, psi for pressure, ft for level and HGL - these are regardless of the "display" unit you have set) Meaning, if the difference between the Alert value and the model value are less than 0.001, the Alert will still trigger.
For example if a pipe has a flow of 0.000000000000001 gpm, an Alert on Flow = 0 will retrieve the pipe. If the flow is 0.0005 gpm, the Alert on Flow = 0 will still trigger. However if the flow is 0.005 gpm, the Alert will not trigger, since the difference between 0 and 0.005 is greater than the tolerance of 0.001 gpm.
Note: this tolerance with Alerts is always evaluated on the flow in units of gallons per minute (gpm). So, if you’re using units such as L/s, this is internally converted to gpm first before checking the 0.001 tolerance.
Queries incorporate a 0.000001 constant to create the "buffer" zone around the query value. Like with the "tolerance" of 0.001 with Alerts, this allows queries to retrieve elements that are very slightly different from the query value.
For example, say you have the following query: Flow = 0.000510267000001 gpm
The built-in "floating point fuzz" that's added means that the query will actually retrieve any pipe that is between these two values:
0.000510267000001 * (1 - 0.000001) = 0.000510266489733999999 gpm
0.000510267000001 * (1 + 0.000001)= 0.000510267510268000001 gpm
So for example a pipe with flow of 0.000510267156642 gpm will be retrieved by this query.
As another example, if you're using SI units and wanting to retrieve a pipe diameter = 200 mm for example, that 200 is converted internally to Feet as the storage unit in the database, and then converted back to the desired display unit. This can introduce small conversion error so the diameter won't be exactly 200.0 mm. The tolerance mentioned above helps with this so that a query on diameter = 200 should still retrieve the pipes.
When using the iif statement in a formula user data extension (Data Type set to "Real (Formula)") to apply logic, the above mechanism is not used. So for example the below will not work correctly:
iif( [Physical_PipeDiameter; Millimeters] =200 ,0,1)
... because the diameter in millimeters will convert to feet as the storage unit, then back to millimeters as the display unit, introducing a very small conversion error, such that the actual diameter used is not exactly 200. Without the special mechanism used by queries and alerts, a pipe with exactly 200 mm entered as the diameter will not be captured and the result of the above formula user data extension for that pipe will be "1".
See below for a recommendation to address this, or consider using the "Round" function.
Given the above, it is recommended that you use less than or greater than instead of equals for queries. For example if you’re looking to find pipes with zero flow, your query might be something like Flow < 0.01 gpm
How do you create an IF / THEN / ELSE statements using Formula derived user data extensions?