# Boolean expressions

This section covers boolean expression evaluation rules.

Boolean expressions are a combination of `AND`

, `OR`

, `NOT`

boolean operators, as well as comparison
operators such as `==`

, `>=`

or `!=`

.

The result of a boolean expression is a `TRUE`

or `FALSE`

boolean
value, but it can also be `NULL`

if one of the operands is `NULL`

.

A boolean value is typically used in an `IF`

block, `WHILE`

block, or the `WHEN`

clause in a `CASE`

block.

`expr`AND`expr``expr`OR`expr``NOT`

`expr`

The (`expr`) operands of boolean expressions are boolean values.

*Boolean conditions*in SQL, as SQL statements are executed by the database engine.

`NULL`

, the result is `NULL`

. If one of the
operands is `NULL`

and the other is non-null, the result depends on the operator type
and the non-null value. For more details, see `AND`

, `OR`

, `NOT`

operators.The following example shows a simple boolean expression using the `AND`

operator:

```
IF a AND b THEN
DISPLAY "Both a and b are TRUE"
END IF
```

```
IF (a == b) AND (a == c) THEN
DISPLAY "a, b and c are equal"
END IF
```

`NOT`

operator will negate a boolean
expression:```
IF NOT a THEN
DISPLAY "a is FALSE"
END IF
```

Use a `BOOLEAN`

variable to store
the result of a boolean expression:

```
MAIN
DEFINE b BOOLEAN
LET b = ( "a" == "b" ) -- result is FALSE
END MAIN
```

```
DEFINE var STRING, cnt INTEGER
IF var AND cnt>0 THEN
...
END IF
```

`IF LENGTH(var)>0 AND cnt>0 THEN`

`IF var IS NOT NULL AND cnt>0 THEN`

If the operand is not of type boolean, it has to be converted to a boolean. If a conversion is required:

- Any numeric value evaluates to
`FALSE`

, if and only if the value is 0. - Any character string value
(
`STRING`

,`CHAR`

,`VARCHAR`

) follows the next rules:- If the string starts with a digit, then this conversion evaluates to
`FALSE`

, if and only if the string to integer conversion returns 0. - If the string does not start with a digit, then this conversion evaluates to
`FALSE`

if and only if the string has a length of 0.

Note: Consider using the expression`(LENGTH(`

or`string`)>0)

, to check that a string contains characters, or convert the string to a numeric variable and then test the numeric value.`string`IS NOT NULL - If the string starts with a digit, then this conversion evaluates to
`DATE`

values can be converted to integers.`MDY(12,31,1899) = 0`

and evaluates to`FALSE`

. Any other date value is different from zero and evaluates to`TRUE`

.- Any other data type produces a conversion error and raises the runtime error -1260.

Below a more complex example of boolean expressions:

```
MAIN
DEFINE r BOOLEAN, c INTEGER
LET c = 4
LET r = ( c!=5 ) AND ( c==2 OR c==4 )
IF ( r AND canReadFile("config.txt") ) THEN
DISPLAY "OK"
END IF
END MAIN
```

If an expression that returns `NULL`

is the operand of the `IS NULL`

operator, the value of the
boolean expression is `TRUE`

:

```
MAIN
DEFINE r INTEGER
LET r = NULL
IF r IS NULL THEN
DISPLAY "TRUE"
END IF
END MAIN
```

Boolean expressions in `CASE`

, `IF`

, or `WHILE`

statements evaluate to `FALSE`

, if any element of the comparison is
`NULL`

, except for operands of the `IS NULL`

and the ```
IS NOT
NULL
```

operator.

If you include a boolean expression in a context where the runtime system expects a number, the
expression is evaluated, and is then converted to an integer by the rules `TRUE=1`

and `FALSE=0`

.

```
MAIN
DEFINE r INTEGER
LET c = 4
LET r = 4 + (1==0) -- result is 4.
END MAIN
```