Reference Information: Query Expressions, Clauses, Prompts, and Operators

The Expression Builder window contains various fields and controls to assist you in constructing an expression for your query criteria.

The Expression Builder window is divided into two sections, Simple and Advanced. You can toggle between the two sections by using the Simple and Advanced tabs at the top of the window.

If you access the Expression Builder window from the Field cell, the Advanced section is selected by default, and the Simple button is disabled. If you access the Expression Builder window from the Criteria cell or the Or cell, the Simple section is selected by default, and the Advanced button is enabled.

Simple Section

The Simple section of the Expression Builder window lets you define a simple expression using conditions on the field from which you accessed the Expression Builder window.

The Simple section of the Expression Builder window contains the following features:

• And/Or: A list that appears for all rows except the first. You can use the And and Or options to establish relationships between expressions.

• Field : A read-only text box that displays the value in the Field cell for the field you selected.

• Operator: A list that displays the valid operators to use in the expression, depending on the type of field on which the expression is built.

  Field type	Options
  Text -or- Character	equals
  	not equals
  	contains
  	does not contain
  	starts with
  	ends with
  	is null
  	is not null
  Numeric	equals
  	is at most
  	is at least
  	not equals
  	is less than
  	is greater than
  	is null
  	is not null
  Date	on
  	at
  	at or before
  	at or after
  	not at
  	before
  	after
  	is null
  	is not null
  Logical	is true
  	is false
  	is null
  	is not null

• Value: A text box where you can enter the value you want to include in the expression.
• Add Condition : A button that displays another row of options which you can use to create additional expressions on the field.

Advanced Section

The Advanced section of the Expression Builder window is divided into five main sections, as shown in the following image. These labels correspond to the numbered list following the image.

1. A text box that displays the expression itself. To build the expression manually, you can enter text directly into the text box. Otherwise, the expression will be built dynamically when you select tabs and values in the Expression Builder window.
2. Buttons that insert symbols to establish a relationship between parts of an expression, or that insert symbols to group those parts. Selecting a button will insert the corresponding symbol into the expression.
   Note:

   • If you are using date to build an expression, note that date can be entered in any of the acceptable formats. However, always be sure to select the date in the expression, and then select the Date button. Selecting the Date button converts the date in the expression to yyy-mm-dd format.
   • The button opens the Prompt Settings section of the Expression Builder window, which has options that let you add a prompt to a query.
3. A list of tabs that you can use to toggle between categories for the valid components of the expression, which can include the following:
   • System Codes: This option appears only if a System Code Table Valid Values list has been defined for the selected field in the Rules Editor (i.e., if no Valid Values list has been defined, if the Valid Values list uses a static list of values, or if the Valid Values list is By Rule, this option does not appear). If this option appears and you select it, list 5 displays the following:
     • All the codes and descriptions for the appropriate System Code Table if the Valid Values rule is defined as System Code Table Only.
     • Only the codes and descriptions that meet the literal value criteria if the Valid Values rule is defined as System Code Table with Literal Reference. Additionally, if a literal reference has been defined, the reference value will appear in parentheses beside the table ID in list 4.

     • All the codes and descriptions for the appropriate System Code Table if the Valid Values rule is defined as System Code with Field Reference (i.e., in the absence of a field value to use as the reference, there is no way to limit the list).

       Note: When you select a System Code to include in your expression, the System Code ID (rather than the description) will be used. Even though System Code descriptions appear in the datasheet, the IDs are actually stored in the database. By using the ID instead of the description in the expression, your criteria will apply to all System Codes, including those with different, translated descriptions.
     • Tables: The database tables from which you can select fields to include in the expression. When you select the Tables option in list 3, list 4 displays the query source families, and list 5 displays all the fields in those families.
     • Functions: The three types of functions that you can include in the expression. When you select a function category in list 3, list 4 displays the types of functions in that category, and list 5 displays all the available functions for that category.
     • Constants: The constants that you can include in the expression. A constant is a static value provided for comparative reasons. When you select the Constants option in list 3, and list 5 displays the available constants.
     • Operators: The symbols that you use to join parts of the expression. When you select the Operators option in list 3, list 4 displays different classifications of operators, and list 5 displays all the valid operators.
   • A list that displays a subset of options based on your selection in list 3. Selecting an item in this list limits the options in list 5.
   • A list that displays a subset of values based on your selections in lists 3 and 4. Selecting any item in this list will insert the value into the expression. The value will be inserted wherever the cursor is currently positioned.

An expression is a string of characters that, together, define a certain set of conditions to be applied to a query. In other words, an expression is the code that APM reads in order to determine what you want to retrieve from the database and how you want to display the query results.

Details

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 FieldField, 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.

This topic contains syntax suggestions and requirements for expressions on character fields, as well as details about how APM reformats expressions on character fields.

When you create an expression on a character field, the actual field value(s) must be within single quotation marks.

About Automatic Reformatting on Character Fields

If you enter something in the Criteria cell or the Or cell of the grid in the Conditions section of the Design workspace for a character field, and you do not enter single quotation marks yourself, APM will insert them automatically for you. For example, if you want to search for records where the Asset Status is Active, you can enter either 'Active' or Active, APM will insert the single quotation marks automatically, reformatting the entered text as 'Active'.

If the expression contains multiple words, or if you want to use operators other than is equal to, and you do not enter the single quotation marks yourself, APM will insert the single quotation marks around the entire phrase, including the operators. Depending on the values stored in the database, this may or may not return the appropriate results.

For example, suppose you create a query on the Shell and Tube Heat Exchanger family, and you add the Asset Manufacturer field to the grid in the Conditions section. If there are five different manufacturers of Shell and Tube Heat Exchangers, but you want to return Shell and Tube Heat Exchangers manufactured by only two of those manufacturers, Alco and Whitlock, the query expression on the Manufacturer field must be formatted as follows:

='Alco' or 'Whitlock'

If you enter Alco or Whitlock in the Criteria cell for the Asset Manufacturer field, APM will reformat the expression as 'Alco or Whitlock'. The query will not return any results because the syntax suggests that you want to find Shell and Tube Heat Exchangers whose manufacturer is Alco or Whitlock, which does not exist in the database.

Likewise, if you enter =Alco or Whitlock, APM will reformat the expression as ='Alco' or 'Whitlock', which, again, would not return any results because there is no manufacturer named =Alco or Whitlock in the database.

To return the appropriate results, you should enter 'Alco' or 'Whitlock' in the Criteria cell, or enter Alco in the Criteria cell and Whitlock in the Or cell.

This topic contains syntax suggestions and requirements for expressions on text fields, as well as details about how APM reformats expressions on text fields.

When you create an expression on a text field, the value(s) within the expression must be placed within single quotation marks.

About Automatic Reformatting on Text Fields

If you enter something for a text field in the Criteria cell or the Or cell of the grid in the Conditions section, and you do not enter single quotation marks yourself, APM will insert them automatically for you. For example, if you want to search for records where the Asset Additional Information field contains the text This asset exists for testing purposes, you can enter the phrase with or without the single quotation marks. If you omit the single quotation marks, APM will insert them for you.

This topic contains syntax suggestions and requirements for expressions on date fields, as well as details about how APM reformats expressions on date fields.

When you create an expression on a date field, you should use the following syntax:

            
                (# :D 'yyyy-mm-dd')
            
        

This topic contains syntax suggestions and requirements for expressions on logical fields, as well as details about how APM reformats expressions on logical fields.

A logical field represents a value of True or False. When you create an expression for a logical field, the exact syntax to use is either 'Y' (True) or 'N' (False).

About Automatic Reformatting on Logical Fields

Some common syntax options that APM will reformat to match the syntax required for logical fields are:

• True (using any case combination, such as true or TRUE)
• False (using any case combination, such as false or FALSE)
• Yes (using any case combination, such as yes or YES)
• No (using any case combination, such as no or NO)

This topic contains syntax suggestions and requirements for expressions on numeric fields.

Details

When you create an expression on a numeric field, you must use the exact numeric value that are stored in the database. Some numeric values may be displayed differently than they are stored. For example, numbers may be formatted to display a certain number of decimal places, to include a currency symbol, or to be converted to a different unit of measure (UOM).

To determine how values are stored in the database, run the query in unformatted mode. Then, format numeric values in your query expressions exactly as they are displayed in the unformatted query results.

Unlike character and text field expression values, which require single quotation marks, the actual numeric values that you want to return must not have single quotation marks in your expression. Specifying the value as it is stored is sufficient to return the expected results. For example, an acceptable way to express that returned records should contain a value of 5 in the Number of Storage Tanks field in the Criticality Analysis family is:

WHERE [Criticality Analysis].[No of Storage Tanks] = 5

You can build expressions in the Field, Criteria, and Or cells in the grid within the Conditions section in the Design workspace. This topic includes formatting tips for creating expressions in each of those cells.

Expressions in the Field Cell

When you access the Expression Builder window from the Field cell, the Advanced tab is selected by default, and the Simple tab is disabled. You can construct an expression on the Field cell to reformat the results that are returned by the query.

For example, suppose that you want to display an installation date in two formats: the stored format and a modified format. In this case, you would add the Installation Date field to the query twice. The first would return the stored value, and the second could contain a Date function in the Field cell to indicate how you want the date to appear in the results.

If you add an expression to the Field cell, you must enter the exact syntax that is required in order to run the query. APM will not reformat any text that you enter in the Expression Builder window for a Field cell.

Expressions in the Criteria Cell

When you access the Expression Builder window from the Criteria cell, the Simple tab is selected by default, but the Advanced tab is enabled. You can construct an expression in the Criteria cell to limit the results that are returned by the query.

For example, suppose that you want to return only those pieces of equipment manufactured by GOULDS. In this case, you would add the Manufacturer field to the query. You can then construct an expression in the Criteria cell using either the Simple or Advanced section.

If you access the SQL workspace, you will see the following code:

SELECT [MI_EQUIP000].[MI_EQUIP000_MFR_C] "Manufacturer"
FROM [MI EQUIP000]
WHERE [MI EQUIP000].[MI EQUIP000 MFR C] = 'GOULDS'

The expression [MI EQUIP000].[MI EQUIP000 MFR C] = 'GOULDS' is inserted automatically into the WHERE clause at the end of the SQL code.

Constructing an expression in the Simple section

In the Simple section of the Expression Builder window, you could use the options to construct an expression that looks something like the following image:

Constructing an expression in the Advanced section

In the Advanced section of the Expression Builder window, you could construct an expression that looks something like the following code:

'GOULDS'

While this text alone does not constitute an entire expression, APM interprets the data in the remaining cells to construct a valid expression. The equal (=) operator is understood, and the Field and Table cells indicate the locations from which you want to retrieve data.

Expressions in the Or Cell

When you access the Expression Builder window from the Or cell, the Simple tab is selected by default, but the Advanced tab is enabled. You can construct an expression in the Or cell to limit the results that are returned by the query.

For example, suppose you want to return only those pieces of equipment manufactured by JENSEN or WESTERN SUPPLY. In this case, you would add the Manufacturer field to the query. You can then construct an expression in the Or cell using either the Simple or Advanced section.

If you access the SQL workspace, you will see the following code:

SELECT [MI_EQUIP000].[MI_EQUIP000_MFR_C] "Manufacturer"
 FROM [MI EQUIP000]
 WHERE ([MI EQUIP000].[MI EQUIP000 MFR C] = 'JENSEN'
 OR [MI EQUIP000].[MI EQUIP000 MFR C] = 'WESTERN SUPPLY')

The expression [MI EQUIP000].[MI EQUIP000 MFR C] = 'JENSEN' OR [MI EQUIP000].[MI EQUIP000 MFR C] = 'WESTERN SUPPLY' is inserted automatically into the WHERE clause at the end of the SQL code.

Constructing an expression in the Simple section

In the Simple section of the Expression Builder window, you could use the options to construct an expression that looks something like that shown in the following image:

Constructing an expression in the Advanced section

In the Advanced section of the Expression Builder window for the Criteria cell, you could construct an expression that resembles the following code:

'JENSEN'

Then, you could construct an expression in the Advanced section of the Expression Builder window for the Or cell that resembles the following code:

'WESTERN SUPPLY'

While this text alone does not constitute an entire expression, APM interprets the data in the remaining cells to construct a valid expression. The equal (=) operator is understood, and the Field and Table cells indicate the locations from which you want to retrieve data.

Details

WHERE clauses are used to define conditions that you want to apply to the query to limit the results.

Details

In an aggregate query, HAVING clauses are used to define conditions that you want to apply to each aggregate value after the calculation dictated by the aggregate function has been performed.

You can construct a query that will, when run, prompt the user to enter or select values by which the results will be filtered.

Details

You can create a prompt on a character, numeric, date, or logical field in any type of query (Select, Crosstab, Append, Update, or Delete). Like all query options, you can construct prompts by modifying the SQL code directly, but APM provides the Prompt Settings section of the Expression Builder window, which guides you step-by-step through the process of creating prompts. This documentation focuses primarily on that feature.

When a user runs a query by opening it from the catalog, prompts appear in a window that disappears when the query results are returned.

Using the Prompt Settings section of the Expression Builder window, you can define the basic settings for the prompt that you want to create.

Prompt Settings

The following table includes details on the available settings on the prompt builder. After you have defined the prompt settings you want, you can select Next to further define the prompt. The content that appears on subsequent screens in the Prompt Builder depends on your selection in the Valid Values section. These screens are documented in more detail in the topics that explain how to create specific types of prompts.

All date fields contain both a date and time value. When you apply specific criteria to a field, you can use the DAYOF operator and omit the time to return all the records that contain the specified date and any time.

Details

For example, specifying the criteria =DayOf(#:D '2000-03-10') would return records from any time in the same day, for example, 12:00:00 A.M., 2:00:00 P.M., 5:00:17 P.M., or any other time. You can use the DAYOF operator with prompts as well: DayOf(? :d :id=mydate).

Otherwise, you will need to know how to enter a date that will retrieve the desired results:

• If you enter a date with no time, the query will return records where the time is 12:00 A.M. Records that contain any other time will not be included in the results.

• If you enter a date and a specific time, the query will return records that contain that specific date and time.

  Note: You must specify the seconds value as 00. Entering any other value will cause the query to not return any results.
• If you use the Calendar to select a date, you will need to modify the time manually in order to include seconds, which are required to return results. Either enter a different time, or delete the existing time (to use a time of 12:00:00 A.M.).

All dates and times in APM are stored in UTC format, and, the time values will be displayed using the appropriate conversion from UTC to the time zone that is associated with the Security User who is logged in when the query is run. This allows any user who runs the query to provide a local date and time in the prompt.

Units of measure (UOMs) can be associated with numeric fields. UOM Conversion Sets can be defined on fields, and will convert stored numeric values to different values and UOMs.

Details

If a query has a prompt on a numeric field, when the query is accessed via the Catalog or a URL, the associated UOM will appear on the Enter parameter values window. For example, you would define a prompt on the Measurement Value field of the Thickness Measurement family to display returned values in inches.

If you run a query in formatted mode, the Enter parameter values window will display the appropriate UOM, based on the UOM Conversion Set that is associated with your Security User account. In order for the query to return results, you will need to enter numeric values associated with the UOM that is displayed.

If you run a query in unformatted mode, the Enter parameter values window will display the stored values, and the base UOM will always be displayed.

Query prompts can help users limit the results that will be returned by the query. In some cases, however, users might not know what to select or type in a query prompt. This might be especially true if the prompt does not present a list of values and the user must enter the value as it is stored in the database in order to return any results.

To address this concern, you can configure a query prompt to accept the percent (%) wildcard. When a user enters only a percent symbol in the prompt text box, the query will return records where the field on which the prompt was built contains any value. When a user enters a combination of text and the percent symbol, the query will return records where the field on which the prompt was built contains a value that contains the specified text, preceded or followed by any combination of characters (depending on where the percent symbol is placed in the prompt text box).

To configure a prompt to accept a percent wildcard, you must use the Like operator instead of the equal (=) operator in the expression that defines the prompt.

For example, suppose you build a prompt on the Asset Manufacturer field with no list of valid values. The expression for the prompt would look like this:

                    [Asset].[ASSET MANUF CHR] = (? :s :caption='Manufacturer' :id=Manufacturer)
                

If you want users to be able to enter a percent symbol in the prompt to return records where the Asset Manufacturer field contains any value, you can replace the equal operator (=) with the Like operator. The expression for the prompt would look like this:

                    [Asset].[ASSET MANUF CHR] Like (? :s :caption='Manufacturer' :id=Manufacturer)
                

When you run the query, you could enter the percent symbol (%) in the Manufacturer box on the prompt window. In this case, the results would display records where the Asset Manufacturer field contains any value.

Additionally, you could enter SEI%, and the query would display records where the value in the Asset Manufacturer field begins with the letters SEI, followed by any combination of characters, such as SEIMENS-AL and SEIGER.

In some cases, records might contain fields that do not contain values. If you want to return a query with a prompt on these fields to see all records where the field is empty, you must construct the query in a certain way.

This query construction is best understood through an example. The following example assumes that you want to return records where the Asset Description field of Air Cooled Heat Exchanger records is empty. It also assumes that you want to see the Asset ID, Asset Description, and Asset Installation Date of the returned records.

To configure a prompt to return records where the Asset Description field is empty, you would need to add the following fields to the query:

• Asset ID
• Asset Description
• Asset Installation Date

You would need to add two expressions to the Asset Description field. In the Criteria cell, you would need to add the prompt (? :s :caption='Description' :id=Description), and in the Or cell, you would need to add the expression IS NULL.

The design grid would look like the following image:

When the query is run, the Description box appears in the prompt window. If a user selects Done without entering a value in the prompt, the results will display records where the Asset Description field is empty, like the ones in the following image:

If you enter a value in the Description box, the query will return the matching records and the records where the Asset Description box is blank.

This topic describes the operators that can be used to manipulate values in character fields.

Details

When you enter anything in the Criteria cell or the Or cell, the is equal to (=) operator is assumed at the beginning of the text unless a different operator is specified.

It is not necessary to enter the = operator at the beginning of the expression in a given cell. If you do not enter the operator, will insert it automatically, but it will not be displayed.

Available Operators for Character Fields

The following table provides examples of operators that you can use to manipulate values returned in character fields.

This topic describes the operators that can be used to manipulate values in text in an Oracle database. For SQL server and PostgreSQL, you can treat them the same as character fields.

Available Operators for Text Fields

The following table provides examples of operators that you can use to manipulate values returned in character fields.

When you enter anything in the Criteria cell or the Or cell, the is equal to (=) operator is assumed at the beginning of the text unless a different operator is specified.

It is not necessary to enter the = operator at the beginning of the expression in a given cell. If you do not enter the operator, will insert it automatically, but it will not be displayed.

Available Operators for Date Fields

The following table provides examples of operators that you can use to manipulate values returned in date fields.

This topic describes the operators that can be used to manipulate values in logical fields.

Details

When you enter anything in the Criteria cell or the Or cell, the is equal to (=) operator is assumed at the beginning of the text unless a different operator is specified.

It is not necessary to enter the = operator at the beginning of the expression in a given cell. If you do not enter the operator, will insert it automatically, but it will not be displayed.

Available Operators for Logical Fields

The following table provides examples of operators that you can use to manipulate values returned in logical fields.

This topic describes the operators that can be used to manipulate values in numeric fields.

Details

When you enter anything in the Criteria cell or the Or cell, the is equal to (=) operator is assumed at the beginning of the text unless a different operator is specified.

It is not necessary to enter the = operator at the beginning of the expression in a given cell. If you do not enter the operator, will insert it automatically, but it will not be displayed.

Available Operators for Numeric Fields

The following table provides examples of operators that you can use to manipulate values returned in numeric fields.