Expressions
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. If you repeatedly use similar complex expressions, consider using an SQL function to encapsulate a common expression.
Authorization
The use of some of the expressions, such as a scalarsubselect, sequencereference, or functioninvocation, might require special authorization. For these expressions, the privileges held by the authorization ID of the statement must include the following authorizations: scalarsubselect
 For information about authorization considerations, see
SQL Queries
.  sequencereference
 The authorization to reference the sequence. For information about authorization considerations,
see
Sequence authorization
.  functioninvocation
 The authorization to execute a userdefined function. For information about authorization
considerations, see the
Function invocation
section inFunctions
.  variable
 If the variable is a global variable, the authorization to reference the global variable is
required. For information, see
Global variables
.
In a Unicode database, an expression that accepts a character or graphic string will accept any string types for which conversion is supported.
 ^{1} See Function invocation for more information.
 ^{2} See Constants for more information.
 ^{3} See References to variables for more information.
 ^{4} See Special registers for more information.
 ^{5} See Scalar fullselect for more information.
 ^{6} See Durations for more information.
 ^{7} See CASE expression for more information.
 ^{8} See CAST specification for more information.
 ^{9} See Field reference for more information.
 ^{10} See XMLCAST specification for more information.
 ^{11} See ARRAY element specification for more information.
 ^{12} See Array constructor for more information.
 ^{13} See Dereference operation for more information.
 ^{14} See Method invocation for more information.
 ^{15} See OLAP specification for more information.
 ^{16} See ROW CHANGE expression for more information.
 ^{17} See Sequence reference for more information.
 ^{18} See Subtype treatment for more information.
 ^{19} See Search conditions for more information.
 ^{20} The  operator can be used as a synonym for CONCAT.
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.
 String (except binary string)
 Numeric (this is implicitly cast to VARCHAR)
 Datetime (this is implicitly cast to VARCHAR)
 Boolean (this is implicitly cast to VARCHAR)
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:
Operands  Combined Length Attributes^{1}  Result 

CHAR(A) CHAR(B)  <256  CHAR(A+B) 
CHAR(A) CHAR(B)  >255  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, 2147483647)) 
CLOB(A) VARCHAR(B)    CLOB(MIN(A+B, 2147483647)) 
CLOB(A) LONG VARCHAR    CLOB(MIN(A+32700, 2147483647)) 
CLOB(A) CLOB(B)    CLOB(MIN(A+B, 2147483647)) 
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, 1073741823)) 
DBCLOB(A) VARGRAPHIC(B)    DBCLOB(MIN(A+B, 1073741823)) 
DBCLOB(A) LONG VARGRAPHIC    DBCLOB(MIN(A+16350, 1073741823)) 
DBCLOB(A) DBCLOB(B)    DBCLOB(MIN(A+B, 1073741823)) 
BINARY(A) BINARY(B)  <256  BINARY(A+B) 
BINARY(A) CHAR(B) FOR BIT DATA  <256  BINARY(A+B) 
BINARY(A) BINARY(B)  >255  VARBINARY(A+B) 
BINARY(A) CHAR(B) FOR BIT DATA  >255  VARBINARY(A+B) 
BINARY(A) VARBINARY(B)    VARBINARY(MIN(A+B, 32672)) 
BINARY(A) VARCHAR(B) FOR BIT DATA    VARBINARY(MIN(A+B, 32672)) 
VARBINARY(A) VARBINARY(B)    VARBINARY(MIN(A+B, 32672)) 
VARBINARY(A) CHAR(B) FOR BIT DATA    VARBINARY(MIN(A+B, 32672)) 
VARBINARY(A) VARCHAR(B) FOR BIT DATA    VARBINARY(MIN(A+B, 32672)) 
BLOB(A) BINARY(B)    BLOB(MIN(A+B, 2147483647)) 
BLOB(A) CHAR(B) FOR BIT DATA    BLOB(MIN(A+B, 2147483647)) 
BLOB(A) VARBINARY(B)    BLOB(MIN(A+B, 2147483647)) 
BLOB(A) VARCHAR(B) FOR BIT DATA    BLOB(MIN(A+B, 2147483647)) 
BLOB(A) BLOB(B)    BLOB(MIN(A+B, 2147483647)) 
Notes ^{1} The numbers specified for the Combined Length Attributes are listed in OCTETS for character strings and CODEUNITS16 for graphic strings. Refer to the next table if an operand is defined with CODEUNITS32. 
Operands  Combined Length Attributes^{1}  Result 

CHAR(A) CHAR(B)  <256  CHAR(A+B) 
CHAR(A) CHAR(B)  >255  VARCHAR(A+B) 
CHAR(A) VARCHAR(B)    VARCHAR(MIN(A+B, 32672)) 
CHAR(A) LONG VARCHAR    LONG VARCHAR 
VARCHAR(A) VARCHAR(B)    VARCHAR(MIN(A+B, 32672)) 
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)    VARGRAPHIC(MIN(A+B, 16336)) 
GRAPHIC(A) LONG VARGRAPHIC    LONG VARGRAPHIC 
VARGRAPHIC(A) VARGRAPHIC(B)    VARGRAPHIC(MIN(A+B, 16336)) 
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)) 
BINARY(A) BINARY(B)  <256  BINARY(A+B) 
BINARY(A) CHAR(B) FOR BIT DATA  <256  BINARY(A+B) 
BINARY(A) BINARY(B)  >255  VARBINARY(A+B) 
BINARY(A) CHAR(B) FOR BIT DATA  >255  VARBINARY(A+B) 
BINARY(A) VARBINARY(B)    VARBINARY(MIN(A+B, 32672)) 
BINARY(A) VARCHAR(B) FOR BIT DATA    VARBINARY(MIN(A+B, 32672)) 
VARBINARY(A) VARBINARY(B)    VARBINARY(MIN(A+B, 32672)) 
VARBINARY(A) CHAR(B) FOR BIT DATA    VARBINARY(MIN(A+B, 32672)) 
VARBINARY(A) VARCHAR(B) FOR BIT DATA    VARBINARY(MIN(A+B, 32672)) 
BLOB(A) BINARY(B)    BLOB(MIN(A+B, 2G)) 
BLOB(A) CHAR(B) FOR BIT DATA    BLOB(MIN(A+B, 2G)) 
BLOB(A) VARBINARY(B)    BLOB(MIN(A+B, 2G)) 
BLOB(A) VARCHAR(B) FOR BIT DATA    BLOB(MIN(A+B, 2G)) 
BLOB(A) BLOB(B)    BLOB(MIN(A+B, 2G)) 
Notes ^{1} The numbers specified for the Combined Length Attributes are listed in OCTETS for character strings and CODEUNITS16 for graphic strings. Refer to the next table if 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:
Operands  Combined Length Attributes  Result 

CHAR(A) CHAR(B)  <64  CHAR(A+B) 
CHAR(A) CHAR(B)  >63  VARCHAR(A+B) 
CHAR(A) VARCHAR(B)    VARCHAR(MIN(A+B, 8168)) 
VARCHAR(A) VARCHAR(B)    VARCHAR(MIN(A+B, 8168)) 
CLOB(A) CHAR(B)    CLOB(MIN(A+B, 536870911)) 
CLOB(A) VARCHAR(B    CLOB(MIN(A+B, 536870911)) 
CLOB(A) CLOB(B)    CLOB(MIN(A+B, 536870911)) 
GRAPHIC(A) GRAPHIC(B)  <64  GRAPHIC(A+B) 
GRAPHIC(A) GRAPHIC(B)  >63  VARGRAPHIC(A+B) 
GRAPHIC(A) VARGRAPHIC(B)    VARGRAPHIC(MIN(A+B, 8168)) 
VARGRAPHIC(A) VARGRAPHIC(B)    VARGRAPHIC(MIN(A+B, 8168)) 
DBCLOB(A) CHAR(B)    DBCLOB(MIN(A+B, 536870911)) 
DBCLOB(A) VARCHAR(B)    DBCLOB(MIN(A+B, 536870911)) 
DBCLOB(A) DBCLOB(B)    DBCLOB(MIN(A+B, 536870911)) 
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 nonparameter 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 column
COLA
is defined as VARCHAR(5) and the value'AA'
was inserted into it.  The character host variable
:host_var
is defined with length 5 and value'BB '
.  The column
COLC
defined as CHAR(5) and the value'CC'
was inserted into it.  The column
COLD
defined as CHAR(5) and the value'DDDDD'
was inserted into it.
COLA CONCAT :host_var CONCAT COLC CONCAT
COLD
is 'AABB CC DDDDD'
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
defined as CHAR(10)COLB
defined as VARCHAR(5)
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.Userdefined types and the concatenation operator
A weakly typed distinct type is the only userdefined 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.
 A nonzero nondecimal floatingpoint operand
 A decimal floatingpoint operand, including 0, 0, quiet NaNs, signalling NaNs, +infinity, and infinity
x
is small integer, the data type of
x
is large integer. The first character of the token following a prefix operator
cannot be a plus (+) or minus () symbol.Arithmetic Operator  Name  Description 

+  Addition  The result is the sum of the first and second arguments. 
  Subtraction  The result is the first argument minus the second argument. 
*  Multiplication  The result is the first argument multiplied by the second argument. 
/  Division  The result is the first argument divided by the second argument. The value of the second operand may not be zero, unless the calculation is performed using decimal floatingpoint arithmetic. 
%  Modulo  The result is the remainder of the first argument divided by the second argument. 
**  Exponential  The result is the first argument raised to the power of the second argument. The data type of
the result is:

Before an arithmetic operation is performed, an operand that is a nonLOB 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 dividebyzero or a numeric overflow occurs during the processing of an nondecimal floatingpoint expression, an error is returned (SQLSTATE 22003 or 22012). For decimal floatingpoint 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 nondecimal floatingpoint expression, the query returns a warning (SQLSTATE 01519 or 01564), and proceeds with processing the SQL statement.
For decimal floatingpoint expressions, the setting of dft_sqlmathwarn has no effect; arithmetic conditions return an appropriate value (possibly a decimal floatingpoint 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 floatingpoint 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.
 Add a case expression to check for dividebyzero and set the required value for such a situation
 Add additional predicates to handle nulls (like a check constraint
on not nullable columns could become:
to cause the constraint to be violated on an overflow).check (c1*c2 is not null and c1*c2>5000)
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
p
 Precision of the first operand.
s
 Scale of the first operand.
p'
 Precision of the second operand.
s'
 Scale of the second operand.
 Addition and subtraction
 The scale of the result is
MAX(s,s')
. If DEC15 mode is in effect andp<15
andp'<15
, the precision isMIN(15,MAX(ps,p's')+MAX(s,s')+1)
. Otherwise, the precision isMIN(31,MAX(ps,p's')+MAX(s,s')+1)
.  Multiplication
 If DEC15 mode is in effect and
p<15
andp'<15
, the precision isMIN(15,p+p')
and the scale isMIN(15,s+s')
. Otherwise the precision isMIN(31,p+p')
and the scale isMIN(31,s+s')
.  Division
 The following table shows the result precision and scale based on various factors.
Table 5. Precision and scale of the result of a decimal division DECIMAL arithmetic mode^{1} p p' Result precision Result scale default n/a n/a 31 31p+ss'
DEC15 <=15 <=15 15 15(ps+s')
DEC15 >15 <=15 31 N(ps+s')
, where:N
is30p' if p'
is oddN
is29p' if p'
is even
DEC31 n/a <=15 31 N(ps+s')
, where:N
is30p' if p'
is oddN
is29p' if p'
is even
DEC15 or DEC31 n/a >15 31 15(ps+MAX(0,s'(p'15)))
Note: These modes are determined by the dec_arithmetic configuration parameter.
If a minimum DECIMAL division scale S is in effect, then the scale is the minimum of S and the scale derived from Table 5. Otherwise, a negative scale results in an error (SQLSTATE 42911).
 Exponential
 The result type is DOUBLE.
Floatingpoint operands
If either operand of an arithmetic operator is floatingpoint, but not decimal floatingpoint, the operation is performed in floatingpoint. The operands are first converted to doubleprecision floatingpoint numbers, if necessary. Thus, if any element of an expression is a floatingpoint number, the result of the expression is a doubleprecision floatingpoint number.
An operation involving a floatingpoint number and an integer is performed with a temporary copy of the integer which has been converted to doubleprecision floatingpoint. An operation involving a floatingpoint number and a decimal number is performed with a temporary copy of the decimal number which has been converted to doubleprecision floatingpoint. The result of a floatingpoint operation must be within the range of floatingpoint numbers.
The order in which floatingpoint operands (or arguments to functions) are processed can slightly affect results because floatingpoint 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 floatingpoint operands should not depend on the results being precisely the same each time an SQL statement is executed.
Decimal floatingpoint operands
 Integer and decimal floatingpoint operands
 If one operand is a small integer or large integer and the other is a DECFLOAT(n) number, the operation is performed in DECFLOAT(n) using a temporary copy of the integer that has been converted to a DECFLOAT(n) number. If one operand is a big integer, and the other is a decimal floatingpoint number, a temporary copy of the big integer is converted to a DECFLOAT(34) number. The rules for twodecimal floatingpoint operands then apply.
 Decimal and decimal floatingpoint operands
 If one operand is a decimal and the other is a decimal floatingpoint number, the operation is performed in decimal floatingpoint using a temporary copy of the decimal number that has been converted to a decimal floatingpoint number based on the precision of the decimal number. If the decimal number has a precision less than 17, the decimal number is converted to a DECFLOAT(16) number; otherwise, the decimal number is converted to a DECFLOAT(34) number. The rules for twodecimal floatingpoint operands then apply.
 Floatingpoint and decimal floatingpoint operands
 If one operand is a floatingpoint number (REAL or DOUBLE) and the other is a DECFLOAT(n) number, the operation is performed in decimal floatingpoint using a temporary copy of the floatingpoint number that has been converted to a DECFLOAT(n) number.
 Two decimal floatingpoint operands
 If both operands are DECFLOAT(n), the operation is performed in DECFLOAT(n). If one operand is DECFLOAT(16) and the other is DECFLOAT(34), the operation is performed in DECFLOAT(34).
General arithmetic operation rules for decimal floatingpoint
 Every operation on finite numbers is carried out as though an
exact mathematical result is computed, using integer arithmetic on
the coefficient, where possible.
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 E_{min}, the calculated coefficient and exponent form the result, unless the value of the exponent is less than E_{tiny}, in which case the exponent is set to E_{tiny}, 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 E_{max}, 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.
 Arithmetic that uses the special value infinity follows the usual rules, where negative infinity
is less than every finite number and positive infinity is greater than every finite number. Under
these rules, an infinite result is always exact. Certain uses of infinity return an invalid
operation condition. The following list shows the operations that can cause an invalid operation
condition. The result of such an operation is a NaN when one of the operands is infinity but the
other operand is not a NaN (quiet or signalling).
 Add +infinity to infinity during an addition or subtraction operation
 Multiply 0 by +infinity or infinity
 Divide either +infinity or infinity by either +infinity or infinity
 Either argument of the QUANTIZE function is +infinity or infinity
 The second argument of the POWER function is +infinity or infinity
 A signaling NaN is an operand of an arithmetic operation
 The result of any arithmetic operation that has a NaN (quiet or signalling) operand is NaN. The sign of the result is copied from the first operand that is a signalling NaN; if neither operand is signalling, the sign is copied from the first operand that is a NaN. Whenever a result is a NaN, the sign of the result depends only on the copied operand.
 The sign of the result of a multiplication or division operation is negative only if the operands have different signs and neither is a NaN.
 The sign of the result of an addition or subtraction operation is negative only if the result is
less than zero and neither operand is a NaN, except for the following cases, in which the result is 0:
 A result is rounded to 0, and the value, before rounding, had a negative sign
 0 is subtracted from 0
 Operands with opposite signs are added, or operands with the same sign are subtracted; the result has a coefficient of 0, and the rounding mode is ROUND_FLOOR
 Operands are multiplied or divided, the result has a coefficient of 0, and the signs of the operands are different
 The first argument of the POWER function is 0, and the second argument is a positive odd number
 The argument of the CEIL, FLOOR, or SQRT function is 0
 The first argument of the ROUND or TRUNCATE function is 0
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
Userdefined 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 userdefined 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
.
Bitwise Operator  Name  Description 

&  BITAND  The result is a bit pattern in which each bit is the result of a logical AND operation performed on the corresponding bits of the input arguments. 
  BITOR  The result is a bit pattern in which each bit is the result of a logical OR operation performed on the corresponding bits of the input arguments. 
^  BITXOR  The result is a bit pattern in which each bit is the result of a logical XOR (exclusive OR) operation performed on the corresponding bits of the input arguments. 
Before a bitwise operation is performed, an operand that is a nonLOB 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
 Unary BITNOT (~)
 Unary positive (+) or unary negative () prefix
 Exponential (**)
 Multiplication (*), division (/), modulo (%), or concatenation (CONCAT or )
 Addition (+) or subtraction ()
 BITAND (&), BITOR (), or BITXOR (^).
 Predicates.
 Logical NOT.
 Logical AND.
 Logical OR.
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
SQL_COMPAT
global variable is set to 'NPS'
: The symbol ^ is interpreted as the exponential operator (equivalent to **) and not as the BITXOR operator. The symbol ** is also interpreted as the exponential operator.
 The symbol # is interpreted as the BITXOR operator.