A condition specifies a combination of one or more expressions and logical operators and returns a value of TRUE
, FALSE
, or UNKNOWN
.
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:
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 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
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 |
|
comparison |
|
exponentiation, logical negation |
|
conjunction |
|
disjunction |
|
disjunction |
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> |
|
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> |
|
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> |
|
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> <query id="Q2"><![CDATA[ SELECT * FROM S0 WHERE salary NOT IN (V1) ]]></query> |
(arith_expr::=, const_string::=
, non_mt_arg_list::=
, non_mt_arg_list_set::=
)
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.
Type of Condition | Operation | Examples |
---|---|---|
NOT |
Returns |
<query id="Q1"><![CDATA[ SELECT * FROM S0 WHERE NOT (job_id IS NULL) ]]></query> |
AND |
Returns |
<query id="Q1"><![CDATA[ SELECT * FROM S0 WHERE job_id = 'PU_CLERK' AND dept_id = 30 ]]></query> |
OR |
Returns |
<query id="Q1"><![CDATA[ SELECT * FROM S0 WHERE job_id = 'PU_CLERK' OR department_id = 10 ]]></query> |
XOR |
Returns |
<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.
AND | TRUE | FALSE | UNKNOWN |
---|---|---|---|
TRUE |
|
|
|
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.
OR | TRUE | FALSE | UNKNOWN |
---|---|---|---|
TRUE |
|
|
|
FALSE |
|
|
|
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.
XOR | TRUE | FALSE | UNKNOWN |
---|---|---|---|
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>
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.
(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.
Type of Condition | Operation | Example |
---|---|---|
x LIKE y |
|
create query q291 as select * from SLk1 where first1 like "^Ste(v|ph)en$" |
See Also:
"lk"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
.
A range condition tests for inclusion in a range.
Table 6-9 describes the range conditions.
A NULL
condition tests for nulls. This is the only condition that you should use to test for nulls.
Table 6-10 lists the 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> |
An in_condition
is a membership condition. It tests a value for membership in a list of values or view.
(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.
Type of Condition | Operation | Example |
---|---|---|
IN |
Equal-to-any-member-of test. Equivalent to |
<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 != |
<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>