About Formatted Expressions on Character Fields

If you want to return records where the Asset Status field contains the value Active, the syntax would be:

...where Active is within single quotation marks because it is the stored value that you want to use for limiting the query results.

If you want to search on multiple values, such as Centrifugal Pump and Rotating Pump, you would again place the actual values within single quotation marks. The syntax would be:

...where Centrifugal Pump and Rotating Pump are within single quotation marks because they are the actual values to be used in the query criteria.

Note: When you are using an Oracle schema, the value is case sensitive by default. This means, for example, that if you enter WHERE [Asset].[ASSET STAT CHR] = 'ACTIVE' for a field where values are stored in the database as Active, the query will not return any results.

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, Meridium Enterprise 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. If you enter Active, Meridium Enterprise 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, Meridium Enterprise 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:

If you enter Alco or Whitlock in the Criteria cell for the Asset Manufacturer field, Meridium Enterprise 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, Meridium Enterprise 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.

Note: If you enter an entirely numeric value (e.g., 123) or an expression that can be interpreted as a mathematical equation (e.g., 123-12), Meridium Enterprise APM will not insert single quotation marks automatically. This will cause the expression to be invalid, and when you run the query, the system will display an error. To resolve this problem, add the single quotation marks manually.