Expressions

Authorization

scalar-subselect

For information about authorization considerations, see SQL Queries.

sequence-reference

The authorization to reference the sequence. For information about authorization considerations, see Sequence authorization.

function-invocation

The authorization to execute a user-defined function. For information about authorization considerations, see the Function invocation section in Functions.

variable

If the variable is a global variable, the authorization to reference the global variable is required. For information, see Global variables.

Expressions without operators

If no operators are used, the result of the expression is the specified value. For example:

   SALARY:SALARY'SALARY'MAX(SALARY)

Expressions with the concatenation operator

The concatenation operator (CONCAT or ||) combines two operands to form a string expression.

The first operand is an expression that returns a value of any string data type, any numeric data type, or any datetime data type. The second operand is also an expression that returns a value of any string data type, any numeric data type, or any datetime data type. However, some data types are not supported in combination with the data type of the first operand, as described in the remainder of this section.

Concatenation involving both character string operands and graphic string operands is supported only in a Unicode database. Character operands are first converted to the graphic data type before the concatenation. Character strings defined as FOR BIT DATA cannot be cast to the graphic data type.

If either operand can be null, the result can be null, and if either is null, the result is the null value. Otherwise, the result consists of the first operand string followed by the second. Note that no check is made for improperly formed mixed data when doing concatenation.

The length of the result is the sum of the lengths of the operands. In a Unicode database, the string unit of the result is the maximum string unit of the operands, as described in Rules for result data types.

The data type and length attribute of the result is determined from that of the operands as shown in the following table unless an operand is defined with CODEUNITS32:

If an operand is defined with CODEUNITS32, the other operand cannot be defined as FOR BIT DATA. Otherwise, when an operand is defined with CODEUNITS32, the data type and length attribute of the result is determined from that of the operands as shown in the following table:

For compatibility with previous versions or other database products, there is no automatic escalation of results involving LONG VARCHAR or LONG VARGRAPHIC data types to LOB data types. For example, concatenation of a CHAR(200) value and a completely full LONG VARCHAR value would result in an error rather than in a promotion to a CLOB data type.

The code page of the result is considered a derived code page and is determined by the code page of its operands. For more information, see Rules for string conversions.

One operand may be a parameter marker. If a parameter marker is used, then the data type and length attributes of that operand are considered to be the same as those for the non-parameter marker operand. The order of operations must be considered to determine these attributes in cases with nested concatenation.

   FIRSTNME CONCAT ' ' CONCAT LASTNAME

The data type of the result is VARCHAR and its length attribute is 2+5+5+5=17. The result code page is the section code page. For more information about section code pages, see Derivation of code page values.

   COLA CONCAT COLB CONCAT ?

User-defined types and the concatenation operator

A weakly typed distinct type is the only user-defined type that can be used with the concatenation operator. The source type of the weakly typed distinct type is used as the data type of the operand when processing the concatenation operator.

   CREATE FUNCTION ATTACH (TITLE, TITLE_DESCRIPTION)
     RETURNS VARCHAR(50) SOURCE CONCAT (VARCHAR(), VARCHAR())

   CREATE FUNCTION CONCAT (TITLE, TITLE_DESCRIPTION)
     RETURNS VARCHAR(50) SOURCE CONCAT (VARCHAR(), VARCHAR())

Expressions with arithmetic operators

If an expression uses arithmetic operators, the result of the expression is a value derived from the application of the operators to the values of the operands. These operators can be specified using either infix or prefix notation. For example, the statements a+b and "+"(a,b) are equivalent.

If any operand in an arithmetic expression can be null, or if the database is configured with dft_sqlmathwarn set to yes, the result can be null. If any operand in an arithmetic expression has the null value, the result of the expression is the null value.

Arithmetic operators can be applied to signed numeric types and datetime types (see Datetime arithmetic in SQL). For example, USER+2 is invalid. When any operand of an arithmetic operation is a weakly typed distinct type, the operation is performed assuming that the data type of the operand is the source type of the weakly typed distinct type. Sourced functions can be defined for arithmetic operations on strongly typed distinct types with a source type that is a signed numeric type.

Before an arithmetic operation is performed, an operand that is a non-LOB string is converted to DECFLOAT(34) using the rules for CAST specification. For more information, see Casting between data types. Arithmetic operations involving graphic string operands can be performed only for Unicode databases.

Arithmetic errors

If an arithmetic error such as divide-by-zero or a numeric overflow occurs during the processing of an non-decimal floating-point expression, an error is returned (SQLSTATE 22003 or 22012). For decimal floating-point expressions, a warning is returned (SQLSTATEs 0168C, 0168D, 0168E, or 0168F, depending on the nature of the arithmetic condition).

A database can be configured (using dft_sqlmathwarn set to YES) so that arithmetic errors return a null value for the non-decimal floating-point expression, the query returns a warning (SQLSTATE 01519 or 01564), and proceeds with processing the SQL statement.

For decimal floating-point expressions, the setting of dft_sqlmathwarn has no effect; arithmetic conditions return an appropriate value (possibly a decimal floating-point special value), the query returns a warning (SQLSTATEs 0168C, 0168D, 0168E, or 0168F), and proceeds with processing of the SQL statement. Special values returned include plus and minus infinity and not a number. Arithmetic expressions involving one or more decimal floating-point numbers never evaluate to a null value unless one or more of the arguments to the expression are null.

When arithmetic errors are treated as nulls, there are implications on the results of SQL statements. The following are some examples of these implications.

• An arithmetic error that occurs in the expression that is the argument of an aggregate function causes the row to be ignored in the determining the result of the aggregate function. If the arithmetic error was an overflow, this may significantly impact the result values.
• An arithmetic error that occurs in the expression of a predicate in a WHERE clause can cause rows to not be included in the result.
• An arithmetic error that occurs in the expression of a predicate in a check constraint results in the update or insert proceeding since the constraint is not false.

Two integer operands

If both operands of an arithmetic operator are integers, the operation is performed in binary and the result is a large integer unless either (or both) operand is a big integer, in which case the result is a big integer. Any remainder of division is lost. The result of an integer arithmetic operation (including negation by means of a unary minus operator) must be within the range of the result type.

Integer and decimal operands

If one operand is an integer and the other is a decimal, the operation is performed in decimal using a temporary copy of the integer that has been converted to a decimal number with precision p and scale 0; p is 19 for a big integer, 11 for a large integer, and 5 for a small integer.

Two decimal operands

If both operands are decimal, the operation is performed in decimal. The result of any decimal arithmetic operation is a decimal number with a precision and scale that are dependent on the operation and the precision and scale of the operands. If the operation is addition or subtraction and the operands do not have the same scale, the operation is performed with a temporary copy of one of the operands. The copy of the shorter operand is extended with trailing zeros so that its fractional part has the same number of digits as the longer operand.

The result of a decimal operation cannot have a precision greater than 31. The result of decimal addition, subtraction, and multiplication is derived from a temporary result which can have a precision greater than 31. If the precision of the temporary result is not greater than 31, the final result is the same as the temporary result.

Decimal arithmetic in SQL

Floating-point operands

If either operand of an arithmetic operator is floating-point, but not decimal floating-point, the operation is performed in floating-point. The operands are first converted to double-precision floating-point numbers, if necessary. Thus, if any element of an expression is a floating-point number, the result of the expression is a double-precision floating-point number.

An operation involving a floating-point number and an integer is performed with a temporary copy of the integer which has been converted to double-precision floating-point. An operation involving a floating-point number and a decimal number is performed with a temporary copy of the decimal number which has been converted to double-precision floating-point. The result of a floating-point operation must be within the range of floating-point numbers.

The order in which floating-point operands (or arguments to functions) are processed can slightly affect results because floating-point operands are approximate representations of real numbers. Since the order in which operands are processed may be implicitly modified by the optimizer (for example, the optimizer may decide what degree of parallelism to use and what access plan to use), an application that uses floating-point operands should not depend on the results being precisely the same each time an SQL statement is executed.

Decimal floating-point operands

General arithmetic operation rules for decimal floating-point

   INFINITY + 1          = INFINITY
   INFINITY + INFINITY   = INFINITY
   INFINITY + -INFINITY  = NAN         -- warning
   NAN + 1               = NAN
   NAN + INFINITY        = NAN
   1 - INFINITY          = -INFINITY
   INFINITY - INFINITY   = NAN         -- warning
   -INFINITY - -INFINITY = NAN         -- warning
   -0.0 - 0.0E1          = -0.0
   -1.0 * 0.0E1          = -0.0
   1.0E1 / 0             = INFINITY    -- warning
   -1.0E5 / 0.0          = -INFINITY   -- warning
   1.0E5 / -0            = -INFINITY   -- warning
   INFINITY / -INFINITY  = NAN         -- warning
   INFINITY / 0          = INFINITY
   -INFINITY / 0         = -INFINITY
   -INFINITY / -0        = INFINITY

User-defined types as operands of arithmetic operators

Weakly typed distinct type operands can be used with arithmetic operators, provided that source type of the weakly typed distinct type is supported by the arithmetic operator. There is no need to create additional user-defined functions to support arithmetic operations for weakly typed distinct type operands.

   CREATE FUNCTION REVENUE (INCOME, EXPENSES)
     RETURNS DECIMAL(8,2) SOURCE "-" (DECIMAL, DECIMAL)

   CREATE FUNCTION "-" (INCOME, EXPENSES)
     RETURNS DECIMAL(8,2) SOURCE "-" (DECIMAL, DECIMAL)

Expressions with bitwise operators

The bitwise operators BITAND (&), BITOR (|), BITXOR (ˆ), and BITNOT (~) correspond to the similarly named scalar functions described in BITAND, BITANDNOT, BITOR, BITXOR, and BITNOT scalar functions.

If any operand in a bitwise expression can be null, the result can be null. If any operand in a bitwise expression has the null value, the result of the expression is the null value.

The unary bitwise BITNOT (~) prefix operator reverses each bit of the operand to which it applies. If the data type of a value x is DECIMAL, REAL, DOUBLE, or DECFLOAT(16), the data type of ~x is DECFLOAT(34); otherwise, the data type is the same as that of x.

Before a bitwise operation is performed, an operand that is a non-LOB string is converted to DECFLOAT(34) using the rules for CAST specification. For more information, see Casting between data types. Bitwise operations involving graphic string operands can be performed only for Unicode databases.

Precedence of operations

Scalar fullselect

Scalar fullselect

A scalar fullselect is a fullselect, enclosed in parentheses, that returns a single row consisting of a single column value. If the fullselect does not return a row, the result of the expression is the null value. If the select list element is an expression that is simply a column name or a dereference operation, the result column name is based on the name of the column. The authorization required for a scalar fullselect is the same as that required for an SQL query.

Compatability features