6 Conditions

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:

• 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 Event Processing 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>
<view id="V2" schema="salary"><![CDATA[ 
   SELECT salary 
   FROM S0
   WHERE department_id = 20
]]></view>
<query id="Q2"><![CDATA[
  V1 IN V2
]]></query>

condition::=

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

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

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

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

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

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

• in_condition_set: Section 6.8.1, "Using IN and NOT IN as a Set Operation"

• in_condition_membership: Section 6.8.2, "Using IN and NOT IN as a Membership Condition"

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

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

non_mt_arg_list_set::=

(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

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

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

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

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