SQL filters

SQL filters are text strings that you use to specify a subset of the data items in an internal or SQL database data type.

For SQL database and internal data types, the filter is an SQL WHERE clause that provides a set of comparisons that must be true in order for a data item to be returned. These comparisons are typically between field names and their corresponding values.

Syntax

For SQL database data types, the syntax of the SQL filter is specified by the underlying data source. The SQL filter is the contents of an SQL WHERE clause specified in the format provided by the underlying database. When the data items are retrieved from the data source, this filter is passed directly to the underlying database for processing.

For internal data types, the SQL filter is processed internally by the policy engine. For internal data types, the syntax is as follows:

Field
				Operator
				Value [AND | OR | NOT (Field
				Operator
				Value) ...]

where Field is the name of a data type field, Operator is a comparative operator, and Value is the field value.

Attention: Note that for both internal and SQL data types, any string literals in an SQL filter must be enclosed in single quotation marks. The policy engine interprets double quotation marks before it processes the SQL filter. Using double quotation marks inside an SQL filter causes parsing errors.

Operators

The type of comparison is specified by one of the standard comparison operators. The SQL filter syntax supports the following comparative operators:

  • >
  • <
  • =
  • <=
  • =>
  • !=
  • LIKE
    Restriction: You can use the LIKE operator with regular expressions as supported by the underlying data source.

The SQL filter syntax supports the AND, OR and NOT boolean operators.

Tip: Multiple comparisons can be used together with the AND, OR, and NOT operators.

Order of operation

You can specify the order in which expressions in the SQL are evaluated using parentheses.

Regular expressions

Refer to your vendor's database documentation for specific syntax rules on using regular expressions. Some databases may require you to escape reserved characters in the query. SQL filters in a policy are escaped using the backslash character (\). If the database also uses the backslash as an escape character, you may need to double escape the characters to ensure the regular expression filter is processed correctly. Since the SQL filter string is parsed twice, firstly by the policy engine and then by the database SQL parser, double escaping may be needed to preserve the query during processing.

Note: When writing an SQL filter with a regular expression for an ObjectServer that requires reserved characters to be escaped twice, you will need to escape the expression three times. This is necessary since the filter will be parsed three times, firstly by the policy engine, then the ObjectServer's SQL parser and finally by the ObjectServer's regular expression library. For example, to escape the parentheses in the string "1_(22)", use this SQL filter: "1_\\\\(22\\\\)".

Examples

Here is an example of an SQL filter:

Location = 'NYC'
Location LIKE 'NYC.*'
Facility = 'Wandsworth' AND Facility = 'Putney'
Facility = 'Wall St.' OR Facility = 'Midtown'
NodeID >= 123345
NodeID != 123234

You can use this filter to get all data items where the value of the Location field is New York:

Location = 'New York'

Using this filter you get all data items where the value of the Location field is New York or New Jersey:

Location = 'New York' OR Location = 'New Jersey'

To get all data items where the value of the Location field is Chicago or Los Angeles and the value of the Level field is 3:

(Location = 'New York' OR Location = 'New Jersey') AND Level = 3