Criteria

You are looking at an older version of the documentation. The latest version is found here.

Criteria may be:

Predicates that evaluate to TRUE or FALSE;
Logical criteria that combines criteria (AND, OR, NOT);
A value expression with type boolean.

Usage:

SQL
```
criteria AND|OR criteria
```
SQL
```
NOT criteria
```
SQL
```
(criteria)
```

SQL

expression (=|<>|!=|<|>|<=|>=) (expression|((ANY|ALL|SOME) subquery))

SQL
```
expression [NOT] IS NULL
```

SQL

expression [NOT] IN (expression[,expression]*)|subquery

SQL
```
expression [NOT] LIKE pattern [ESCAPE char]
```
Matches the string expression against the given string pattern. The pattern may contain % to match any number of characters and _ to match any single character. The escape character can be used to escape the match characters % and _.
SQL
```
expression [NOT] SIMILAR TO pattern [ESCAPE char]
```
SIMILAR TO is a cross between LIKE and standard regular expression syntax. % and _ are still used, rather than .* and ., respectively.

The Data Virtuality Server does not exhaustively validate SIMILAR TO pattern values, but rather the pattern is converted to an equivalent regular expression. We recommend not to rely on general regular expression features when using SIMILAR TO. If additional features are needed, use LIKE_REGEX. We do not recommend sing a non-literal pattern, as pushdown support is limited.
SQL
```
expression [NOT] LIKE_REGEX pattern
```
LIKE_REGEX allows for standard regular expression syntax to be used for matching. This is different from SIMILAR TO and LIKE in that the escape character is no longer used (\ is already the standard escape mechanism in regular expressions, and % and _ have no special meaning. The runtime engine uses the JRE implementation of regular expressions; see the java.util.regex.Pattern class for details.

The Data Virtuality Server does not exhaustively validate LIKE_REGEX pattern values. It is possible to use JRE only regular expression features that are not specified by the SQL specification. Additionally, not all sources support the same regular expression flavour or extensions. In pushdown situations, we recommend to ensure that the pattern used will have the same meaning in the Data Virtuality Server and across all applicable sources.
SQL
```
EXISTS(subquery)
```
SQL
```
expression [NOT] BETWEEN minExpression AND maxExpression
```
The Data Virtuality Server converts BETWEEN into the equivalent form expression >= minExpression AND expression <= maxExpression.
SQL
```
expression
```
In this case, expression has type boolean and the following syntax rules apply:
- The precedence ordering from lowest to highest is a comparison, NOT, AND, OR;
- Criteria nested in parentheses will be logically evaluated prior to evaluating the parent criteria. Some examples of valid criteria are:
  - (balance > 2500.0)
  - 100*(50 - x)/(25 - y) > z
  - concat(areaCode,concat('-',phone)) LIKE '314%1';
- Null values represent an unknown value. Comparison with a null value will evaluate to 'unknown', which can never be TRUE even if not is used.