1 Boolean_Expressions 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. 2 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