A Boolean expression is the logical representation of a
relationship between value expressions. The value of a Boolean
expression is either true or false. When dealing with relational
sources, the value of a Boolean expression is either TRUE, FALSE,
or UNKNOWN according to SQL/Services standards. The UNKNOWN
boolean value may result if either value expression in a Boolean
expression is null.
You can use Boolean expressions in the following DEC DATATRIEVE
clauses and statements:
o WITH clause in a record selection expression
o WITH clause in a SELECT statement
o IF clause of an IF-THEN-ELSE statement
o IF clause of an IF-THEN-ELSE value expression
o CHOICE statement
o CHOICE value expression
o VALID IF clause in a record definition
o WHILE statement
The DEC DATATRIEVE CHOICE and IF-THEN-ELSE value expressions, and
the CHOICE and IF-THEN-ELSE statements can contain an additional
ELSE clause to support the UNKNOWN boolean value.
Boolean expressions consist of value expressions, relational
operators, and Boolean operators. Relational operators control
the comparison of value expressions. Boolean operators enable you
to join two or more Boolean expressions and to reverse the value
of a Boolean expression. All Boolean expressions contain value
expressions and relational operators, and some contain Boolean
operators.
1 – Relational Operators
Relational operators compare value expressions, check whether a
code string is contained in a table, and check whether a record
stream is empty or not. Most Boolean expressions contain a field
name, a relational operator, and a value expression.
The relational operators are:
o EQUAL
o NOT EQUAL
o CONTAINING
o NOT CONTAINING
o STARTING WITH
o GREATER THAN
o GREATER EQUAL
o LESS THAN
o LESS EQUAL
o BETWEEN
o MISSING
o NOT MISSING
o IN table-name
o NOT IN table-name
o ANY
o NOT ANY
o AFTER
o BEFORE
The STARTING WITH relational operator is designed to work on text
strings and can give inconsistent results when used with numeric
fields.
When handling character string literals, DEC DATATRIEVE considers
lowercase letters to have a greater value than uppercase letters.
Within each case, however, DEC DATATRIEVE sorts the letters
alphabetically, and considers the letters near the beginning of
the alphabet to be smaller than those near the end. Consequently,
"ALBIN" is less than "AMERICAN."
The order and value associated with alphanumeric characters is
determined by the ASCII collating sequence. Lowercase letters
have a higher ASCII value than uppercase letters. See the OpenVMS
documentation for more information on the DEC Multinational
Character Set, which includes the ASCII character set.
In Boolean expressions using the relational operator CONTAINING,
the comparison of the value expression and the field value is
case insensitive. The comparison is case insensitive regardless
of whether you enclose a character string literal within
quotation marks.
The following examples show how to use relational operators that
compare field values to value expressions:
DTR> FIND YACHTS WITH BEAM EQ 9,10,14
[50 records found]
DTR> PRINT CURRENT WITH RIG NE "SLOOP"
LENGTH
OVER
MANUFACTURER MODEL RIG ALL WEIGHT BEAM PRICE
EASTWARD HO MS 24 7,000 09 $15,900
FISHER 30 KETCH 30 14,500 09
GRAMPIAN 34 KETCH 33 12,000 10 $29,675
The last PRINT statement can be written with the value expression
preceding the field name. DEC DATATRIEVE displays the same
records:
DTR> PRINT CURRENT WITH "SLOOP" NE RIG
LENGTH
OVER
MANUFACTURER MODEL RIG ALL WEIGHT BEAM PRICE
EASTWARD HO MS 24 7,000 09 $15,900
FISHER 30 KETCH 30 14,500 09
GRAMPIAN 34 KETCH 33 12,000 10 $29,675
2 – Boolean Operators
There are four Boolean operators: AND, OR, NOT, and BUT. With
AND, OR, and BUT, you can join two or more Boolean expressions to
form a single Boolean expression. NOT allows you to reverse the
value of a Boolean expression.
The AND and BUT operators perform the same function. If you link
Boolean expressions with either AND or BUT, the resulting Boolean
expression is true only if all the Boolean operators linked with
either AND or BUT are true.
If you link Boolean expressions with OR, the resulting Boolean
expression is true if any one of the Boolean linked with OR are
true.
If you precede a Boolean expression with NOT, the resulting
Boolean expression is true if the Boolean expression following
NOT is false.
The following examples show the use of Boolean operators:
DTR> READY YACHTS
DTR> PRINT YACHTS WITH BUILDER = "PEARSON" AND LOA = 30
LENGTH
OVER
MANUFACTURER MODEL RIG ALL WEIGHT BEAM PRICE
PEARSON 30 SLOOP 30 8,320 09
DTR> FIND YACHTS WITH BUILDER = "PEARSON" OR LOA = 30
[21 records found]
DTR> READY FAMILIES
DTR> PRINT FAMILIES WITH FATHER NOT EQ "JIM" AND
[Looking for Boolean expression]
CON> ANY KIDS WITH AGE GT 31
NUMBER KID
FATHER MOTHER KIDS NAME AGE
JEROME RUTH 4 ERIC 32
CISSY 24
NANCY 22
MICHAEL 20
HAROLD SARAH 3 CHARLIE 31
HAROLD 35
SARAH 27
DTR>
You can also use parentheses to group Boolean expressions. DEC
DATATRIEVE evaluates Boolean expressions in parentheses before
evaluating other Boolean expressions. If a Boolean expression
contains Boolean operators as well as parentheses, DEC DATATRIEVE
evaluates the Boolean expression in the following order:
1. Expressions enclosed in parentheses
2. Expressions preceded by NOT
3. Expressions combined with AND or BUT
4. Expressions combined with OR