What is an Expression?

You can build simple expressions that limit the query results based on criteria that is applied to a single field, or you can build complex expressions that can be used to perform calculations, reformat stored values, concatenate stored values, and so on. As long as you understand the stored data and the way in which you want to present it to users, you can construct expressions to perform simple to very complex operations on the query data.

You can think of an expression as the combination of any of the following items that together define the conditions by which you want to limit the query results:

• Functions
• Clauses
• Operators
• Any other data required to yield results

In the grid in the Conditions section, you can construct an expression in the Criteria cell, the Or cell, and the Field cell. Expressions can be constructed manually, or using the Expression Builder window, which is accessible from the Field, Criteria, and Or cells of the grid in the Conditions section.

When building expressions, you must use the base (i.e., stored) values for Units of Measure, formatted values, and translated strings. For example, consider a query that contains an expression in a field that stores numeric values in inches. To filter your query results on that field, you must specify values as they are stored in that field (i.e., in inches) rather than as they are displayed when the query is run in formatted mode (e.g., in centimeters).

For instance, to return only the records in which 25 inches has been recorded in this field, your query expression must contain the value 25, not 63.5, which is the stored value converted to centimeters, and which might be displayed to some users. To determine how you should construct values in your expressions, you can run the query in unformatted mode.

The following text is an example of an expression:

This expression combines the TO CHAR function, the Asset Installation Date field, and the text 'yyyy,' which yields a result that is formatted as a four-digit year.

The following text is another example of an expression:

This expression combines the DECODE function, the Asset Status field, and additional data that indicates that a stored value of Active should return A, a stored value of Inactive should return I, and any other stored value (including null values) should return No Status.

Expressions can exist within SELECT statements, WHERE clauses, or HAVING clauses, or they can exist outside of these SQL components. For example, consider the following WHERE clause:

The expression [Asset].[ASSET MANUF CHR] = 'GOULDS' is contained within the WHERE clause.