Skip Headers
Oracle® CEP CQL Language Reference
11g Release 1 (11.1.1)
E12048-02
  Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
 
Next
Next
 

12 Conditions

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

12.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:

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

12.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 12-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 12-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

comparison

NOT

exponentiation, logical negation

AND

conjunction

OR

disjunction

XOR

disjunction


12.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 12-2 lists comparison conditions.

Table 12-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 12.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 12.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

Membership tests.

For more information, see Section 12.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>
<query id="Q2"><![CDATA[
  SELECT * FROM S0
  WHERE salary NOT IN (V1)
]]></query>

condition::=

Surrounding text describes condition.gif.

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

12.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 12-3 lists logical conditions.

Table 12-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 12-4 shows the result of applying the NOT condition to an expression.

Table 12-4 NOT Truth Table

-- TRUE FALSE UNKNOWN

NOT

FALSE

TRUE

UNKNOWN


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

Table 12-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 12-6 shows the results of applying OR to two expressions.

Table 12-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 12-7 shows the results of applying XOR to two expressions.

Table 12-7 XOR Truth Table

XOR TRUE FALSE UNKNOWN

TRUE

FALSE

TRUE


FALSE

TRUE

FALSE


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>

12.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.gif.

(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 12-8 describes the LIKE conditions.

Table 12-8 LIKE Conditions

Type of Condition Operation Example
x LIKE y

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

create query q291 as select * from SLk1 where first1 like "^Ste(v|ph)en$" 


See Also:

"lk"

12.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.

12.5 Range Conditions

A range condition tests for inclusion in a range.

between_condition::=

Surrounding text describes between_condition.gif.

(arith_expr::=)

Table 12-9 describes the range conditions.

Table 12-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>

12.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.gif.

(Chapter 11, "Expressions")

Table 12-10 lists the null conditions.

Table 12-10 Null Conditions

Type of Condition Operation Example
IS [NOT] NULL 

Tests for nulls.

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

12.7 Compound Conditions

A compound condition specifies a combination of other conditions.

compound_conditions::=

Surrounding text describes compound_conditions.gif.

See Also:

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

12.8 IN Condition

An in_condition is a membership condition. It tests a value for membership in a list of values or view.

in_condition::=

Surrounding text describes in_condition.gif.

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

If you use the upper form of the in_condition condition (with a single expression to the left of the operator), then you must use a non_mt_arg_list. If you use the lower form of this condition (with multiple expressions to the left of the operator), then you must use a non_mt_arg_list_set, and the expressions in each list of expressions must match in number and datatype.

Table 12-11 lists the form of IN condition.

Table 12-11 IN Conditions

Type of Condition Operation Example
IN

Equal-to-any-member-of test. Equivalent to =ANY.

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

Equivalent to !=ALL. Evaluates to FALSE if any member of the set is NULL.

<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>
<query id="Q2"><![CDATA[ 
    SELECT * FROM S0
    WHERE salary NOT IN (V1)
]]></query>

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 the c1 and c2 if c1 is neither 50 nor 30:

create query check_notin1 as 
    select c1,c2 from S0[range 1] 
    where 
        c1 not in (50, 30);

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:

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