6 Conditions

A condition specifies a combination of one or more expressions and logical operators and returns a value of `TRUE`, `FALSE`, or `UNKNOWN`.

6.1 Introduction to Conditions

Oracle CQL provides the following conditions:

You must use appropriate condition syntax whenever `condition` appears in Oracle CQL statements.

You can use a condition in the `WHERE` clause of these statements:

• `SELECT`

You can use a condition in any of these clauses of the `SELECT` statement:

• `WHERE`

• `HAVING`

"Query"

A condition could be said to be of a logical datatype.

The following simple condition always evaluates to `TRUE`:

```1 = 1
```

The following more complex condition adds the `salary` value to the `commission_pct` value (substituting the value 0 for null using the `nvl` function) and determines whether the sum is greater than the number constant 25000:

```NVL(salary, 0) + NVL(salary + (salary*commission_pct, 0) > 25000)
```

Logical conditions can combine multiple conditions into a single condition. For example, you can use the `AND` condition to combine two conditions:

```(1 = 1) AND (5 < 7)
```

Here are some valid conditions:

```name = 'SMITH'
S0.department_id = S2.department_id
hire_date > '01-JAN-88'
commission_pct IS NULL AND salary = 2100
```

6.1.1 Condition Precedence

Precedence is the order in which Oracle CEP evaluates different conditions in the same expression. When evaluating an expression containing multiple conditions, Oracle CEP evaluates conditions with higher precedence before evaluating those with lower precedence. Oracle CEP evaluates conditions with equal precedence from left to right within an expression.

Table 6-1 lists the levels of precedence among Oracle CQL condition from high to low. Conditions listed on the same line have the same precedence. As the table indicates, Oracle evaluates operators before conditions.

Table 6-1 Oracle CQL Condition Precedence

Type of Condition Purpose

Oracle CQL operators are evaluated before Oracle CQL conditions

`=, <>, <, >, <=, >=`

comparison

`IS NULL`, `IS NOT NULL`, `LIKE`, `BETWEEN`, `IN`, `NOT IN`

comparison

`NOT`

exponentiation, logical negation

`AND`

conjunction

`OR`

disjunction

`XOR`

disjunction

6.2 Comparison Conditions

Comparison conditions compare one expression with another. The result of such a comparison can be `TRUE`, `FALSE`, or `NULL`.

When comparing numeric expressions, Oracle CEP uses numeric precedence to determine whether the condition compares `INTEGER`, `FLOAT`, or `BIGINT` values.

Two objects of nonscalar type are comparable if they are of the same named type and there is a one-to-one correspondence between their elements.

A comparison condition specifies a comparison with expressions or view results.

Table 6-2 lists comparison conditions.

Table 6-2 Comparison Conditions

Type of Condition Purpose Example

`=`

Equality test.

```<query id="Q1"><![CDATA[
SELECT *
FROM S0
WHERE salary = 2500
]]></query>
```

`<>`

Inequality test.

```<query id="Q1"><![CDATA[
SELECT *
FROM S0
WHERE salary <> 2500
]]></query>
```

`>`

`<`

Greater-than and less-than tests.

```<query id="Q1"><![CDATA[
SELECT * FROM S0
WHERE salary > 2500
]]></query>
<query id="Q1"><![CDATA[
SELECT * FROM S0
WHERE salary < 2500
]]></query>
```

`>=`

`<=`

Greater-than-or-equal-to and less-than-or-equal-to tests.

```<query id="Q1"><![CDATA[
SELECT * FROM S0
WHERE salary >= 2500
]]></query>
<query id="Q1"><![CDATA[
SELECT * FROM S0
WHERE salary <= 2500
]]></query>
```

`like`

Pattern matching tests on character data.

For more information, see Section 6.4, "LIKE Condition".

```<query id="q291"><![CDATA[
select * from SLk1
where first1 like "^Ste(v|ph)en\$"
]]></query>
```

`is [not] null`

Null tests.

For more information, see Section 6.6, "Null Conditions".

```<query id="Q1"><![CDATA[
SELECT last_name
FROM S0
WHERE commission_pct
IS NULL
]]></query>
```
```<query id="Q2"><![CDATA[
SELECT last_name
FROM S0
WHERE commission_pct
IS NOT NULL
]]></query>
```

`[not] in`

Set and membership tests.

For more information, see Section 6.8, "IN Condition".

```<query id="Q1"><![CDATA[
SELECT * FROM S0
WHERE job_id NOT IN
('PU_CLERK','SH_CLERK')
]]></query>
<view id="V1" schema="salary"><![CDATA[
SELECT salary
FROM S0
WHERE department_id = 30
]]></view>
<view id="V2" schema="salary"><![CDATA[
SELECT salary
FROM S0
WHERE department_id = 20
]]></view>
<query id="Q2"><![CDATA[
V1 IN V2
]]></query>
```

condition::=

6.3 Logical Conditions

A logical condition combines the results of two component conditions to produce a single result based on them or to invert the result of a single condition. Table 6-3 lists logical conditions.

Table 6-3 Logical Conditions

Type of Condition Operation Examples
```NOT
```

Returns `TRUE` if the following condition is `FALSE`. Returns `FALSE` if it is `TRUE`. If it is `UNKNOWN`, then it remains `UNKNOWN`.

```<query id="Q1"><![CDATA[
SELECT *
FROM S0
WHERE NOT (job_id IS NULL)
]]></query>
```
```AND
```

Returns `TRUE` if both component conditions are `TRUE`. Returns `FALSE` if either is `FALSE`. Otherwise returns `UNKNOWN`.

```<query id="Q1"><![CDATA[
SELECT *
FROM S0
WHERE job_id = 'PU_CLERK'
AND dept_id = 30
]]></query>
```
```OR
```

Returns `TRUE` if either component condition is `TRUE`. Returns `FALSE` if both are `FALSE`. Otherwise returns `UNKNOWN`.

```<query id="Q1"><![CDATA[
SELECT *
FROM S0
WHERE job_id = 'PU_CLERK'
OR department_id = 10
]]></query>
```
```XOR
```

Returns `TRUE` if either component condition is `TRUE`. Returns `FALSE` if both are `FALSE`. Otherwise returns `UNKNOWN`.

```<query id="Q1"><![CDATA[
SELECT *
FROM S0
WHERE job_id = 'PU_CLERK'
XOR department_id = 10
]]></query>
```

Table 6-4 shows the result of applying the `NOT` condition to an expression.

Table 6-4 NOT Truth Table

-- TRUE FALSE UNKNOWN

NOT

`FALSE`

`TRUE`

`UNKNOWN`

Table 6-5 shows the results of combining the `AND` condition to two expressions.

Table 6-5 AND Truth Table

AND TRUE FALSE UNKNOWN

TRUE

`TRUE`

`FALSE`

`UNKNOWN`

FALSE

`FALSE`

`FALSE`

`FALSE`

UNKNOWN

`UNKNOWN`

`FALSE`

`UNKNOWN`

For example, in the `WHERE` clause of the following `SELECT` statement, the `AND` logical condition returns values only when both `product.levelx is BRAND` and `v1.prodkey` equals `product.prodkey`:

```<view id="v2" schema="region, dollars, month_"><![CDATA[
select
v1.region,
v1.dollars,
v1.month_
from
v1,
product
where
product.levelx = "BRAND" and v1.prodkey = product.prodkey
]]></view>
```

Table 6-6 shows the results of applying `OR` to two expressions.

Table 6-6 OR Truth Table

OR TRUE FALSE UNKNOWN

TRUE

`TRUE`

`TRUE`

`TRUE`

FALSE

`TRUE`

`FALSE`

`UNKNOWN`

UNKNOWN

`TRUE`

`UNKNOWN`

`UNKNOWN`

For example, the following query returns the internal account identifier for `RBK` or `RBR` accounts with a risk of type 2:

```<view id="ValidAccounts" schema="ACCT_INTRL_ID"><![CDATA[
select ACCT_INTRL_ID from Acct
where (
((MANTAS_ACCT_BUS_TYPE_CD = "RBK") OR (MANTAS_ACCT_BUS_TYPE_CD = "RBR")) AND
(ACCT_EFCTV_RISK_NB != 2)
)
]]></view>
```

Table 6-7 shows the results of applying `XOR` to two expressions.

Table 6-7 XOR Truth Table

XOR TRUE FALSE UNKNOWN

TRUE

`FALSE`

`TRUE`

`UNKNOWN`

FALSE

`TRUE`

`FALSE`

`UNKNOWN`

UNKNOWN

`UNKNOWN`

`UNKNOWN`

`UNKNOWN`

For example, the following query returns `c1` and `c2` when `c1` is 15 and `c2` is 0.14 or when `c1` is 20 and `c2` is 100.1, but not both:

```<query id="q6"><![CDATA[
select
S2.c1,
S3.c2
from
S2[range 1000], S3[range 1000]
where
(S2.c1 = 15 and S3.c2 = 0.14) xor (S2.c1 = 20 and S3.c2 = 100.1)
]]></query>
```

6.4 LIKE Condition

The `LIKE` condition specifies a test involving regular expression pattern matching. Whereas the equality operator (=) exactly matches one character value to another, the `LIKE` conditions match a portion of one character value to another by searching the first value for the regular expression pattern specified by the second. `LIKE` calculates strings using characters as defined by the input character set.

like_condition::=

In this syntax:

• `arith_expr` is an arithmetic expression whose value is compared to `const_string`.

• `const_string` is a constant value regular expression to be compared against the `arith_expr`.

If any of `arith_expr` or `const_string` is null, then the result is unknown.

The `const_string` can contain any of the regular expression assertions and quantifiers that `java.util.regex` supports: that is, a regular expression that is specified in string form in a syntax similar to that used by Perl.

Table 6-8 describes the `LIKE` conditions.

Table 6-8 LIKE Conditions

Type of Condition Operation Example
```x LIKE y
```

`TRUE` if `x` does match the pattern `y`, `FALSE` otherwise.

```<query id="q291"><![CDATA[
select * from SLk1 where first1 like "^Ste(v|ph)en\$"
]]></query>
```
```<query id="q292"><![CDATA[
select * from SLk1 where first1 like ".*intl.*"
]]></query>
```

"lk"

For more information on Perl regular expressions, see `http://perldoc.perl.org/perlre.html`.

6.4.1 Examples

This condition is true for all `last_name` values beginning with `Ma`:

```last_name LIKE '^Ma'
```

All of these `last_name` values make the condition true:

```Mallin, Markle, Marlow, Marvins, Marvis, Matos
```

Case is significant, so `last_name` values beginning with `MA`, `ma`, and `mA` make the condition false.

Consider this condition:

```last_name LIKE 'SMITH[A-Za-z]'
```

This condition is true for these `last_name` values:

```SMITHE, SMITHY, SMITHS
```

This condition is false for `SMITH` because the `[A-Z]` must match exactly one character of the `last_name` value.

Consider this condition:

```last_name LIKE 'SMITH[A-Z]+'
```

This condition is false for `SMITH` but true for these `last_name` values because the `[A-Z]+` must match 1 or more such characters at the end of the word.

```SMITHSTONIAN, SMITHY, SMITHS
```

For more information, see `http://java.sun.com/j2se/1.5.0/docs/api/java/util/regex/Pattern.html`.

6.5 Range Conditions

A range condition tests for inclusion in a range.

between_condition::=

Table 6-9 describes the range conditions.

Table 6-9 Range Conditions

Type of Condition Operation Example
```BETWEEN x AND y
```

Greater than or equal to `x` and less than or equal to `y`.

```<query id="Q1"><![CDATA[
SELECT * FROM S0
WHERE salary
BETWEEN 2000 AND 3000
]]></query>
```

6.6 Null Conditions

A `NULL` condition tests for nulls. This is the only condition that you should use to test for nulls.

null_conditions::=

Table 6-10 lists the null conditions.

Table 6-10 Null Conditions

Type of Condition Operation Example
```IS [NOT] NULL
```

Tests for nulls.

```<query id="Q1"><![CDATA[
SELECT last_name
FROM S0
WHERE commission_pct
IS NULL
]]></query>
```
```<query id="Q2"><![CDATA[
SELECT last_name
FROM S0
WHERE commission_pct
IS NOT NULL
]]></query>
```

6.7 Compound Conditions

A compound condition specifies a combination of other conditions.

compound_conditions::=

Section 6.3, "Logical Conditions" for more information about `NOT`, `AND`, and `OR` conditions

6.8 IN Condition

You can use the `IN` and `NOT IN` condition in the following ways:

Note:

You cannot combine these two usages.

When using the `NOT IN` condition, be aware of the effect of null values as Section 6.8.3, "NOT IN and Null Values" describes.

6.8.2 Using IN and NOT IN as a Membership Condition

In this usage, the query will be a `SELECT`-`FROM`-`WHERE` query that either tests whether or not one argument is a member of a list of arguments of the same type or tests whether or not a list of arguments is a member of a set of similar lists.

in_condition_membership::=

non_mt_arg_list_set::=

When you use `IN` or `NOT IN` to test whether or not a `non_mt_arg_list` is a member of a set of similar lists, then you must use a `non_mt_arg_list_set`. Each `non_mt_arg_list` in the `non_mt_arg_list_set` must match the `non_mt_arg_list` to the left of the condition in number and type of arguments.

Note:

You cannot combine this usage with `in_condition_set` as Section 6.8.1, "Using IN and NOT IN as a Set Operation" describes.

Consider the query `Q1` in Example 6-1 and the data stream `S0` in Example 6-2. Stream `S0` has schema `(c1 integer, c2 integer)`. Example 6-3 shows the relation that the query returns. In Q1, the `non_mt_arg_list_set` is `((50,4),(4,5))`. Note that each `non_mt_arg_list` that it contains matches the number and type of arguments in the `non_mt_arg_list` to the left of the condition, `(c1, c2)`.

Example 6-1 S [range C on E] INTERVAL Value: Query

```<query id="Q1"><![CDATA[
select c1,c2 from S0[range 1] where (c1,c2) in ((50,4),(4,5))
]]></query>
```

Example 6-2 S [range C on E] INTERVAL Value: Stream Input

```Timestamp   Tuple
1000        50, 4
2000        30, 6
3000          , 5
4000        22,
h 200000000
```

Example 6-3 S [range C on E] INTERVAL Value: Relation Output

```Timestamp   Tuple Kind  Tuple
1000:       +           50,4
2000:       -           50,4
```

6.8.3 NOT IN and Null Values

If any item in the list following a `NOT` `IN` operation evaluates to null, then all stream elements evaluate to `FALSE` or `UNKNOWN`, and no rows are returned. For example, the following statement returns `c1` and `c2` if `c1` is neither 50 nor 30:

```<query id="check_notin1"><![CDATA[
select c1,c2 from S0[range 1]
where
c1 not in (50, 30)
]]></query>
```

However, the following statement returns no stream elements:

```<query id="check_notin1"><![CDATA[
select c1,c2 from S0[range 1]
where
c1 not in (50, 30, NULL)
]]></query>
```

The preceding example returns no stream elements because the `WHERE` clause condition evaluates to:

```c1 != 50 AND c1 != 30 AND c1 != null
```

Because the third condition compares `c1` with a null, it results in an `UNKNOWN`, so the entire expression results in `FALSE` (for stream elements with `c1` equal to 50 or 30). This behavior can easily be overlooked, especially when the `NOT` `IN` operator references a view.

Moreover, if a `NOT` `IN` condition references a view that returns no stream elements at all, then all stream elements will be returned, as shown in the following example. Since V1 returns no stream elements at all, Q1 will return

```<view id="V1" schema="c1"><![CDATA[
IStream(select * from S1[range 10 slide 10] where 1=2)
]]></view>
<view id="V2" schema="c1"><![CDATA[
IStream(select * from S1[range 10 slide 10] where c1=2)
]]></view>
<query id="Q1"><![CDATA[
V1 not in V2
]]></query>
```