Skip Headers
Oracle® Fusion Middleware CQL Language Reference for Oracle Complex Event Processing
11g Release 1 (11.1.1.6.3)

Part Number E12048-10
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

6 Conditions

This chapter provides a reference to conditions in Oracle Continuous Query Language (Oracle CQL). 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

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:

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

See Also:

"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

See Section 4.1.2, "What You May Need to Know About Operator Precedence".

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

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::=

Surrounding text describes condition.png.

(arith_expr::=, const_string::=, non_mt_arg_list::=, non_mt_arg_list_set::=, sfw_block::=)

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::=

Surrounding text describes like_condition.png.

(arith_expr::=, const_string::=)

In this syntax:

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>

See Also:

"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::=

Surrounding text describes between_condition.png.

(arith_expr::=)

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::=

Surrounding text describes null_conditions.png.

(Chapter 5, "Expressions")

Table 6-10 lists the null conditions.

Table 6-10 Null Conditions

Type of Condition Operation Example
IS [NOT] NULL 

Tests for nulls.

See Also: Section 2.5, "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::=

Surrounding text describes compound_conditions.png.

See Also:

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.1 Using IN and NOT IN as a Set Operation

See "BINARY Example: IN and NOT IN".

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::=

Surrounding text describes in_condition_membership.png.

(arith_expr::=, non_mt_arg_list::=, non_mt_arg_list_set::=)

non_mt_arg_list_set::=

Surrounding text describes non_mt_arg_list_set.png.

(non_mt_arg_list::=)

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>