Back to Contents

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 Operators section.
• 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.

Notes:

1. Boolean expressions are a combination of Logical Operators and Boolean comparisons based on Comparison Operators.
2. Boolean expressions are based on the INTEGER data type for evaluation.
3. Any integer value different from zero is defined as true, while zero is defined as false.
4. Use an INTEGER variable to store the result of a Boolean expression.
5. If an expression that returns NULL is the operand of the IS NULL operator, the value of the Boolean expression is TRUE.
6. 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.
7. 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
8. 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.

Example:

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

Warnings:

1. 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).
2. The syntax of Boolean expressions in BDL is not the same as Boolean conditions in SQL statements.
3. 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.

Integer Expressions

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

Notes:

1. The data type of the expression result can be SMALLINT or INTEGER.
2. 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
3. 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.

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

Warnings:

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

Number Expressions

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

Notes:

1. The data type of the expression result can be SMALLINT, INTEGER, DECIMAL, SMALLFLOAT or FLOAT.
2. 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
3. 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.

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

Warnings:

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

String Expressions

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

Notes:

1. The data type of the expression result is STRING.
2. 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
3. Other operands whose values are not character string data types are converted to strings according to the data conversion rules.

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

Warnings:

1. If an element of an integer expression is NULL, the expression is evaluated to NULL.
2. An empty string ("") is equivalent to NULL.

Date Expressions

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

Notes:

1. The data type of the expression result is a DATE value.
2. 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
3. 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.

Example:

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

Warnings:

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

Datetime Expressions

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

Notes:

1. The data type of the expression result is a DATETIME value.
2. 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
3. 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.

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

Warnings:

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

Interval Expressions

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

Notes:

1. The data type of the expression result is a INTERVAL value.
2. 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
3. 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.

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

Warnings:

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