An expression specifies a value. It can be a simple value, consisting of only a constant or a column name, or it can be more complex. When repeatedly using similar complex expressions, an SQL function to encapsulate a common expression can be considered.
In a Unicode database, an expression that accepts a character or graphic string will accept any string types for which conversion is supported.
expression .-operator--------------------------------------. V (1) | |------+-----+--+-function-invocation--------------+-+----------| +- + -+ +-(expression)---------------------+ '- - -' | (2) | +-constant-------------------------+ +-column-name----------------------+ | (3) | +-variable-------------------------+ | (4) | +-special-register-----------------+ | (5) | +-scalar-fullselect----------------+ | (6) | +-labeled-duration-----------------+ | (7) | +-case-expression------------------+ | (8) | +-cast-specification---------------+ | (9) | +-field-reference------------------+ | (10) | +-xmlcast-specification------------+ | (11) | +-array-element-specification------+ | (12) | +-array-constructor----------------+ | (13) | +-dereference-operation------------+ | (14) | +-method-invocation----------------+ | (15) | +-OLAP-specification---------------+ | (16) | +-row-change-expression------------+ | (17) | +-sequence-reference---------------+ | (18) | '-subtype-treatment----------------' operator (19) |--+-CONCAT------+----------------------------------------------| +- / ---------+ +- * ---------+ +- + ---------+ '- - ---------'
If no operators are used, the result of the expression is the specified value.
SALARY:SALARY'SALARY'MAX(SALARY)
The concatenation operator (CONCAT) 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 below.
The operands can be any combination of string (except binary string), numeric, and datetime values. When any operand is a non-string value, it is implicitly cast to VARCHAR. A binary string can only be concatenated with another binary string. However, through the castable process of function resolution, a binary string can be concatenated with a character string defined as FOR BIT DATA when the first operand is the binary string.
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.
The data type and length attribute of the result is determined from that of the operands as shown in the following table:
Operands | Combined Length Attributes | Result |
---|---|---|
CHAR(A) CHAR(B) | <255 | CHAR(A+B) |
CHAR(A) CHAR(B) | >254 | VARCHAR(A+B) |
CHAR(A) VARCHAR(B) | <4001 | VARCHAR(A+B) |
CHAR(A) VARCHAR(B) | >4000 | LONG VARCHAR |
CHAR(A) LONG VARCHAR | - | LONG VARCHAR |
VARCHAR(A) VARCHAR(B) | <4001 | VARCHAR(A+B) |
VARCHAR(A) VARCHAR(B) | >4000 | LONG VARCHAR |
VARCHAR(A) LONG VARCHAR | - | LONG VARCHAR |
LONG VARCHAR LONG VARCHAR | - | LONG VARCHAR |
CLOB(A) CHAR(B) | - | CLOB(MIN(A+B, 2G)) |
CLOB(A) VARCHAR(B) | - | CLOB(MIN(A+B, 2G)) |
CLOB(A) LONG VARCHAR | - | CLOB(MIN(A+32K, 2G)) |
CLOB(A) CLOB(B) | - | CLOB(MIN(A+B, 2G)) |
GRAPHIC(A) GRAPHIC(B) | <128 | GRAPHIC(A+B) |
GRAPHIC(A) GRAPHIC(B) | >127 | VARGRAPHIC(A+B) |
GRAPHIC(A) VARGRAPHIC(B) | <2001 | VARGRAPHIC(A+B) |
GRAPHIC(A) VARGRAPHIC(B) | >2000 | LONG VARGRAPHIC |
GRAPHIC(A) LONG VARGRAPHIC | - | LONG VARGRAPHIC |
VARGRAPHIC(A) VARGRAPHIC(B) | <2001 | VARGRAPHIC(A+B) |
VARGRAPHIC(A) VARGRAPHIC(B) | >2000 | LONG VARGRAPHIC |
VARGRAPHIC(A) LONG VARGRAPHIC | - | LONG VARGRAPHIC |
LONG VARGRAPHIC LONG VARGRAPHIC | - | LONG VARGRAPHIC |
DBCLOB(A) GRAPHIC(B) | - | DBCLOB(MIN(A+B, 1G)) |
DBCLOB(A) VARGRAPHIC(B) | - | DBCLOB(MIN(A+B, 1G)) |
DBCLOB(A) LONG VARGRAPHIC | - | DBCLOB(MIN(A+16K, 1G)) |
DBCLOB(A) DBCLOB(B) | - | DBCLOB(MIN(A+B, 1G)) |
BLOB(A) BLOB(B) | - | BLOB(MIN(A+B, 2G)) |
Note that, for compatibility with previous versions, 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.
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
returns
the value Pierre Fermat.The value of COLA CONCAT :host_var CONCAT COLC CONCAT COLD is 'AABB CC DDDDD'
The data type is VARCHAR, the length attribute is 17 and the result code page is the section code page. For more information on section code pages, see "Derivation of code page values".
COLA CONCAT COLB CONCAT ?
is
considered VARCHAR(15), because COLA CONCAT COLB is
evaluated first, giving a result that is the first operand of the
second CONCAT operation. 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())
If arithmetic operators are used, the result of the expression is a value derived from the application of the operators to the values of the operands.
If any operand can be null, or the database is configured with dft_sqlmathwarn set to yes, the result can be null.
If any operand 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. Sourced functions can be defined for arithmetic operations on distinct types with a source type that is a signed numeric type.
The prefix operator + (unary plus) does not change its operand. The prefix operator - (unary minus) reverses the sign of a nonzero non decimal floating-point operand. The prefix operator - (unary minus) reverses the sign of all decimal floating-point operands, including zero and special values; that is, signalling and non-signalling NaNs and plus and minus infinity. If the data type of A is small integer, the data type of -A is large integer. The first character of the token following a prefix operator must not be a plus or minus sign.
The infix operators +, -, *, and / specify addition, subtraction, multiplication, and division, respectively. The value of the second operand of division must not be zero, except if the calculation is performed using decimal floating-point arithmetic. These operators can also be treated as functions. Thus, the expression "+"(a,b) is equivalent to the expression a+b. "operator" function.
Operands with a character or graphic string data type, except LOBs, are converted to DECFLOAT(34) using the rules for CAST specification, prior to performing the arithmetic operation. For more information, see "Casting between data types". Note that arithmetic involving graphic string operands is supported only in a Unicode database.
Operands with a string data type are converted to DECFLOAT(34) using the rules for CAST specification prior to performing the arithmetic operation. For more information, refer to "Casting between data types". The string must contain a valid representation of a number.
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) which depends 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, 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.
check (c1*c2 is not null and c1*c2>5000)
to
cause the constraint to be violated on an overflow).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 unary minus) must be within the range of the result type.
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.
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 must not have a precision greater than 31. The result of decimal addition, subtraction, and multiplication is derived from a temporary result which may 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.
The following formulas define the precision and scale of the result of decimal operations in SQL. The symbols p and s denote the precision and scale of the first operand, and the symbols p' and s' denote the precision and scale of the second operand.
The precision is min(31,max(p-s,p'-s') +max(s,s')+1). The scale of the result of addition and subtraction is max (s,s').
The precision of the result of multiplication is min (31,p+ p') and the scale is min(31,s+s').
The precision of the result of division is 31. The scale is 31-p+s-s'. The scale must not be negative.
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.
If the coefficient of the theoretical exact result has no more than the number of digits that reflect its precision (16 or 34), it is used for the result without change (unless there is an underflow or overflow condition). If the coefficient has more than the number of digits that reflect its precision, it is rounded to exactly the number of digits that reflect its precision (16 or 34), and the exponent is increased by the number of digits that are removed.
The CURRENT DECFLOAT ROUNDING MODE special register determines the rounding mode.
If the value of the adjusted exponent of the result is less than Emin, the calculated coefficient and exponent form the result, unless the value of the exponent is less than Etiny, in which case the exponent is set to Etiny, the coefficient is rounded (possibly to zero) to match the adjustment of the exponent, and the sign remains unchanged. If this rounding gives an inexact result, an underflow exception condition is returned.
If the value of the adjusted exponent of the result is larger than Emax, an overflow exception condition is returned. In this case, the result is defined as an overflow exception condition and might be infinite. It has the same sign as the theoretical result.
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
CREATE FUNCTION REVENUE (INCOME, EXPENSES)
RETURNS DECIMAL(8,2) SOURCE "-" (DECIMAL, DECIMAL)
CREATE FUNCTION "-" (INCOME, EXPENSES)
RETURNS DECIMAL(8,2) SOURCE "-" (DECIMAL, DECIMAL)
Expressions within parentheses and dereference operations are evaluated first from left to right. (Parentheses are also used in fullselects, search conditions, and functions. However, they should not be used to arbitrarily group sections within SQL statements.) When the order of evaluation is not specified by parentheses, prefix operators are applied before multiplication and division, and multiplication and division are applied before addition and subtraction. Operators at the same precedence level are applied from left to right.
Scalar fullselect |--(--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.