Back to Contents

Operators

Summary:

• Definition
• Operator List
• Order of Precedence List
• General Warnings

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

Definition

The operators listed in this section can appear in Expressions. Expressions with several operators are evaluated according to their precedence, from highest to lowest, as indicated in the left-most (P) column. Use parentheses to instruct the runtime system to perform the expression evaluation in a different way than the default order of precedence.

List of Operators

• Parentheses ( ( ) )
• Associative Operators
  • Membership ( . )
• Assignment Operators
  • Assignment ( := )
• Comparison Operators
  • Is Null (IS NULL)
  • Like (LIKE)
  • Matches (MATCHES)
  • Equal (==)
  • Different (!= or <>)
  • Lower (<)
  • Lower or Equal (<=)
  • Greater (>)
  • Greater or Equal (>=)
• Logical Operators
  • Not (NOT)
  • And (AND)
  • Or (OR)
• State Operators
  • SQL State Code (SQLSTATE)
  • SQL Error Message (SQLERRMESSAGE)
  Numeric Operators
  • Addition (+)
  • Subtraction (-)
  • Multiplication (*)
  • Division (/)
  • Exponentiation (**)
  • Modulus (MOD)
• String Operators
  • ASCII Char (ASCII)
  • Concatenate (||)
  • Append (,)
  • Substring ([x,y])
  • Formatting (USING)
  • Clipped (CLIPPED)
  • Spaces (SPACES)
  • Localized String (LSTR)
  • Replace (SFMT)
• Datetime Operators
  • Current Datetime (CURRENT x TO y)
  • Datetime Extensions (EXTEND( d, x TO y))
  • Date Conversion (DATE)
  • Time Conversion (TIME)
  • Current Date (TODAY)
  • Year of Date (YEAR(d))
  • Month of Date (MONTH(d))
  • Day of Date (DAY(d))
  • Weekday of Date (WEEKDAY(d))
  • Building a Date (MDY(m,d,y))
  • Interval Unit (UNITS)
• Form Field Operators
  • Field Buffer (GET_FLDBUF)
  • Current Field (INFIELD)
  • Field Modification (FIELD_TOUCHED)

Order of Precedence List

The following list describes the precedence order of operators. The P column defines the precedence, from highest(14) to lowest(1). The A column defines the direction of associativity (L=Left, R=Right, N=None). Some operators have the same precedence.

General Warnings

Pure SQL Operators

The following operators are related to SQL syntax and not part of the language:

• BETWEEN expr AND expr
• IN ( expr [ , ..' ] )

Report Routine Operators

The following operators are only available in the FORMAT section of report routines:

• PAGENO
• WORDWRAP

See the Report Definition for more details.

PARENTHESES

Purpose:

Parentheses are typically used to associate a set of values or expressions to override the default order of precedence.

Syntax:

Notes:

1. Typically used to change the precedence of operators.

Example:

01 MAIN
02   DEFINE n INTEGER
03   LET n = ( ( 3 + 2 ) * 2 )
04   IF n=10 AND ( n<=0 OR n>=20 ) THEN
05     DISPLAY "OK"
06   END IF
07 END MAIN

MEMBERSHIP

Purpose:

The period membership operator specifies that its right-hand operand is a member of the set whose name is its left-hand operand.

Syntax:

Notes:

1. Typically used for record members.

Example:

01 MAIN
02   DEFINE rec RECORD
03      n INTEGER,
04      c CHAR(10)
05   END RECORD
06   LET rec.n = 12345
06   LET rec.c = "abcdef"
07 END MAIN

ASSIGNMENT

Purpose:

The := assignment operator sets a value to the left-hand operand, which must be a variable.

Syntax:

Notes:

1. Do not confuse with the LET instruction.
2. The left-hand operand must be a variable.
3. The assignment operator can be used in expressions.
4. The assignment operator has the lowest precedence.

Example:

01 MAIN
02   DEFINE var1, var2 INTEGER
03   -- 1. Evaluates 2*5
04   -- 2. Sets var2 to 10
05   -- 3. Then affects var1 with 10
06   LET var1 = var2:=2*5
07 END MAIN

IS NULL

Purpose:

The IS NULL operator is provided to test whether a value is NULL.

Syntax:

Notes:

1. Applies to most Data Types, except complex types like BYTE and TEXT.

Example:

01 MAIN
02   DEFINE n INTEGER
03   LET n = 257
04   IF n IS NULL THEN
05      DISPLAY "Something is wrong here"
06   END IF
07 END MAIN

EQUAL

Purpose:

The == operator evaluates if two expressions or two records are identical.

Syntax 1: Expression comparison

Syntax 2: Record comparison

Notes:

1. Syntax 1 applies to most Data Types, except complex types like BYTE and TEXT.
2. expr can be any expression supported by the language.
3. Syntax 2 allows you to compare all members of records having the same structure.
4. record1 and record2 are records with the same structure.
5. A single equal = can be used as an alias for this operator. 

Usage:

When comparing expressions using the first syntax, the result of the operator is FALSE when one of the operands is NULL.

When comparing two records using the second syntax, the runtime system compares all corresponding members of the records. If a pair of members are different, the result of the operator is FALSE. When two corresponding members are NULL, they are considered as equal. 

Example:

01 MAIN
02   IF 256==257 THEN
03      DISPLAY "Something is wrong here"
04   END IF
05 END MAIN

DIFFERENT

Purpose:

The != operator evaluates if two expressions or two records are different.

Syntax 1: Expression comparison

Syntax 2: Record comparison

Notes:

1. Syntax 1 applies to most Data Types, except complex types like BYTE and TEXT.
2. expr can be any expression supported by the language.
3. Syntax 2 allows to compare all members of records having the same structure.
4. record1 and record2 are records with the same structure.
5. An alias exists for this operator: <>

Usage:

When comparing expressions with the first syntax, the result of the operator is FALSE when one of the operands is NULL.

When comparing two records with the second syntax, the runtime system compares all corresponding members of the records. If one pair of members are different, the result of the operator is TRUE. When two corresponding members are NULL, they are considered as equal. 

Example:

01 MAIN
02   IF 256 != 257 THEN
03      DISPLAY "This seems to be true"
04   END IF
05 END MAIN

LOWER

Purpose:

The < operator is provided to test whether a value or expression is lower than another .

Syntax:

Warnings:

1. Applies to most Data Types, except complex types like BYTE and TEXT.

LOWER OR EQUAL

Purpose:

The <= operator is provided to test whether a value or expression is lower than or equal to another .

Syntax:

Warnings:

1. Applies to most Data Types, except complex types like BYTE and TEXT.

GREATER

Purpose:

The > operator is provided to test whether a value or expression is greater than another .

Syntax:

Warnings:

1. Applies to most Data Types, except complex types like BYTE and TEXT.

GREATER OR EQUAL

Purpose:

The >= operator is provided to test whether a value or expression is greater than or equal to another .

Syntax:

Warnings:

1. Applies to most Data Types, except complex types like BYTE and TEXT.

NOT

Purpose:

The NOT operator is a typical logical NOT used to invert a Boolean expression.

Syntax:

Example:

01 MAIN
02   IF NOT ( 256 != 257 ) THEN
03      DISPLAY "Something is wrong here"
04   END IF
05 END MAIN

AND

Purpose:

The AND operator is the logical intersection operator.

Syntax:

Example:

01 MAIN
02   IF 256!=257 AND 256=257 THEN
03      DISPLAY "Sure?"
04   END IF
05 END MAIN

OR

Purpose:

The OR operator is the logical union operator.

Syntax:

Example:

01 MAIN
02   IF TRUE OR FALSE THEN
03      DISPLAY "Must be true!"
04   END IF
05 END MAIN

SQLSTATE

Purpose:

The SQLSTATE operator returns the ANSI SQLSTATE code if an SQL error occurred.

Syntax:

Warnings:

1. The SQLSTATE error code is a standard ANSI specification, but not all database engines support this feature. Check the database server documentation for more details.

Example:

01 MAIN
02   DATABASE stores
03   WHENEVER ERROR CONTINUE
04   SELECT foo FROM bar
05   DISPLAY SQLSTATE
06 END MAIN

SQLERRMESSAGE

Purpose:

The SQLERRMESSAGE operator returns the error message if an SQL error occurred.

Syntax:

Example:

01 MAIN
02   DATABASE stores
03   WHENEVER ERROR CONTINUE
04   SELECT foo FROM bar
05   DISPLAY SQLERRMESSAGE
06 END MAIN

ASCII

Purpose:

The ASCII operator returns the character corresponding to the ASCII code passed as a parameter.

Syntax:

Notes:

1. intexpr is an integer expression..
2. Typically used to generate a non-printable character like NewLine or Escape.
3. In a default (U.S. English) locale, this is the logical inverse of the ORD() built-in function.

Warnings:

1. This is not a function, but a real operator (it can, for example, be used as a function parameter).

Tips:

1. Often used with parentheses ( ASCII(n) ), but these are not needed.

Example:

01 MAIN
02   DISPLAY ASCII 65, ASCII 66, ASCII 7
03 END MAIN

LIKE

Purpose:

The LIKE operator returns TRUE if a string matches a given mask.

Syntax:

Notes:

1. mask can be any combination of characters, including % and _ symbols.
2. The % symbols character matches any string of zero or more characters.
3. The _ symbols character matches any single character.
4. ec defines the escape symbol to use symbols as normal characters; default is backslash.

Warnings:

1. Do not confuse with the LIKE clause of the DEFINE instruction.
2. LIKE operators used in SQL statements are interpreted by the database server. This may have a different behavior than the LIKE operator of the language.

Example:

01 MAIN
02   IF "abcdef" LIKE "a%e_" THEN
03      DISPLAY "yes"
04   END IF
05 END MAIN

MATCHES

Purpose:

The MATCHES operator returns TRUE if a string matches a given mask.

Syntax:

Notes:

1. mask can be any combination of characters, including *, ? and [] symbols.
2. The * symbols character matches any string of zero or more characters.
3. The ? symbols character matches any single character.
4. The [ ] brackets match any enclosed character. A hyphen ( - ) between characters means a range of characters. An initial caret ( ^ ) matches any character that is not listed.
5. ec defines the escape symbol to use symbols as normal characters; default is backslash.

Example:

01 MAIN
02   IF "abcdef" NOT MATCHES "b*[a-z]" THEN
03      DISPLAY "yes"
04   END IF
05 END MAIN

CONCATENATE

Purpose:

The || operator is the concatenation operator that produces a string expression.

Syntax:

Notes:

1. expr can be a character, numeric or date time expression.
2. This operator has a high precedence; it can be used in parameters for function calls.
3. The precedence of this operator is higher than LIKE and MATCHES, but less than arithmetic operators. For example, a || b + c is equivalent to (a||(b+c)).

Warnings:

1. If any of the members of a concatenation expression is NULL, the result string will be NULL.

Example:

01 MAIN
02   DISPLAY "Length: " || length( "ab" || "cdef" )
03 END MAIN

APPEND

Purpose:

The , operator appends a value to a string.

Syntax:

Notes:

1. Can only be used in LET and DISPLAY instructions.
2. In earlier versions this was the only way to concatenate strings; use the || operator instead.

Example:

01 MAIN
02   DISPLAY "Today:", TODAY, " and a number: ", 12345.67
03 END MAIN

SUBSTRING

Purpose:

The [] operator is provided to extract a sub-string from a character variable.

Syntax:

Notes:

1. start defines the position of the first character of the sub-string to be extracted.
2. end defines the position of the last character of the sub-string to be extracted.
3. If end is not specified, only one character is extracted.

Warnings:

1. Sub-strings expressions in SQL statements are interpreted by the database server. This may have a different behavior than the sub-string operator of the language.

Example:

01 MAIN
02   DEFINE s CHAR(10)
03   LET s = "abcdef"
04   DISPLAY s[3,4]
05 END MAIN

USING

Purpose:

The USING operator converts datetime and numeric values into a string with a formatting mask.

Syntax:

Notes:

1. format defines the formatting mask to be used; see below for more details.

Warnings:

1. The formatting characters of USING are not identical to those that you can specify in the format strings of FORMAT and PICTURE form field attributes.

Formatting symbols for numbers:

Formatting symbols for dates:

Example:

01 MAIN
02   DEFINE d DECIMAL(12,2)
03   LET d = -12345678.91
04   DISPLAY d USING "$-##,###,##&.&&"
05   DISPLAY TODAY USING "yyyy-mm-dd"
06 END MAIN

CLIPPED

Purpose:

The CLIPPED operator removes trailing blanks of a string expression.

Syntax:

Example:

01 MAIN
02    DISPLAY "Some text   " CLIPPED
03 END MAIN

SPACES

Purpose:

The SPACES operator returns a character string with blanks.

Syntax:

Warnings:

1. intexpr is an integer expression.
2. "SPACE" is an alias for this operator.

Example:

01 MAIN
02    DISPLAY 20 SPACES || "xxx"
03 END MAIN

LSTR

Purpose:

The LSTR operator returns a Localized String corresponding to the identifier passed as parameter.

Syntax:

Warnings:

1. strexpr is a string expression.

Example:

01 MAIN
02    DISPLAY LSTR ("str"||123)  -- loads string 'str123'
03 END MAIN

SFMT

Purpose:

The SFMT operator returns string after replacing the parameter.

Syntax:

Warnings:

1. strexpr is a string expression.
2. param is any valid expression used to replace parameter place holders (%n).

Usage:

The SFMT() operator can be used with parameters that will be automatically set in the string at the position defined by parameter place holders. The parameters used with the SFMT() operator can be any valid expression. Numeric and date/time expressions are evaluated to strings according to the current format settings (DBDATE, DBMONEY).

A place holder is a special marker in the string that is defined by the percent character followed by the parameter number. For example, %4 represents the parameter #4. You are allowed to use the same parameter place holder several times in the string. If you want to use the percent sign in the string, you must escape it with %%.

Example:

01 MAIN
02   DEFINE n INTEGER
03   LET n = 234
04   DISPLAY SFMT("Order #%1 has been %2.",n,"deleted")
05 END MAIN

ADDITION

Purpose:

The + operator adds a number to another.

Syntax:

Example:

01 MAIN
02  DISPLAY 100 + 200
03 END MAIN

SUBTRACTION

Purpose:

The - operator subtracts a number from another.

Syntax:

Example:

01 MAIN
02  DISPLAY 100 - 200
03 END MAIN

MULTIPLICATION

Purpose:

The * operator multiplies a number with another.

Syntax:

Example:

01 MAIN
02  DISPLAY 100 * 200
03 END MAIN

DIVISION

Purpose:

The / operator divides a number by another.

Syntax:

Example:

01 MAIN
02  DISPLAY 100 / 200
03 END MAIN

EXPONENTIATION

Purpose:

The ** operator returns a value calculated by raising the left-hand operand to a power corresponding to the integer part of the right-hand operand.

Syntax:

Example:

01 MAIN
02  DISPLAY 2 ** 8
03 END MAIN

MODULUS

Purpose:

The MOD operator returns the integer remainder from the division of the integer part of two numbers.

Syntax:

Example:

01 MAIN
02   DISPLAY 256 MOD 16
03   DISPLAY 26.51 MOD 2.7
04 END MAIN

CURRENT

Purpose:

The CURRENT operator returns the current date and time according to the qualifier.

Syntax:

Notes:

1. qual1, qual2 and scale define the date time qualifier; see the DATETIME data type for more details.

Example:

01 MAIN
02   DISPLAY CURRENT YEAR TO FRACTION(4)
03   DISPLAY CURRENT HOUR TO SECOND
04 END MAIN

EXTEND

Purpose:

The EXTEND operator adjusts a date time value according to the qualifier.

Syntax:

Notes:

1. This operator is used to convert a date time expression to a DATETIME value with a different precision.
2. dtexpr is a date or datetime expression. If it is a character string, it must consist of valid and unambiguous time-unit values and separators, but with these restrictions:
   • It cannot be a character string in date format, such as "12/12/99".
   • It cannot be an ambiguous numeric datetime value, such as "05:06" or "05".
   • It cannot be a time expression that returns an INTERVAL value.
3. qual1, qual2 and scale define the date time qualifier; see the DATETIME data type for more details.
4. The default qualifier is: YEAR TO DAY.

Example:

01 MAIN
02   DISPLAY EXTEND ( TODAY, YEAR TO FRACTION(4) )
03 END MAIN

DATE

Purpose:

The DATE operator converts a character expression, an integer or a datetime to a date value.

Syntax:

Notes:

1. dtexpr is a character string, an integer or a datetime expression.
2. This operator is used to convert a character string, an integer or a date time value to a DATE value.
3. When dtexpr is a character string expression, it must properly formatted according to datetime format settings like DBDATE.
4. If dtexpr is an integer expression, it is used as the number of days since December 31, 1899.
5. If you supply no operand, it returns a character representation of the current date in the format "weekday month day year".

Example:

01 MAIN
02   DISPLAY DATE ( 34000 )
03   DISPLAY DATE ( "12/04/1978" )
04   DISPLAY DATE ( CURRENT )
05 END MAIN

TIME

Purpose:

The TIME operator converts the time-of-day portion of its datetime operand to a character string.

Syntax:

Notes:

1. dtexpr is a datetime expression.
2. This operator converts a date time expression to a character string representing the time-of-day part of its operand.
3. The format of the returned string is always "hh:mi:ss".
4. If you supply no operand, it returns a character representation of the current time.

Example:

01 MAIN
02   DISPLAY TIME ( CURRENT )
03 END MAIN

TODAY

Purpose:

The TODAY operator returns the current calendar date.

Syntax:

Notes:

1. Reads current system clock and returns a DATE value that represents the current calendar date.

Tips:

1. See also the CURRENT operator that returns current date and time.

Example:

01 MAIN
02   DISPLAY TODAY
03 END MAIN

YEAR

Purpose:

The YEAR operator extracts the year of a date time expression.

Syntax:

Notes:

1. dtexpr is a date or datetime expression.
2. Returns an integer corresponding to the year portion of its operand.

Example:

01 MAIN
02   DISPLAY YEAR ( TODAY )
03   DISPLAY YEAR ( CURRENT )
04 END MAIN

MONTH

Purpose:

The MONTH operator extracts the month of a date time expression.

Syntax:

Notes:

1. dtexpr is a date or datetime expression.
2. Returns a positive whole number between 1 and 12 corresponding to the month of its operand.

Example:

01 MAIN
02   DISPLAY MONTH ( TODAY )
03   DISPLAY MONTH ( CURRENT )
04 END MAIN

DAY

Purpose:

The DAY operator extracts the day of the month of a date time expression.

Syntax:

Notes:

1. dtexpr is a date or datetime expression.
2. Returns a positive whole number between 1 and 31 corresponding to the day of the month of its operand.

Example:

01 MAIN
02   DISPLAY DAY ( TODAY )
03   DISPLAY DAY ( CURRENT )
04 END MAIN

WEEKDAY

Purpose:

The WEEKDAY operator extracts the day of the week of a date time expression.

Syntax:

Notes:

1. dtexpr is a date or datetime expression.
2. Returns a positive whole number between 0 and 6 corresponding to the day of the week implied by its operand.
3. The integer 0 (Zero) represents Sunday.

Example:

01 MAIN
02   DISPLAY WEEKDAY ( TODAY )
03   DISPLAY WEEKDAY ( CURRENT )
04 END MAIN

MDY

Purpose:

The MDY operator builds a date value with 3 integers representing the month, day and year.

Syntax:

Notes:

1. intexpr1 is an integer representing the month (from 1 to 12).
2. intexpr2 is an integer representing the day (from 1 to 28, 29, 30 or 31 depending on the month).
3. intexpr3 is an integer representing the year (four digits).
4. The result is a DATE value.

Example:

01 MAIN
02   DISPLAY MDY ( 12, 3+2, 1998 )
03 END MAIN

UNITS

Purpose:

The UNITS operator converts an integer expression to an interval value.

Syntax:

Notes:

1. intexpr is an integer expression.
2. qual is one of the unit specifiers of a DATETIME qualifier.
3. The result is a INTERVAL value.

Example:

01 MAIN
02   DEFINE d DATE
03   LET d = TODAY + 200
04   DISPLAY (d - TODAY) UNITS DAY
05 END MAIN

GET_FLDBUF

Purpose:

The GET_FLDBUF operator returns as character strings the current values of the specified fields.

Syntax:

Notes:

1. group can be a table name, a screen record, a screen array or 'formonly'.
2. field is the name of the screen field.
3. Typically used to get the value of a screen field before the input buffer is copied into the associated variable.
4. If multiple fields are specified between parentheses, you must use the RETURNING clause.
5. When used in a INPUT ARRAY instruction, the runtime system assumes that you are referring to the current row.

Warnings:

1. The values returned by this operator are context dependent;, it must be used carefully. If possible, use the variable associated to the input field instead.

Example:

01 ...
02   LET v = GET_FLDBUF( customer.custname )
03   CALL GET_FLDBUF( customer.* ) RETURNING rec_customer.*
04 ...

INFIELD

Purpose:

The INFIELD operator returns TRUE if its operand is the identifier of the current screen field.

Syntax:

Notes:

1. group can be a table name, a screen record, a screen array or 'FORMONLY'.
2. field is the name of the field in the form.
3. Typically used to check for the current field in a CONSTRUCT, INPUT or INPUT ARRAY instruction.
4. When used in an INPUT ARRAY instruction, the runtime system assumes that you are referring to the current row.

Example:

01 ...
02 INPUT ...
03   IF INFIELD( customer.custname ) THEN
04      MESSAGE "The current field is customer's name."
05 ...

FIELD_TOUCHED

Purpose:

The FIELD_TOUCHED operator returns TRUE if the value of a screen field has changed since the beginning of the interactive instruction.

Syntax:

Notes:

1. group can be a table name, a screen record, a screen array or 'FORMONLY'.
2. field is the name of the field in the form.
3. Typically used to check if the value of a field was edited..
4. When used in an INPUT ARRAY instruction, the runtime system assumes that you are referring to the current row.

Warnings:

1. After a DISPLAY instruction, the modified field is marked as 'touched'.
2. Do not confuse with FGL_BUFFERTOUCHED; in that function, the flag is reset when entering a new field!

Example:

01 ...
02 INPUT ...
03   IF FIELD_TOUCHED( customer.custname ) THEN
04      MESSAGE "Customer name was changed."
05 ...