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.

This chapter includes the following sections:

• Introduction to Conditions

• Comparison Conditions

• Logical Conditions

• LIKE Condition

• Range Conditions

• Null Conditions

• Compound Conditions

• IN Condition.

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

See Also:

Query.

A condition could be said to be of a logical data type.

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 Stream Explorer evaluates different conditions in the same expression. When evaluating an expression containing multiple conditions, Oracle Stream Explorer evaluates conditions with higher precedence before evaluating those with lower precedence. Oracle Stream Explorer 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 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 Stream Explorer 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 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 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 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::=

(aggr_expr::= and 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.

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.

The LIKE condition with the syntax of the comparison String supports % for 0 or more characters and - for any single character in coherence.

like_condition::=

(arith_expr::= )

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>

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

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

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

See Also:

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:

• in_condition_set: Using IN and NOT IN as a Membership Condition

• in_condition_membership: Using IN and NOT IN as a Membership Condition.

Note:

You cannot combine these two usages.

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

6.8.1 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::= and non_mt_arg_list_set::= )

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.

Consider the query Q1 and the data stream S0. Stream S0 has schema (c1 integer, c2 integer). The following example 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).

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

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

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

6.8.2 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 all V2 stream elements.

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