|Oracle® OLAP DML Reference
10g Release 1 (10.1)
Part Number B10339-02
A Boolean expression is a logical statement that is either
FALSE. Boolean expressions can compare data of any type as long as both parts of the expression have the same basic data type. You can test data to see if it is equal to, greater than, or less than other data.
A Boolean expression can consist of Boolean data, such as the following:
BOOLEAN values (
NO, and their synonyms,
BOOLEAN variables or formulas
Functions that yield
BOOLEAN values calculated by comparison operators
For example, assume that your code contains the following Boolean expression.
actual GT 20000
When processing this expression, Oracle OLAP compares each value of the variable
actual to the constant 20,000. When the value is greater than 20,000, then the statement is
TRUE; when the value is less than or equal to 20,000, then the statement is
When you are supplying a Boolean value, you can type either
TRUE for a true value, and
FALSE for a false value. When the result of a Boolean calculation is produced, the defaults are
NO in the language specified by the NLS_LANGUAGE option. The read-only YESSPELL and NOSPELL options record the
Table 2-9, "Comparison and Logical Operators" shows the comparison and logical operators. Each operator has a priority that determines its order of evaluation. Operators of equal priority are evaluated left to right, unless parentheses change the order of evaluation. However, the evaluation is halted when the truth value is already decided. For example, in the following expression, the TOTAL function is never executed because the first phrase determines that the whole expression is true.
yes EQ yes OR TOTAL(sales) GT 20000
A Boolean expression is a three-part clause that consists of two items to be compared, separated by a comparison operator. You can create a more complex Boolean expression by joining any of these three-part expressions with the
OR logical operators. Each expression that is connected by
OR must be a complete Boolean expression in itself, even when it means specifying the same variable several times.
For example, the following expression is not valid because the second part is incomplete.
sales GT 50000 AND LE 20000
In the next expression, both parts are complete so the expression is valid.
sales GT 50000 AND sales LE 20000
When you combine several Boolean expressions, the whole expression must be valid even when the truth value can be determined by the first part of the expression. The whole expression is compiled before it is evaluated, so when there are undefined variables in the second part of a Boolean expression, you get an error.
NOT operator, with parentheses around the expression, to reverse the sense of a Boolean expression.
The following two expressions are equivalent.
district NE 'BOSTON' NOT(district EQ 'BOSTON')
Example 3-1 Using Boolean Comparisons
LIMIT time TO FIRST 2 LIMIT geography TO 'BOSTON' REPORT DOWN product ACROSS time: f.sales GT 7500
REPORT statement returns the following data.
CHANNEL: TOTALCHANNEL GEOGRAPHY: BOSTON ---F.SALES GT 7500--- --------TIME--------- PRODUCT Jan02 Feb02 -------------- ---------- ---------- Portaudio NO NO Audiocomp YES YES TV NO NO VCR NO NO Camcorder YES YES Audiotape NO NO Videotape YES YES
When the data you are comparing in a Boolean expression involves an NA value, a
NO result is returned when that makes sense. For example, when you test whether an NA value is equal to a non-NA value, then the result is
NO. However, when the result would be misleading, then
NA is returned. For example, testing whether an NA value is less than or greater than a non–NA value gives a result of
Table 3-4, "Boolean Expressions with NA Values that Result in non-NA Values" shows the results of Boolean expressions involving NA values, which yield non-NA values.
You are comparing two floating point numbers and at least one number is the result of an arithmetic operation.
You have mixed
DECIMAL data types in a comparison.
Oracle recommends that you use the ABS and ROUND functions to do approximate tests for equality and avoid all three causes of unexpected comparison failure. When using ABS or ROUND, you can adjust the absolute difference or the rounding factor to values you feel are appropriate for your application. When speed of calculation is important, then you probably want to use the ABS rather than the ROUND function.
expense is a decimal variable whose value is set by a calculation. When the result of the calculation is 100.000001 and the number of decimal places is two, then the value appears in output as 100.00. However, the output of the following statement returns
SHOW expense EQ 100.00
You can use the
ABS or the
ROUND function to ignore these slight differences when making comparisons.
A standard restriction on the use of floating point numbers in a computer language is that you cannot expect exact equality in a comparison of two floating point numbers when either number is the result of an arithmetic operation. For example, on some systems, the following statement returns a
NO instead of the expected
SHOW .1 + .2 EQ .3
When you deal with decimal data, you should not code direct comparisons. Instead, you can use the ABS or the ROUND function to allow a tolerance for approximate equality. For example, either of the following two statements produce the desired
SHOW ABS((.1 + .2) - .3) LT .00001 SHOW ROUND(.1 + .2) EQ ROUND(.3, .00001)
You cannot expect exact equality between
NUMBER representations of a decimal number with a fractional component, because the
NUMBER data types have more significant digits to approximate fractional components that cannot be represented exactly.
Suppose you define a variable with a
SHORTDECIMAL data type and set it to a fractional decimal number, then compare the
SHORTDECIMAL number to the fractional decimal number, as shown here.
DEFINE sdvar SHORTDECIMAL sdvar = 1.3 SHOW sdvar EQ 1.3
The comparison is likely to return
NO. What happens in this situation is that the literal is automatically typed as
DECIMAL and converts the
DECIMAL, which extends the decimal places with zeros. A bit-by-bit comparison is then performed, which fails. The same comparison using a variable with a
DECIMAL or a
NUMBER data type is likely to return
There are several ways to avoid this type of comparison failure:
Do not mix the
NUMBER types in comparisons. To avoid mixing these two data types, you should generally avoid defining variables with decimal components as
ROUND function to allow for approximate equality. The following statements both produce
SHOW ABS(sdvar - 1.3) LT .00001 SHOW ROUND(sdvar, .00001) EQ ROUND(.3, .00001)
Values are not compared in the same dimension based on their textual values. Instead, Oracle OLAP compares the positions of the values in the default status of the dimension. This enables you to specify statements like the following statement.
REPORT district LT 'Seattle'
Statements are interpreted such as these using the following process:
The text literal
'Seattle' is converted to its position in the
district default status list of the dimension.
That position is compared to the position of all other values in the
As shown by the following report, the value
YES is returned for districts that are positioned before
Seattle in the
district default status list of the dimension, and
REPORT 22 WIDTH district LT 'Seattle' District DISTRICT LT 'Seattle' -------------- ---------------------- Boston YES Atlanta YES Chicago YES Dallas YES Denver YES Seattle NO
A more complex example assigns increasing values to the variable
quota based on initial values assigned to the first six months. The comparison depends on the position of the values in the
month dimension. Because it is a time dimension, the values are in chronological order.
quota = IF month LE 'Jun02' THEN 100 ELSE LAG(quota, 1, month)* 1.15
However, when you compare values from different dimensions, such as in the expression
district, then the only common denominator is
TEXT, and text values are compared, not dimension positions.
You can compare two dates with any of the Boolean comparison operators. For dates, "less" means before and "greater" means after. The expressions being compared can include any of the date calculations discussed in "Comparison and Logical Operators". For example, in a billing application, you can determine whether today is 60 or more days after the billing date in order to send out a more strongly worded bill.
bill.date + 60 LE SYSDATE
When you compare text data, you must specify the text exactly as it appears, with punctuation, spaces, and uppercase or lowercase letters. A text literal must be enclosed in single quotes. For example, this expression tests whether the first letter of each employee's name is greater than the letter "M."
EXTCHARS(employee.name, 1, 1) GT 'M'
You can compare
ID values, but they can only be equal when they are the same length. When you test whether a text value is greater or less than another, the ordering is based on the setting of the NLS_SORT option.
You can compare numbers with text by first converting the number to text. Ordering is based on the values of the characters. This can produce unexpected results because the text is evaluated from left to right. For example, the text literal
1234 is greater than
2, the second character in the first text literal, is greater than
0, the second character in the second text literal.
The result of the following
SHOW statement is
SHOW name.desc EQ name.label
The result of the following statements is
name.desc = '3-Person' SHOW name.desc EQ name.label
An underscore (_) character in a pattern matches any single character.
A percent (%) character in a pattern matches zero or more characters in the first string.
For example, a pattern of
%AT_ matches any text that contains zero or more characters, followed by the characters
AT, followed by any other single character. Both
LIKE is used to compare them with the pattern
No negation operator exists for LIKE. To accomplish negation, you must negate the entire expression. For example, the result of the following statement is
SHOW NOT ('Boston' LIKE 'Bo%')
You can also compare a text literal to a relation. A relation contains values of the related dimension and the text literal is compared to a value of that dimension. For example,
district holds values of
region, so you can do the following comparison.
region.district EQ 'West'