Expressions

Summary:

Definition
Boolean Expressions
Integer Expressions
Number Expressions
String Expressions
Date Expressions
Datetime Expressions
Interval Expressions

See also: Variables, Data Types, Literals, Constants.

Definition

What is an Expression?

An Expression is a sequence of operands, operators, and parentheses that the runtime system can evaluate as a single value.

Expressions can include the following components:

Operators, as described in the Basic Syntax Elements.
Parentheses, to overwrite precedence of operators.
Operands, including the following:
- Variables
- Constants
- Functions (returning a single value)
- Literal values
- Other expressions

Differences Between BDL and SQL Expressions

Expressions in SQL statements are evaluated by the database server, not by the runtime system. The set of operators that can appear in SQL expressions resembles the set of BDL operators, but they are not identical. A program can include SQL operators, but these are restricted to SQL statements. Similarly, most SQL operands are not valid in BDL expressions. The SQL identifiers of databases, tables, or columns can appear in a LIKE clause or field name in BDL statements, provided that these SQL identifiers comply with the naming rules of BDL. Here are some examples of SQL operands and operators that cannot appear in other BDL expressions:

SQL identifiers, such as column names
The SQL keywords USER and ROWID
Built-in or aggregate SQL functions that are not part of BDL
The BETWEEN and IN operators
The EXISTS, ALL, ANY, or SOME keywords of SQL expressions

Conversely, you cannot include BDL specific operators in SQL expressions, as for example:

Arithmetic operators for exponentiation (**) and modulus (MOD)
String operators ASCII, COLUMN, SPACE, SPACES, and WORDWRAP
Field operators FIELD_TOUCHED( ), GET_FLDBUF( ), and INFIELD( )
The report operators LINENO and PAGENO

Parentheses in BDL Expressions

You can use parentheses as you would in algebra to override the default order of precedence of operators. In mathematics, this use of parentheses represents the "associative" operator. It is, however, a convention in computer languages to regard this use of parentheses as delimiters rather than as operators. (Do not confuse this use of parentheses to specify operator precedence with the use of parentheses to enclose arguments in function calls or to delimit other lists.)

In the following example, the variable y is assigned the value of 2:

LET y = 15 MOD 3 + 2

In the next example, however, y is assigned the value of 0 because the parentheses change the sequence of operations:

LET y = 15 MOD (3 + 2)

Boolean Expressions

A Boolean expression is one that evaluates to an INTEGER value that can be TRUE, FALSE and in some cases, NULL.

Usage:

Boolean expressions are a combination of Logical Operators and Boolean comparisons based on Comparison Operators. The result type of a Boolean expression is INTEGER. Any integer value different from zero is defined as true, while zero is defined as false. You can use an INTEGER or a BOOLEAN variable to store the result of a Boolean expression:

01 MAIN
02   DEFINE b BOOLEAN
03   LET b = ( TRUE AND FALSE )
04   IF b THEN
05      DISPLAY "TRUE"
06   END IF
07 END MAIN

If an expression that returns NULL is the operand of the IS NULL operator, the value of the Boolean expression is TRUE:

01 MAIN
02   DEFINE r INTEGER
03   LET r = NULL
04   IF r IS NULL THEN
05      DISPLAY "TRUE"
06   END IF
07 END MAIN

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:

The Boolean expression evaluates to TRUE if the value is a non-zero real number or any of the following items:

Character string representing a non-zero number
Non-zero INTERVAL
Any DATE or DATETIME value
A TRUE value returned by a Boolean function like INFIELD( )
The built-in integer constant TRUE

If a Boolean expression includes an operand whose value is not an integer data type, the runtime system attempts to convert the value to an integer according to the data conversion rules.

A Boolean expression evaluates to NULL if the value is NULL and the expression does not appear in any of the following contexts:

The IS [NOT] NULL test.
Boolean Comparisons.
Any conditional statement (IF, CASE, WHILE).

The syntax of Boolean expressions in BDL is not the same as Boolean conditions in SQL statements.

Boolean expressions in CASE, IF, or WHILE statements return FALSE if any element of the comparison is NULL, except for operands of the IS NULL and the IS NOT NULL operator. See Boolean Operators for more information about individual Boolean operators and Boolean expressions.

Example:

01 MAIN
02   DEFINE r, c INTEGER
03   LET c = 4
03   LET r = ( FALSE!=FALSE ) AND ( c=2 OR c=4 )
04   IF ( r AND canReadFile("config.txt") ) THEN
05      DISPLAY "OK"
06   END IF
07 END MAIN

Integer Expressions

An Integer expression is one that evaluates to a whole number.

Usage:

The data type of the expression result can be SMALLINT or INTEGER.

The operands must be one of:

An integer literal
A variable or constant of type SMALLINT or INTEGER
A function returning a single integer value
A Boolean expression
The result of a DATE subtraction

If an integer expression includes an operand whose value is not an integer data type, the runtime system attempts to convert the value to an integer according to the data conversion rules.

If an element of an integer expression is NULL, the expression is evaluated to NULL.

Example:

01 MAIN
02   DEFINE r, c INTEGER
03   LET c = 4
04   LET r = c * ( 2 + c MOD 4 ) / getRowCount("customers")
05 END MAIN

Number Expressions

A Number expression is one that evaluates to a number data type.

Usage:

The data type of the expression result can be SMALLINT, INTEGER, DECIMAL, SMALLFLOAT or FLOAT.

The operands must be one of:

An integer literal
A decimal literal
A variable or constant of numeric data type
A function returning a single numeric value
A Boolean expression
The result of a DATE subtraction

If a number expression includes an operand whose value is not a numeric data type, the runtime system attempts to convert the value to a number according to the data conversion rules.

If an element of a number expression is NULL, the expression is evaluated to NULL.

Example:

01 MAIN
02   DEFINE r, c DECIMAL(10,2)
03   LET c = 456.22
04   LET r = c * 2 + ( c / 4.55 )
05 END MAIN

String Expressions

A String expression is one that includes at least one character string value and that evaluates to the STRING data type.

Usage:

The data type of the expression result is STRING.

At least one of the operands must be one of:

A string literal.
A variable or constant of type CHAR, VARCHAR or STRING.
A function returning a single character value

Other operands whose values are not character string data types are converted to strings according to the data conversion rules.

If an element of an integer expression is NULL, the expression is evaluated to NULL.

An empty string ("") is equivalent to NULL.

Example:

01 MAIN
02   DEFINE r, c VARCHAR(100)
03   LET c = "abcdef" 
04   LET r = c[1,3] || ": " || TODAY USING "YYYY-MM-DD" || " " || length(c)
05 END MAIN

Date Expressions

A Date expression is one that evaluates to a DATE data type.

Usage:

The data type of the expression result is a DATE value.

The operands must be one of:

A string literal that can be evaluated to a Date according to DBDATE
A variable or constant of type DATE
A function returning a single Date value
A unary + or - associated to an Integer expression representing a number of days
The TODAY constant
A CURRENT expression with YEAR TO DAY qualifiers
An EXTEND expression with YEAR TO DAY qualifiers

If a date expression includes an operand whose value is not a date data type, the runtime system attempts to convert the value to a date value according to the data conversion rules.

If an element of an integer expression is NULL, the expression is evaluated to NULL.

Example:

01 MAIN
02   DEFINE r, c DATE
03   LET c = TODAY + 4
04   LET r = ( c - 2 )
05 END MAIN

Datetime Expressions

A Datetime expression is one that evaluates to a DATETIME data type.

Usage:

The data type of the expression result is a DATETIME value.

The operands must be one of:

A datetime literal
A string literal representing a Datetime with the ISO format YYYY-MM-DD hh:mm:ss.fffff
A variable or constant of DATETIME type
A function returning a single Datetime value
A unary + or - associated to an Interval expression
A CURRENT expression
An EXTEND expression

If a datetime expression includes an operand whose value is not a datetime data type, the runtime system attempts to convert the value to a datetime value according to the data conversion rules.

If an element of an integer expression is NULL, the expression is evaluated to NULL.

Example:

01 MAIN
02   DEFINE r, c DATETIME YEAR TO SECOND
03   LET c = CURRENT YEAR TO SECOND
04   LET r = c + INTERVAL( 234-02 ) YEAR TO MONTH
05 END MAIN

Interval Expressions

An Interval expression is one that evaluates to a INTERVAL data type.

Usage:

The data type of the expression result is a INTERVAL value.

The operands must be one of:

An interval literal
A string literal representing an Interval with the ISO format YYYY-MM-DD hh:mm:ss.fffff
An integer expression using the UNITS operator
A variable or constant of INTERVAL type
A function returning a single Interval value
The result of a DATETIME subtraction

If an interval expression includes an operand whose value is not an interval data type, the runtime system attempts to convert the value to an interval value according to the data conversion rules.

If an element of an integer expression is NULL, the expression is evaluated to NULL.

Example:

01 MAIN
02   DEFINE r, c INTERVAL HOUR TO MINUTE
03   LET c = "12:45"
04   LET r = c + ( DATETIME(14:02) HOUR TO MINUTE - DATETIME(10:43) HOUR TO MINUTE )
05 END MAIN