6 Conditions

6.1 Introduction to Conditions

Oracle CQL provides the following conditions:

• Section 6.2, "Comparison Conditions"

• Section 6.3, "Logical Conditions"

• Section 6.4, "LIKE Condition"

• Section 6.5, "Range Conditions"

• Section 6.6, "Null Conditions"

• Section 6.7, "Compound Conditions"

• Section 6.8, "IN Condition"

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

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

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

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

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

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

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

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

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

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

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.

NOT 

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

AND 

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

OR 

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

XOR 

<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-5 shows the results of combining the AND condition to two expressions.

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.

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.

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

(arith_expr::=, const_string::=)

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.

x LIKE y

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

last_name LIKE '^Ma' 

Mallin, Markle, Marlow, Marvins, Marvis, Matos 

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

SMITHE, SMITHY, SMITHS 

last_name LIKE 'SMITH[A-Z]+' 

SMITHSTONIAN, SMITHY, SMITHS 

6.5 Range Conditions

A range condition tests for inclusion in a range.

between_condition::=

(arith_expr::=)

Table 6-9 describes the range conditions.

BETWEEN x AND 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::=

(Chapter 5, "Expressions")

Table 6-10 lists the null conditions.

IS [NOT] NULL 

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

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

(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 6-11 lists the form of IN condition.

IN

<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 

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