Language Reference Guide : Language Elements : Expressions : Operators
 
Share this page          
Operators
OpenROAD supports these operators:
Arithmetic operators
The String operator
Logical operators
Comparison (boolean) operators
The Like operator
The Is Null operator
The following sections describe these operators.
Arithmetic Operators
The arithmetic operators in OpenROAD combine numeric expressions into new expressions. These operators are listed in the following table:
Operator
Description
+
Addition
-
Subtraction
*
Multiplication
/
Division
**
Exponentiation
The following are examples:
num_days = num_days + 30;
area = pi * r**2;
In addition to standard arithmetic, OpenROAD supports date arithmetic. For example, if start_date is a date field, OpenROAD can compute the result value for the following statement:
start_date = start_date + '2 days';
The precedence of operators in expressions is as follows (highest to lowest):
**
/
*
+, -
Operators are processed from left to right to control equal precedence.
For example, the following expression has a value of 14 because the multiplication operator (*) has precedence over the addition operator (+):
2 + 3 * 4
You can use parentheses to force alternate precedence. For example, placing parentheses around the expression from the previous example alters its value to 20:
(2 + 3) * 4
Take care when dividing either a float variable into an integer literal constant or an integer literal constant into a float variable, as the result is of the float type but rounded down to the nearest integer.
String Operator
The concatenation operator (+) joins string expressions together into new expressions, for example:
answer = prompt
    'Please enter department for ' + '
        name + ': ';
Logical Operators
The logical operators AND, OR, and NOT join logical expressions into new expressions with a boolean value. The following truth tables show the result of comparisons made with these operators.
Truth table for AND comparisons:
True
False
Null
True
True
False
null
False
False
False
False
Null
null
False
null
Truth table for OR comparisons:
True
False
Null
True
True
True
True
False
True
False
null
Null
True
null
null
Truth table for NOT comparisons:
True
False
Null
False
True
Null
The order of precedence for these operators, from highest to lowest, is as follows:
NOT
AND
OR
You can use parentheses to change this order. In the following example:
if (empnum > 0 or deptno > 0) and status != 3
OpenROAD first evaluates empnum and deptno and then evaluates status.
The following example illustrates the use of comparison and logical operators:
if empnum > 0 and status != 3 then
    callframe newemployee;
endif;
The newemployee frame is called only if both conditions are TRUE, that is, if the current value of empnum is greater than 0, and status has any value other than 3.
Comparison (Boolean) Operators
Boolean expressions yield the boolean values TRUE, FALSE, or (with nullable expressions) null. 4GL includes the logical operators AND, OR, and NOT, and the following comparison operators:
Operator
Operator
=
Equal to
!=
<>
^=
Not equal to
<
Less than
<=
Less than or equal to
>
Greater than
>=
Greater than or equal to
is null
Value is null
is not null
Value is other than null
like
Value is an instance of pattern-matching string
not like
Value is not an instance of pattern-matching string
With null, the result of a comparison will be true, false, or null. The result of a comparison (except for a comparison of reference variables) is null when one or both operands of the expression are null.
In 4GL, the if and while statements and the where clause of query statements test the results of boolean expressions. For example, assume that status is a character field on a form. Whenever the boolean expression “status = 'n' ” is TRUE, the following statement calls the NewProject frame:
if status = 'n' then
    callframe NewProject;
endif;
In if and while statements, if the result of an expression is null, the flow of control occurs exactly as if the boolean expression evaluates to FALSE. For examples, see Nulls in Expressions.
Reference variables may be compared, but only for equality, inequality, is null, or is not null. When comparing two reference variables that both contain null for equality (or inequality), the result is TRUE or FALSE, not null. Similarly, when comparing two reference variables only one of which contains null for equality, the result is FALSE.
SQL Operators
An operator is a symbol that represents an action performed on one or more expressions.
There are three types of SQL operators:
Arithmetic
Comparison
Logical
Arithmetic Operators
Arithmetic operators are used to combine numeric expressions arithmetically to form other numeric expressions.
The following are the valid arithmetic operators (in descending order of precedence):
Arithmetic Operator
Description
**
Exponentiation (binary)
/
Division (binary)
*
Multiplication (binary)
+ and -
Plus, minus (unary)
+ and -
Addition, subtraction (binary)
Unary operators group from right to left, while binary operators group from left to right. Use the unary minus (-) to reverse the algebraic sign of a value.
To force a desired order of evaluation, use parentheses. For example:
(job.Lowsal + 1000) * 12
is an expression in which the parentheses force the addition operator (+) to take precedence over the multiplication operator (*).
Comparison Operators
Comparison operators compare two expressions. SQL includes the following comparison operators:
Comparison Operator
Description
=
Equal to
<>
Not equal to
>
Greater than
>=
Greater than or equal to
<
Less than
<=
Less than or equal to
The <> operator can also be specified as != or ^=.
All comparison operators are of equal precedence.
The equal sign (=) also serves as the assignment operator in assignment operations (see Assignment Operations).
Logical Operators
SQL has three logical operators, shown in order of precedence:
NOT
AND
OR
Use parentheses to change the order of evaluation. For example, the following expression:
exprA OR exprB AND exprC
is evaluated as:
exprA OR (exprB AND exprC)
When parentheses are used as follows:
(exprA OR exprB) AND exprC
OpenROAD evaluates (exprA or exprB) first, then uses the AND operator for the result with exprC.
Parentheses can also be used to change the default evaluation order of a series of expressions combined with the same logical operator. For example, the following expression:
exprA AND exprB AND exprC
is evaluated as:
(exprA AND exprB) AND exprC
To change this default left-to-right grouping, use parentheses as follows:
exprA AND (exprB AND exprC)
The parentheses direct OpenROAD to use the AND operator for exprB and exprC, then use the AND operator for that result with exprA.
SQL Operations
The basic SQL operations supported include:
String concatenation operations
Assignment operations
Arithmetic operations
Date(ingresdate) operations
String Concatenation Operations
To concatenate strings, use the + operator.
For example:
'This ' + 'is ' + 'a ' + 'test.'
gives the value:
'This is a test.'
Also, the concat function can be used to concatenate strings. For details, see String Functions.
Assignment Operations
An assignment operation places a value in a column or variable. Assignment operations occur during the execution of INSERT, UPDATE, FETCH, CREATE TABLE...AS SELECT, and embedded SELECT statements. Assignments can also occur within a database procedure.
When an assignment operation occurs, the data types of the assigned value and the receiving column or variable must either be the same or comparable.
If the data types are not the same, comparable data types are converted.
If the data types are not comparable, convert the assignment value into a type that is the same or comparable with the receiving column or variable.
For information about the type conversion functions, see Default Type Conversion.
Character String Assignments
All character types are comparable with one another and with integer and float types. Any character string can be assigned to any column or variable of character data type. A character string can also be assigned to a column or variable of integer or float type, as long as the content of the string is a valid numeric value. The result of the assignment depends on the types of the assignment string and the receiving column or variable.
Assigned String
Receiving Column or Variable
Description
Fixed-length (c or char)
Fixed-length
Note:  The assigned string is truncated or padded with spaces if the receiving column or variable is not the same length as the fixed length string. If the assigned string is truncated to fit into a host variable, a warning condition is indicated in SQLWARN. For a discussion of the SQLWARN indicators, see "SQL Communications Area (SQLCA)" in the Ingres SQL Reference Guide.
Fixed-length
Variable-length (varchar, long varchar, or text)
The trailing spaces are trimmed. If the receiving column or variable is shorter than the fixed length string, the fixed length string is truncated from the right side. If the assignment was to a variable, a warning condition is indicated in SQLWARN. For a discussion of the SQLWARN indicators, see "SQL Communications Area (SQLCA)" in the Ingres SQL Reference Guide.
Note:  If a long varchar value over is assigned to another character data type, the result is truncated at the maximum row size configured but not exceeding 32,000 (16,000 in a UTF-8 instance).
Variable-length (varchar, long varchar, or text)
Fixed-length
The assigned string is truncated or padded with spaces if the receiving column or variable is not the same length as the variable length string.
If a long varchar value over is assigned to another character data type, the result is truncated at the maximum row size configured but not exceeding 32,000 (16,000 in a UTF-8 instance).
Variable-length
Variable-length
The variable length string is truncated if the receiving column or variable is not long enough.
String Truncation
If an attempt is made to insert a string value into a table column or variable that is too short to contain the value, the string is truncated.
String truncation also can occur as a result of the following statements:
COPY
CREATE TABLE...SELECT
INSERT
UPDATE
Numeric Assignments
All numeric types are compatible with one another and with character types. Money is compatible with all of the numeric and string data types.
Numeric assignments follow these rules:
The client or the DBMS Server can truncate leading zeros, or all or part of the fractional part of a number if necessary. If truncation of the non-fractional part of a value (other than leading zeros) is necessary, an overflow error results. OpenROAD does not report numeric overflow errors.
If the receiving column or variable specifies more digits to the right of the decimal point than is present in the assignment value, the assignment value is padded with trailing zeros.
When a float, float4, decimal, or money value is assigned to an integer column or variable, the fractional part is truncated.
When a decimal value with a scale greater than two is assigned to a money column or variable, the fractional value is rounded.
Character data is subject to numeric syntax checks if assigned in a numeric or money context.
Date(ingresdate) Assignments
The date data type is compatible with string data types if the value in the string is a valid representation of a date or time input format.
Absolute date or interval values can be assigned to an ingresdate variable. In addition, a string literal, a character string host variable, or a character string column value can be assigned to an date column if its value conforms to the valid input formats for Ingres dates or times.
When a date value is assigned to a character string, OpenROAD converts the date to the display format. For more information about date display formats, see Date and Time Display.
Logical Key Assignments
There are two types of logical keys:
TABLE_KEY
This type is comparable only with another TABLE_KEY or a char that has a length of 8 bytes (char(8)).
OBJECT_KEY
This type is comparable only with another OBJECT_KEY or a char that has a length of 16 bytes (char(16)).
If a logical key column is declared as SYSTEM_MAINTAINED, the DBMS Server assigns the values to that column. System maintained logical key columns cannot be updated. If a logical key column is declared as NOT SYSTEM_MAINTAINED, values must be assigned to the column.
In embedded SQL programs, if values are assigned using host variables, the host variables must be char(8)-comparable for TABLE_KEY columns, and char(16)-comparable variables for OBJECT_KEY columns.
Values can be assigned to logical keys, not system maintained, using a hex constant or a string literal. For information about the format of a hex constant, see String Literals.
Values assigned to TABLE_KEYs must be 8 bytes long. Values assigned to OBJECT_KEYs must be 16 bytes long. The following example inserts the value 1 into a TABLE_KEY column using a hex constant:
INSERT INTO test (tablekey) VALUES (TABLE_KEY(X'0000000000000001'));
The previous statement inserts 7 bytes containing 0, followed by 1 byte containing 1. The value is explicitly converted to a table key using the table_key conversion function.
The following example assigns the value 'abc' (padded to 8 characters for data type compatibility) to a logical key column:
INSERT INTO test (tablekey) VALUES (TABLE_KEY('abc'));
Null Value Assignments
A null can be assigned to a column of any data type if the column was defined as a nullable column. A null can also be assigned to a host language variable if there is an indicator variable associated with the host variable.
To ensure that a null is not assigned to a column, use the Ifnull Function.
Arithmetic Operations
An arithmetic operation combines two or more numeric expressions using the arithmetic operators to form a resulting numeric expression. For details about arithmetic operators, see Arithmetic Operators.
Before performing any arithmetic operation, the participating expressions are converted to identical data types. After the arithmetic operation is performed, the resulting expression too has that storage format.
Default Type Conversion
When two numeric expressions are combined, the data types of the expressions are converted to be identical; this conversion determines data type of the result. The expression having the data type of lower precedence is converted to the data type of the higher. The order of precedence among the numeric data types is, in highest-to-lowest order:
money
float4
float
decimal
integer8
integer
smallint
integer1
For example, in an operation that combines an integer and a floating point number, the integer is converted to a floating point number. If the operands are two integers of different sizes, the smaller is converted to the size of the larger. The conversions are done before the operation is performed.
The following table lists the data types that result from combining numeric data types in expressions:
integer1
smallint
integer
integer8
decimal
float
float4
money
integer1
integer
integer
integer
integer8
decimal
float
float4
money
smallint
integer
integer
integer
integer8
decimal
float
float4
money
integer
integer
integer
integer
integer8
decimal
float
float4
money
integer8
integer8
integer8
integer8
integer8
decimal
float
float4
money
decimal
decimal
decimal
decimal
decimal
decimal
float
float4
money
float
float
float
float
float
float
float
float4
money
float4
float4
float4
float4
float4
float4
float4
float4
money
money
money
money
money
money
money
money
money
money
For example, for the expression:
(job.Lowsal + 1000) * 12
the first operator (+) combines a float4 expression (job.Lowsal) with a smallint constant (1000). The result is float4. The second operator (*) combines the float4 expression with a smallint constant (12), resulting in a float4 expression.
To convert one data type to another, use data type conversion functions (see Data Type Conversion Functions).
Arithmetic Operations on Decimal Data Types
In expressions that combine decimal values and return decimal results, the precision (total number of digits) and scale (number of digits to the right of the decimal point) of the result can be determined, as shown in the following table:
Precision
Scale
Addition and subtraction
Largest number of fractional digits plus largest number of non-fractional digits + 1 (to a maximum of 39)
Scale of operand having the largest scale
Multiplication
Total of precisions to a maximum of 39
Total of scales to a maximum of 39
Division
39
(39 – precision of first operand) + (scale of first operand) – (scale of second operand)
For example, in the following decimal addition operation:
1.234 + 567.89
the scale and precision of the result is calculated as follows:
Precision = 7
Calculated as 3 (largest number of fractional digits) + 3 (largest number of non-fractional digits) + 1 = 7
Scale = 3
The first operand has the largest number of digits to the right of the decimal point.
Result:
0569.124
If exponentiation is performed on a decimal value, the resulting data type is float.
Date/Time Arithmetic
The following arithmetic is supported for date/time data types.
Note:   
DATE(INGRESDATE) can store both absolute values and interval values.
'interval type' can be any of the ANSI forms or the DATE(INGRESDATE) interval form
'absolute type' is a non-interval form
Operators that return absolute date forms:
'absolute type'
-
'interval type'
=
'absolute type'
'absolute type'
+
'interval type'
=
'absolute type'
'interval type'
+
'absolute type'
=
'absolute type'
The combining rules are: If either term is date(ingresdate), the result will also be date(ingresdate); otherwise, the result will be the same as the absolute term.
Operators used with intervals:
- 'interval type'
=
'interval type'
=
 
+ 'interval type'
=
'interval type'
=
 
'absolute type'
-
'absolute type'
=
'interval type'
'interval type'
+
'interval type'
=
'interval type'
'interval type'
-
'interval type'
=
'interval type'
'interval type'
*
'numeric-expression'
=
'interval type'
'numeric-expression'
*
'interval type'
=
'interval type'
'interval type'
/
'numeric-expression'
=
'interval type'
'interval type'
/
'interval type'
=
'numeric-expression'
The combining rule is, if an operand is DATE(INGRESDATE), the result will also be DATE(INGRESDATE).
Date(ingresdate) Interval Arithmetic
Date(ingresdate) intervals can be added, subtracted, and divided, and can be multiplied or divided with a numeric expression. The following lists the results of date arithmetic:
When adding intervals, each of the units is added.
For example:
date('6 days') + date('5 hours')
yields, 6 days 5 hours, while:
date('4 years 20 minutes') + date('6 months 80 minutes')
yields, 4 years 6 months 1 hour 40 minutes.
In the previous example, 20 minutes and 80 minutes are added and the result is 1 hour 40 minutes. 20 minutes plus 80 minutes equals 100 minutes, but this result overflows the minute time unit because there are 60 minutes in an hour. Overflows are propagated upward except when intervals are added. In the above example, the result is 1 hour 40 minutes. However, days are not propagated to months. For example, if 25 days is added to 23 days, the result is 48 days.
When intervals or absolute dates are subtracted, the result is returned in appropriate time units. For example, if the following subtraction is performed:
date('2 days') - date('4 hours')
the result is 1 day 20 hours.
Date constants can be converted into numbers of days relative to an absolute date. For example, to convert today’s date to the number of days since January 1, 1900:
num_days = int4(interval('days', 'today' - date('1/1/00')))
To convert the interval back to a date:
(date('1/1/00') + concat(char(num_days), ' days'))
where num_days is the number of days added to the date constant.
Adding a month to a date always yields the same date in the next month. For example:
date('1-feb-98') + '1 month'
yields March 1.
If the result month has fewer days, the resulting date is the last day of the next month. For instance, adding a month to May 31 yields June 30, instead of June 31, which does not exist. Similar rules hold for subtracting a month and for adding and subtracting years.
Dates that are stored without time values can acquire time values as a result of date arithmetic. For example, the following SQL statements create a table with one date column and store today’s date (with no time) in the column:
create table dtest (dcolumn date);
insert into dtest (dcolumn) values (date('today'));
If the contents of the date column is selected using the following query:
select dcolumn from dtest;
a date with no time is returned. For example:
09-aug-2001
If date arithmetic is used to adjust the value of the date column, the values in the column acquire a time. For example:
update dtest set dcolumn=dcolumn-date('1 hour');
select dcolumn from dtest;
returns the value:
08-aug-1998 23:00:00
Date(ingresdate) Comparisons
In comparisons, a blank (default) date(ingresdate) is less than any interval date(ingresdate). All interval dates(ingresdates) are less than all absolute date(ingresdates). Intervals are converted to comparable units before they are compared. For instance, before comparing date('5 hours') and date('200 minutes'), both the hours and minutes are converted to milliseconds internally before comparing the values. Dates(ingresdates) are stored in Greenwich Mean Time (GMT). For this reason, 5:00 PM Pacific Standard Time is equal to 8:00 PM Eastern Standard Time.
Operator Coercion Rules
The implicit coercion rules for operators are as follows:
1. Generally, where a numeric type is required, a string type can be supplied. This is the case for c, text, char, varchar, nchar, and nvarchar. If the character data turns out not to be numeric in form, a conversion error will occur.
2. Generally, where a string type is required, a numeric type can be supplied. This is the case for all integers, decimal, and all floats.
3. When an operator combines two values of the same data class, string or number, then the following precedence, shown from highest to lowest, is followed:
nchar
nvarchar
c
text
money
char
varchar
float
decimal
int
The resolver coerces to types that retain data without over inflating it. For example, coercion of nchar to char is avoided due to loss of richness of data form or collation. Similarly, coercing to a long type might work but would be grossly inefficient. Associated with this is the practical notion that most operators have a type themselves, some are arithmetic (*, /, and so on) and some are string (LIKE and so on).
4. The exception to Rule 3 is the "+" operator, which is an arithmetic operator as well as a concatenation operator for strings. The two modes coexist, but if a number is added to a string, then the result type is a number.
5. Mixed type comparisons between character and numeric data are virtually coerced into Numeric String data before being compared.
Like Operator and Pattern Matching
The like operator lets you compare two strings to see whether they resemble each other in specific ways. To use this operator, specify a pattern (a string of characters with special formatting characters) that specifies what the compared string must look like. If the string being compared is an instance of the pattern described by the pattern string, the expression evaluates to TRUE.
The syntax for the like operation is:
charvar [not] like pattern [escape escapechar]
charvar
Specifies a character string variable
pattern
Specifies a character string literal or character string variable that can include the following special characters:
An underscore (_) matches any single character. For example, the pattern string, “_a” , would produce the matching text strings “Xa,” “aa” and “/a”.
A percent sign (%) matches any string of characters, regardless of length. For example, the pattern string, “Fred%”, would produce the matching text strings of “Fred,” “Frederick,” and “Fred S. Smith, Ph.D.”
The following statement uses “%” to test the value of “emp_name” to see if it starts with “Fred” and ends with a last name of “Smith”:
if emp_name like 'Fred% Smith' then
    msg = 'Found a guy matching the pattern';
endif;
Square brackets [ ], where each bracket is preceded by an escape character, which you define, match the corresponding character position with any of the characters in the bracketed string. The typical definition of an escape character is usually done with quotes.
The escape character can be used to escape itself, so no matter what character you select, you can use it as a character in the pattern by giving it twice, for example:
name like '..%.%' escape '.'
This phrase matches any string beginning with a period (.) and ending with a percent sign.
escapechar
Specifies a character string literal or character string variable of length 1. It is part of the escape clause, [escape escapechar], which has two functions:
You can use it to “escape” the special interpretation of the pattern matching characters “_” and “%.” For example, the following fragment matches a string of any first character and an underline as the second character:
name like '_\_' escape '\'
You can use the escape clause with square brackets to match any of the characters you specify. Typically, square brackets are treated the same as any other regular characters. However, when preceded by the escape character, the brackets define a “match-any-of-these-characters” string.
In the following example, you could use the escape clause with square brackets to match all strings ending with X, Y, or Z:
name like '%\[XYZ\]' escape '\'
As another example:
name like '_\[BC\]C%' escape '\'
This pattern matches “ABC,” “ACC,” “FCC Fairness Doctrine” and does not match “FDC Yellow #42” or “Access” (because “cc” is not uppercase).
Finally, the following example matches any string beginning with a left bracket and whose second character is “1,” “2,” “3” or a right bracket:
name like '[\[123]\]' escape '\'
The escape character cannot be followed in the pattern by any character other than underscore, percent, left or right bracket, or another escape character.
Pattern matching characters have no effect unless used with "like." For example, consider a statement that begins as follows:
if name = 'Fred%' then ...
The comparison tests the value of “name” to see whether it equals the constant literal “Fred%.” In the event that it does, the object of the condition is executed.
Is [Not] Null Operator
The is [not] null operator tests whether an expression is null. The syntax of this operator is:
expression is [not] null
The following code is an example:
if salary is null then
    sal_msg = 'Salary amount is unknown.'
endif;