Skip Headers

Oracle9i OLAP Developer's Guide to the OLAP DML
Release 2 (9.2)

Part Number A95298-01
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index



Working with Expressions, 8 of 11

Boolean Expressions

A Boolean expression is a logical statement that is either true or 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.


For example, if you have the Boolean expression shown below, then each value of the variable actual is compared to the constant 20,000. If the value is greater than 20,000, then the statement is true; if the value is less than or equal to 20,000, then the statement is false.

actual GT 20000

When you are supplying a Boolean value, you can type either yes, on, or true for a true value, and no, off, or false for a false value. When the result of a Boolean calculation is produced, the defaults are yes and no in the language specified by the NLS_LANGUAGE option. The read-only YESSPELL and NOSPELL options record the yes and no values.

The following table shows the comparison operators and the logical operators. You use these operators to make expressions in much the same way as arithmetic operators. The column entitled "Priority" indicates the order in which that operator is evaluated.

Table 4-3 Boolean Operators
Operator Operation Example Priority


Returns opposite of Boolean expression

NOT(yes) = no



Equal to

4 EQ 4 = yes



Not equal to

5 NE 2 = yes



Greater than

5 GT 7 = no



Less than

5 LT 7 = yes



Greater than or equal to

8 GE 8 = yes



Less than or equal to

8 LE 9 = yes



Is a date in a time period?

'1JAN02' IN W1.02 = yes



Does a text value match a specified text pattern?

'FINANCE' LIKE '%NAN%' = yes



Both expressions are true

8 GE 8 AND 5 LT 7 = yes



Either expression is true

8 GE 8 OR 5 GT 7 = yes



yes EQ yes OR TOTAL(sales) GT 20000

Creating Boolean Expressions



sales GT 50000 AND LE 20000


sales GT 50000 AND sales LE 20000

When you combine several Boolean expressions, the whole expression must be valid even if 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 will get an error.


The following two expressions are equivalent.

district NE 'BOSTON'
NOT(district EQ 'BOSTON')

Example 4-3 Using Boolean Comparisons


LIMIT geography TO 'BOSTON'
REPORT DOWN product ACROSS time: f.sales GT 7500

This REPORT command returns the following data.

               ---F.SALES GT 7500---
PRODUCT          JAN02      FEB02
PORTAUDIO              no         no
AUDIOCOMP             yes        yes
TV                     no         no
VCR                    no         no
CAMCORDER             yes        yes
AUDIOTAPE              no         no
VIDEOTAPE             yes        yes

Comparing NA Values in Boolean Expressions

When the data you are comparing in a Boolean expression involves an NA value, a YES or NO result is returned when that makes sense. For example, if you test whether an NA value is equal to a non-NA value, then the result is NO. However, if 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 NA.

The following table shows the results of Boolean expressions involving NA values, which yield non-NA values.

Expression Result





NA EQ non-NA


NA NE non-NA






Controlling Errors When Comparing Numeric Data


Oracle Corporation 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. If speed of calculation is important, then you will probably want to use the ABS rather than the ROUND function.

Controlling Errors Due to Numerical Precision

Suppose expense is a decimal variable whose value is set by a calculation. If the result of the calculation is 100.000001 and the number of decimal places is two, then the value will appear in output as 100.00. However, the output of the following command returns NO.

SHOW expense EQ 100.00


Controlling Errors When Comparing Floating Point Numbers

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 command returns a NO instead of the expected YES.

SHOW .1 + .2 EQ .3

When you deal with decimal data, you should not code direct comparisons such as the one above. Instead, you can use the ABS or the ROUND function to allow a tolerance for approximate equality. For example, either of the following two commands will produce the desired YES.


Controlling Errors When Comparing Different Numeric Data Types



sdvar = 1.3
SHOW sdvar EQ 1.3


There are several ways to avoid this type of comparison failure:

Comparing Dimension Values

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 allows you to specify commands like the following command.


Commands are interpreted such as these using the process below.

  1. The text literal 'SEATTLE' is converted to its position in the district default status list of the dimension.
  3. 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 NO for SEATTLE itself.
    REPORT 22 WIDTH district LT 'SEATTLE'
    BOSTON                            YES
    ATLANTA                           YES
    CHICAGO                           YES
    DALLAS                            YES
    DENVER                            YES
    SEATTLE                            NO


quota = IF month LE 'JUN02' THEN 100 ELSE LAG(quota, 1, month)* 1.15


See Also:

"Conditional Expressions" for information about IF. . .THEN. . .ELSE syntax.

Comparing Dates



Comparing Text Data


EXTCHARS(, 1, 1) GT 'M'


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 '100,999.00' because '2', the second character in the first text literal, is greater than '0', the second character in the second text literal.

Suppose name.label is an ID variable whose value is '3-Person' and name.desc is a TEXT variable whose value is '3-Person Tents'.

The result of the following SHOW command will be NO.

SHOW name.desc EQ name.label

The result of the following commands will be YES.

name.desc = '3-Person'
SHOW name.desc EQ name.label

Comparing a Text Value to a Text Pattern



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 'DATA' and 'ERRATA' will return YES when LIKE is used to compare them with the pattern %AT_.

The results of expressions using the LIKE operator are affected by the settings of the LIKECASE and LIKENL options. See the entries in the OLAP DML Reference for these options, both for examples of their effect on the LIKE operator and for general examples of the use of the LIKE operator.

No negation operator exists for LIKE. To accomplish negation, you must negate the entire expression. For example, the result of the following command is NO.


Comparing Text Literals to Relations


region.district EQ 'WEST'

Copyright © 2001, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index