Search condition general syntax

A search condition is a single predicate or several predicates connected by the logical operators AND or OR. A predicate is an operation on expressions that evaluates to TRUE, FALSE, or UNKNOWN. If a predicate evaluates to TRUE for a row, the row qualifies for further processing. If the predicate evaluates to FALSE or NULL for a row, the row is not available for operations.

SQL syntax

[NOT] 
{BetweenPredicate | ComparisonPredicate | InPredicate |
     LikePredicate| NullPredicate | InfinitePredicate |      NanPredicate | QuantifiedPredicate |(SearchCondition)} 
[{AND | OR} [NOT] 
{BetweenPredicate | ComparisonPredicate |InPredicate |      LikePredicate|NullPredicate | QuantifiedPredicate      |(SearchCondition)}
] [...]

Parameters

Component	Description
NOT, AND, OR	Logical operators with the following functions: NOT negates the value of the predicate that follows it. AND evaluates to TRUE if both the predicates it joins evaluate to TRUE. OR evaluates to TRUE if either predicate it joins evaluates to TRUE, and to FALSE if both predicates evaluates to FALSE. See "Description" for a description of how these operators work when predicates evaluate to NULL.
`BetweenPredicate`	Determines whether an expression is within a certain range of values. For example: A BETWEEN B AND C is equivalent to A >= B AND A<= C.
`ComparisonPredicate`	Compares two expressions or list of two expressions using one of the operators <, <=, >, >=, =, <>.
`InPredicate`	Determines whether an expression or list of expressions matches an element within a specified set.
`ExistsPredicate`	Determines whether a subquery returns any row.
`LikePredicate`	Determines whether an expression contains a particular -character string pattern.
`NullPredicate`	Determines whether a value is NULL.
`InfinitePredicate`	Determines whether an expression is infinite (positive or negative infinity).
`NanPredicate`	Determines whether an expression is the undefined result of an operation ("not a number.")
`QuantifiedPredicate`	Determines whether an expression or list of expressions bears a particular relationship to a specified set.
(`SearchCondition`)	One of the above predicates, enclosed in parentheses.

Description

Predicates in a search condition are evaluated as follows:
- Predicates in parentheses are evaluated first.
- NOT is applied to each predicate.
- AND is applied next, left to right.
- OR is applied last, left to right.
Figure 4-1 shows the values that result from logical operations. A question mark (?) represents the NULL value.

Figure 4-1 Values that result from logical operations

Description of "Figure 4-1 Values that result from logical operations"

When the search condition for a row evaluates to NULL, the row does not satisfy the search condition and the row is not operated on.
You can compare only compatible data types.
- TT_TINYINT, TT_SMALLINT, TT_INTEGER, TT_BIGINT, NUMBER, BINARY_FLOAT and BINARY_DOUBLE are compatible.
- CHAR, VARCHAR2, BINARY, and VARBINARY are compatible, regardless of length.
- CHAR, VARCHAR2, NCHAR, NVARCHAR2, TT_TIME, DATE and TIMESTAMP are compatible.
See Chapter 3, "Expressions" for information on value extensions during comparison operations.
See "Numeric data types" for information about how TimesTen compares values of different but compatible types.