Oracle9i OLAP Services Developer's Guide to the OLAP DMLRelease 1 (9.0.1)Part Number A86720-01

Working with Expressions, 11 of 14

Boolean Expressions

What is a Boolean expression?

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.

A Boolean expression can consist of Boolean data, such as the following:

• Boolean values (YES and NO, and their synonyms ON and OFF and TRUE and FALSE)

• Boolean variables or formulas

• Functions that yield Boolean results

• Boolean values calculated by comparison operators

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, but you can change the output by setting the YESSPELL and NOSPELL options.

Boolean operators

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.

Operator

Operation

Example

Priority

NOT

Returns opposite of Boolean expression

`NOT(YES) = NO`

1st

EQ

Equal to

`4 EQ 4 = YES`

2nd

NE

Not equal to

`4 NE 4 = NO`

2nd

GT

Greater than

`5 GT 7 = NO`

2nd

LT

Less than

`5 LT 7 = YES`

2nd

GE

Greater than or equal to

`8 GE 8 = YES`

2nd

LE

Less than or equal to

`8 LE 9 = YES`

2nd

IN

Is a date in a time period?

`'1JAN97' IN WI.97 = YES`

2nd

LIKE

Does a text value match a specified text pattern?

`'EXPRESS' LIKE '%PRE%' = YES`

2nd

AND

Both expressions are true

`8 GE 8 AND 5 LT 7 = YES`

3rd

OR

Either expression is true

`8 GE 8 OR 5 GT 7 = YES`

4th

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

Creating Boolean expressions

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 AND and OR logical operators. Each expression that is connected by AND or 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 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.

Use the 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: Using Boolean comparisons

The following example shows a report that displays whether sales in Boston for each product were greater than a literal amount.

```limit month to first 2
limit district to 'BOSTON'
fetch sales gt 75000 labeled
```

This FETCH command returns the following data.

```(MONTH JAN95, PRODUCT TENTS, DISTRICT BOSTON): FALSE
(MONTH FEB95, PRODUCT TENTS, DISTRICT BOSTON): FALSE
(MONTH JAN95, PRODUCT CANOES, DISTRICT BOSTON): FALSE
(MONTH FEB95, PRODUCT CANOES, DISTRICT BOSTON): TRUE
(MONTH JAN95, PRODUCT RACQUETS, DISTRICT BOSTON): FALSE
(MONTH FEB95, PRODUCT RACQUETS, DISTRICT BOSTON): FALSE
(MONTH JAN95, PRODUCT SPORTSWEAR, DISTRICT BOSTON): FALSE
(MONTH FEB95, PRODUCT SPORTSWEAR, DISTRICT BOSTON): FALSE
(MONTH JAN95, PRODUCT FOOTWEAR, DISTRICT BOSTON): TRUE
(MONTH FEB95, PRODUCT FOOTWEAR, DISTRICT BOSTON): TRUE
```

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 NA

YES

NA NE NA

NO

NA EQ non-NA

NO

NA NE non-NA

YES

NA AND NO

NO

NA OR YES

YES

Controlling errors when comparing numeric data

If you get unexpected results when comparing numeric data, then there are several possible causes to consider:

• One of the numbers you are comparing may have a small decimal part that does not show in output because of the setting of the DECIMALS option.

• You are comparing two floating point numbers and at least one number is the result of an arithmetic operation.

• You have mixed SHORTDECIMAL and DECIMAL data types in a comparison.

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.

Example: Controlling errors due to the setting of the DECIMALS option

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

You can use the ABS or the ROUND function to ignore these slight differences when making comparisons.

Example: Controlling errors when comparing floating point numbers resulting from arithmetic operations

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.

```show abs((.1 + .2) - .3) lt .00001
show round(.1 + .2) eq round(.3, .00001)
```

Example: Controlling errors when comparing SHORTDECIMAL and DECIMAL values

You cannot expect exact equality between SHORTDECIMAL and DECIMAL representations of a decimal number with a fractional component, because the DECIMAL data type has 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 a comparison of the SHORTDECIMAL number to a fractional decimal number is likely to return NO.

```define sdvar shortdecimal
sdvar = 1.3
show sdvar eq 1.3
```

What happens in this situation is that the literal is automatically typed as DECIMAL and converts the SHORTDECIMAL variable SDVAR to DECIMAL, which extends the decimal places with zeros. A bit-by-bit comparison is then performed, which fails.

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

• Do not mix the SHORTDECIMAL and DECIMAL types in comparisons. To avoid mixing these two data types, you should generally define variables with fractional decimal components as DECIMAL rather than SHORTDECIMAL.

• Use the ABS or ROUND function to allow for approximate equality. The following commands both produce YES.

```show abs(sdvar - 1.3) lt .00001
show round(sdvar, .00001) eq round(.3, .00001)
```

Comparing dimension values

Values are not compared in the same dimension based on the textual value. Instead, it compares the positions of the values in the default status of the dimension. This allows you to specify commands like the following command.

```fetch district lt 'SEATTLE' labeled
```

Commands are interpreted such as these using the process below.

1. The text literal `'SEATTLE'` is converted to its position in the DISTRICT dimension's default status list.

2. That position is compared to the position of all other values in the DISTRICT dimension.

3. As shown by the following report, the value YES is returned for districts that are positioned before SEATTLE in the DISTRICT dimension's default status list and it returns NO for SEATTLE itself. It will also return NO for districts added after SEATTLE.

`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 will be in chronological order.

```quota = if month le 'JUN95' then 100 else lag(quota, 1, month)* 1.15
```

However, if you compare values from different dimensions, such as in the expression `REGION` `LT` `DISTRICT`, then the only common denominator is TEXT, and text values are compared, not dimension positions.

Comparing dates

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 "Numeric Expressions". 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.

```if bill.date + 60 le today
```

Dates also have a numeric value. For example, January 1, 1000, has a value of `-328717`; December 31, 9999, has a value of `2958464`; and January 1, 1900, has a value of `1`. Thus, each date in this range has a corresponding numeric value. For example, January 2, 1000, has a value of `-328716` and January 2, 1900, has a value of `2`. You can use the CONVERT function to change dates to integers and integers to dates and compare them.

Comparing dates and times

There are several types of time dimensions whose values are time periods. Each time period covers a range of dates, from one day to one year. If a date falls between the starting and ending dates of that time period, then is equal to a time period.

You can also compare one time dimension value to another time dimension value, when the two have the same length and phase. However, you cannot compare two time dimension values with the standard Boolean operators when they have different period lengths or phases. To make such a comparison, you can convert a time dimension value to a date (its value becomes the last day in the time period) and then compare it to another time period.

Correct: ` show day lt convert(week date)`
Incorrect:` show day lt week`

The Boolean operator IN is designed for comparing time periods. It evaluates whether a date or time period is contained in another time period. The comparison is based on the ending dates of the time periods. If the ending date of the first period is in the second period, then the result is YES. It does not matter whether the first period is actually shorter or longer than the second.

Comparing text data

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 TEXT and 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 ASCII value of the characters.

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.

Example: Comparing text data

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

The Boolean operator LIKE is designed for comparing a text value to a text pattern. A text value is like another text value or pattern when corresponding characters match.

Besides literal matching, LIKE lets you use wildcard characters to match more than one character in a string:

• 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_` would match any text that contained 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.

```show not ('EXPRESS' like 'EX%')
```

Comparing text literals to relations

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, REGION.DISTRICT holds values of REGION, so you can do the following comparison.

```region.district eq 'WEST'
```