An expression specifies a value.
.-operator---------------------------.
V |
>>----+-----+---+-function-----------+--+----------------------><
+- + -+ +-(expression)-------+
'- - -' +-constant-----------+
+-column-name--------+
+-host-variable------+
+-special-register---+
+-labeled-duration---+
+-cast-specification-+
'-case-expression----'
operator: >>-+-CONCAT-+-------------------------------------------------->< +- || ---+ +- / ----+ +- * ----+ +- ** ---+ +- + ----+ '- - ----' labeled-duration:
>>-+-function------+----+-YEAR---------+-----------------------><
+-(expression)--+ +-YEARS--------+
+-constant------+ +-MONTH--------+
+-column-name---+ +-MONTHS-------+
'-host-variable-' +-DAY----------+
+-DAYS---------+
+-HOUR---------+
+-HOURS--------+
+-MINUTE-------+
+-MINUTES------+
+-SECOND-------+
+-SECONDS------+
+-MICROSECOND--+
'-MICROSECONDS-'
|
If no operators are used, the result of the expression is the specified value.
SALARY :SALARY 'SALARY' MAX(SALARY)
The concatenation operator (CONCAT or ||) combines two strings. The result of the expression is a string.
The operands of concatenation must be compatible strings. Binary strings are only compatible with other binary strings.
The data type of the result is determined by the data types of the
operands. The data type of the result is summarized in the following
table:
Table 17. Result Data Types With Concatenation
| If one operand column is ... | And the other operand is ... | The data type of the result column is ... |
|---|---|---|
| DBCLOB(x) | CHAR(y) or VARCHAR(y) or CLOB(y) or GRAPHIC(y) or VARGRAPHIC(y) or DBCLOB(y) | DBCLOB(z) where z = x + y |
| CLOB(x) | GRAPHIC(y) or VARGRAPHIC(y) | DBCLOB(z) where z = x + y |
| VARGRAPHIC(x) | CHAR(y) or VARCHAR(y) or GRAPHIC(y) or VARGRAPHIC(y) | VARGRAPHIC(z) where z = x + y |
| VARCHAR(x) | GRAPHIC(y) | VARGRAPHIC(z) where z = x + y |
| GRAPHIC(x) | CHAR(y) mixed data | VARGRAPHIC(z) where z = x + y |
| GRAPHIC(x) | CHAR(y) SBCS data or GRAPHIC(y) | GRAPHIC(z) where z = x + y |
| UCS-2 data | UCS-2 or DBCS or mixed or SBCS data | UCS-2 data |
| DBCS data | DBCS or mixed or SBCS data | DBCS data |
| CLOB(x) | CHAR(y) or VARCHAR(y) or CLOB(y) | CLOB(z) where z = x + y |
| VARCHAR(x) | CHAR(y) or VARCHAR(y) | VARCHAR(z) where z = x + y |
| CHAR(x) mixed data | CHAR(y) | VARCHAR(z) where z = x + y |
| CHAR(x) SBCS data | CHAR(y) | CHAR(z) where z = x + y |
| bit data | mixed or SBCS or bit data | bit data |
| mixed data | mixed or SBCS data | mixed data |
| SBCS data | SBCS data | SBCS data |
| BLOB(x) | BLOB(y) | BLOB(z) where z = x + y |
The sum of the lengths of the operands must not exceed the maximum length attribute of the resulting 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.
With mixed data this result will not have redundant shift codes "at the seam". Thus, if the first operand is a string ending with a "shift-in" character (X'0F'), while the second operand is a character string beginning with a "shift-out" character (X'0E'), these two bytes are eliminated from the result.
The length of the result is the sum of the lengths of the operands unless redundant shifts are eliminated; in which case, the length is two less than the sum of the lengths of the operands.
The CONCAT operator should be used instead of the || operator. The code point for the | character varies, depending on the CCSID.
The CCSID of the result is determined by the CCSID of the operands as explained under Conversion Rules for Operations That Combine Strings. Note that as a result of these rules:
Concatenate the column FIRSTNME with a blank and the column LASTNAME.
FIRSTNME CONCAT ' ' CONCAT LASTNAME
If arithmetic operators are used, the result of the expression is a number derived from the application of the operators to the values of the operands.
If any operand can be null, the result can be null. If any operand has the null value, the result of the expression is the null value. Arithmetic operators must not be applied to character strings. For example, USER+2 is invalid.
The prefix operator + (unary plus) does not change its operand. The prefix operator - (unary minus) reverses the sign of a nonzero operand. 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, division, and exponentiation, respectively. The value of the second operand of division must not be zero.
The result of an exponentiation (**) operator is a double-precision floating-point number. The result of the other operators depends on the type of the operand.
If both operands of an arithmetic operator are integers with zero scale, 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 large integers. If either integer operand has nonzero scale, it is converted to a decimal operand with the same precision and scale.
If one operand is an integer with zero scale and the other is decimal, the
operation is performed in decimal using a temporary copy of the integer that
has been converted to a decimal number with precision and scale 0 as defined
in the following table:
| Operand | Precision of Decimal Copy |
|---|---|
| Column or variable: big integer | 19 |
| Column or variable: large integer | 11 |
| Column or variable: small integer | 5 |
| Constant (including leading zeros) | Same as the number of digits in the constant |
If one operand is an integer with nonzero scale, it is first converted to a decimal operand with the same precision and scale.
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.
Unless specified otherwise, all functions and operations that accept decimal numbers allow a precision of up to 31 digits. The result of a decimal operation must not have a precision greater than 31.
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 scale of the result of addition and subtraction is max (s,s'). The precision is min(31,max(p-s,p'-s') +max(s,s')+1).
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, 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 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 converted to double-precision floating point. The result of a floating-point operation must be within the range of floating-point numbers.
A user-defined type cannot be used with arithmetic operators even if its source data type is numeric. To perform an arithmetic operation, create a function with the arithmetic operator as its source. For example, if there were distinct types INCOME and EXPENSES, both of which had DECIMAL(8,2) data types, then the following user-defined function, REVENUE, could be used to subtract one from the other.
CREATE FUNCTION REVENUE ( INCOME, EXPENSES )
RETURNS DECIMAL(8,2) SOURCE "-" ( DECIMAL, DECIMAL)
Alternately, the - (minus) operator could be overloaded using a user-defined function to subtract the new data types.
CREATE FUNCTION "-" ( INCOME, EXPENSES )
RETURNS DECIMAL(8,2) SOURCE "-" ( DECIMAL, DECIMAL)
Datetime values can be incremented, decremented, and subtracted. These operations may involve decimal numbers called durations. A duration is a positive or negative number representing an interval of time. There are four types of durations:
The only arithmetic operations that can be performed on datetime values are addition and subtraction. If a datetime value is the operand of addition, the other operand must be a duration. The specific rules governing the use of the addition operator with datetime values follow:
The rules for the use of the subtraction operator on datetime values are not the same as those for addition because a datetime value cannot be subtracted from a duration, and because the operation of subtracting two datetime values is not the same as the operation of subtracting a duration from a datetime value. The specific rules governing the use of the subtraction operator with datetime values follow:
Dates can be subtracted, incremented, or decremented.
The result of subtracting one date (DATE2) from another (DATE1) is a
date duration that specifies the number of years, months, and days between the
two dates. The data type of the result is DECIMAL(8,0). If DATE1
is greater than or equal to DATE2, DATE2 is subtracted from DATE1. If
DATE1 is less than DATE2, however, DATE1 is subtracted from DATE2, and the
sign of the result is made negative. The following procedural
description clarifies the steps involved in the operation RESULT = DATE1 -
DATE2.
If DAY(DATE2) <= DAY(DATE1)
then DAY(RESULT) = DAY(DATE1) - DAY(DATE2).
If DAY(DATE2) > DAY(DATE1)
then DAY(RESULT) = N + DAY(DATE1) - DAY(DATE2)
where N = the last day of MONTH(DATE2).
MONTH(DATE2) is then incremented by 1.
If MONTH(DATE2) <= MONTH(DATE1)
then MONTH(RESULT) = MONTH(DATE1) - MONTH(DATE2).
If MONTH(DATE2) > MONTH(DATE1)
then MONTH(RESULT) = 12 + MONTH(DATE1) - MONTH(DATE2).
YEAR(DATE2) is then incremented by 1.
YEAR(RESULT) = YEAR(DATE1) - YEAR(DATE2).
For example, the result of DATE('3/15/2000') - '12/31/1999' is 215 (or, a duration of 0 years, 2 months, and 15 days).
The result of adding a duration to a date, or of subtracting a duration from a date, is itself a date. (For the purposes of this operation, a month denotes the equivalent of a calendar page. Adding months to a date, then, is like turning the pages of a calendar, starting with the page on which the date appears.) The result must fall between the dates January 1, 0001 and December 31, 9999 inclusive. If a duration of years is added or subtracted, only the year portion of the date is affected. The month is unchanged, as is the day unless the result would be February 29 of a non-leap-year. In this case, the day is changed to 28, and SQLWARN6 in the SQLCA is set to 'W' to indicate the end-of-month adjustment.
Similarly, if a duration of months is added or subtracted, only months and, if necessary, years are affected. The day portion of the date is unchanged unless the result would be invalid (September 31, for example). In this case, the day is set to the last day of the month, and SQLWARN6 in the SQLCA is set to 'W' to indicate the end-of-month adjustment.
Adding or subtracting a duration of days will, of course, affect the day portion of the date, and potentially the month and year. Adding a labeled duration of DAYS will not cause an end-of-month adjustment.
Date durations, whether positive or negative, may also be added to and subtracted from dates. As with labeled durations, the result is a valid date, and a warning indicator is set in the SQLCA whenever an end-of-month adjustment is necessary.
When a positive date duration is added to a date, or a negative date duration is subtracted from a date, the date is incremented by the specified number of years, months, and days, in that order. Thus DATE1 + X, where X is a positive DECIMAL(8,0) number, is equivalent to the expression:
DATE1 + YEAR(X) YEARS + MONTH(X) MONTHS + DAY(X) DAYS
When a positive date duration is subtracted from a date, or a negative date duration is added to a date, the date is decremented by the specified number of days, months, and years, in that order. Thus, DATE1 - X, where X is a positive DECIMAL(8,0) number, is equivalent to the expression:
DATE1 - DAY(X) DAYS - MONTH(X) MONTHS - YEAR(X) YEARS
When adding durations to dates, adding one month to a given date gives the same date one month later unless that date does not exist in the later month. In that case, the date is set to that of the last day of the later month. For example, January 28 plus one month gives February 28; and one month added to January 29, 30, or 31 results in either February 28 or, for a leap year, February 29.
| Note: | If one or more months is added to a given date and then the same number of months is subtracted from the result, the final date is not necessarily the same as the original date. |
Times can be subtracted, incremented, or decremented.
The result of subtracting one time (TIME2) from another (TIME1) is a
time duration that specifies the number of hours, minutes, and seconds between
the two times. The data type of the result is DECIMAL(6,0). If
TIME1 is greater than or equal to TIME2, TIME2 is subtracted from
TIME1. If TIME1 is less than TIME2, however, TIME1 is subtracted from
TIME2, and the sign of the result is made negative. The following
procedural description clarifies the steps involved in the operation RESULT =
TIME1 - TIME2.
If SECOND(TIME2) <= SECOND(TIME1)
then SECOND(RESULT) = SECOND(TIME1) - SECOND(TIME2).
If SECOND(TIME2) > SECOND(TIME1)
then SECOND(RESULT) = 60 + SECOND(TIME1) - SECOND(TIME2).
MINUTE(TIME2) is then incremented by 1.
If MINUTE(TIME2) <= MINUTE(TIME1)
then MINUTE(RESULT) = MINUTE(TIME1) - MINUTE(TIME2).
If MINUTE(TIME2) > MINUTE(TIME1)
then MINUTE(RESULT) = 60 + MINUTE(TIME1) - MINUTE(TIME2).
HOUR(TIME2) is then incremented by 1.
HOUR(RESULT) = HOUR(TIME1) - HOUR(TIME2).
For example, the result of TIME('11:02:26') - '00:32:56' is 102930 (a duration of 10 hours, 29 minutes, and 30 seconds).
The result of adding a duration to a time, or of subtracting a duration from a time, is itself a time. Any overflow or underflow of hours is discarded, thereby ensuring that the result is always a time. If a duration of hours is added or subtracted, only the hours portion of the time is affected. The minutes and seconds are unchanged.
Similarly, if a duration of minutes is added or subtracted, only minutes and, if necessary, hours are affected. The seconds portion of the time is unchanged.
Adding or subtracting a duration of seconds will, of course, affect the seconds portion of the time, and potentially the minutes and hours.
Time durations, whether positive or negative, also can be added to and subtracted from times. The result is a time that has been incremented or decremented by the specified number of hours, minutes, and seconds, in that order. TIME1 + X, where "X" is a DECIMAL(6,0) number, is equivalent to the expression:
TIME1 + HOUR(X) HOURS + MINUTE(X) MINUTES + SECOND(X) SECONDS
Timestamps can be subtracted, incremented, or decremented.
The result of subtracting one timestamp (TS2) from another (TS1) is a
timestamp duration that specifies the number of years, months, days, hours,
minutes, seconds, and microseconds between the two timestamps. The data
type of the result is DECIMAL(20,6). If TS1 is greater than or equal to
TS2, TS2 is subtracted from TS1. If TS1 is less than TS2, however, TS1
is subtracted from TS2 and the sign of the result is made negative. The
following procedural description clarifies the steps involved in the operation
RESULT = TS1 - TS2.
If MICROSECOND(TS2) <= MICROSECOND(TS1)
then MICROSECOND(RESULT) = MICROSECOND(TS1) -
MICROSECOND(TS2).
If MICROSECOND(TS2) >MICROSECOND(TS1)
then MICROSECOND(RESULT) = 1000000 +
MICROSECOND(TS1) - MICROSECOND(TS2)
and SECOND(TS2) is incremented by 1.
The seconds and minutes part of the timestamps are subtracted as specified
in the rules for subtracting times.
If HOUR(TS2) <= HOUR(TS1)
then HOUR(RESULT) = HOUR(TS1) - HOUR(TS2).
If HOUR(TS2) > HOUR(TS1)
then HOUR(RESULT) = 24 + HOUR(TS1) - HOUR(TS2)
and DAY(TS2) is incremented by 1.
The date part of the timestamps is subtracted as specified
in the rules for subtracting dates.
The result of adding a duration to a timestamp, or of subtracting a duration from a timestamp, is itself a timestamp. Date and time arithmetic is performed as previously defined, except that an overflow or underflow of hours is carried into the date part of the result, which must be within the range of valid dates. Microseconds overflow into seconds.
Expressions within parentheses are evaluated first. When the
order of evaluation is not specified by parentheses, exponentiation is applied
after prefix operators (such as -, unary minus) and before multiplication and
division. Multiplication and division are applied before addition and
subtraction. Operators at the same precedence level are applied from
left to right. The following table shows the priority of all
operators.
| Priority | Operators |
|---|---|
| 1 | +, - (when used for signed numeric values) |
| 2 | ** |
| 3 | *, /, CONCAT, || |
| 4 | +, - (when used between two operands) |
In the following example, operators are applied in the order shown by the numbers in the second row.
1.10 * (SALARY + BONUS) + SALARY / :VAR3
2 1 4 3
.-ELSE NULL----------------.
>>-CASE--+-searched-when-clause-+---+--------------------------+---END-->
'-simple-when-clause---' '-ELSE--result-expression--'
>--------------------------------------------------------------><
searched-when-clause
.-------------------------------------------------------.
V |
|------WHEN--search-condition--THEN--+-result-expression-+--+---|
'-NULL--------------'
simple-when-clause
|---expression-------------------------------------------------->
.-------------------------------------------------.
V |
>--------WHEN--expression--THEN--+-result-expression-+--+-------|
'-NULL--------------'
|
CASE expressions allow an expression to be selected based on the evaluation of one or more conditions. In general, the value of the case-expression is the value of the result-expression following the first (leftmost) when-clause that evaluates to true. If no when-clause evaluates to true and the ELSE keyword is present then the result is the value of the ELSE result-expression or NULL. If no when-clause evaluates to true and the ELSE keyword is not present then the result is NULL. Note that when a when-clause evaluates to unknown (because of nulls), the when-clause is not true and hence is treated the same way as a when-clause that evaluates to false.
The search-condition in a searched-when-clause cannot contain a basic predicate with a subselect, a quantified predicate, an IN predicate using a subselect, or an EXISTS predicate.
When using the simple-when-clause, the value of the expression prior to the first WHEN keyword is tested for equality with the value of the expression following the WHEN keyword(s). The data type of the expression prior to the first WHEN keyword must therefore be compatible with the data types of each expression following the WHEN keyword(s).
A result-expression is an expression following the THEN or ELSE keywords. There must be at least one result-expression in the CASE expression (NULL cannot be specified for every case). All result-expressions must have compatible data types, where the attributes of the result are determined based on the "Rules for Result Data Types".
There are two scalar functions, NULLIF and COALESCE, that are specialized
to handle a subset of the functionality provided by CASE. The following
table shows the equivalent expressions using CASE or these functions.
Table 18. Equivalent CASE Expressions
| CASE Expression | Equivalent Expression |
|---|---|
| CASE WHEN e1=e2 THEN NULL ELSE e1 END | NULLIF(e1,e2) |
| CASE WHEN e1 IS NOT NULL THEN e1 ELSE e2 END | COALESCE(e1,e2) |
| CASE WHEN e1 IS NOT NULL THEN e1 ELSE COALESCE(e2,...,eN) END | COALESCE(e1,e2,...,eN) |
SELECT EMPNO, LASTNAME, CASE SUBSTR(WORKDEPT,1,1) WHEN 'A' THEN 'Administration' WHEN 'B' THEN 'Human Resources' WHEN 'C' THEN 'Accounting' WHEN 'D' THEN 'Design' WHEN 'E' THEN 'Operations' END FROM EMPLOYEE
SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, CASE WHEN EDLEVEL < 15 THEN 'SECONDARY' WHEN EDLEVEL < 19 THEN 'COLLEGE' ELSE 'POST GRADUATE' END FROM EMPLOYEE
SELECT EMPNO, WORKDEPT, SALARY+COMM
FROM EMPLOYEE
WHERE (CASE WHEN SALARY=0 THEN NULL
ELSE COMM/SALARY
END) > 0.25
SELECT LASTNAME, CASE WHEN LASTNAME = 'Haas' THEN 'President' ... SELECT LASTNAME, CASE LASTNAME WHEN 'Haas' THEN 'President' ...
(1)
>>-CAST--(--+-expression-------+---AS--data-type-------)-------><
+-NULL-------------+
'-parameter-marker-'
Notes:
data-type |--+---BIGINT--------------------------------------------------------------------------------+-> +-+-INTEGER-+-----------------------------------------------------------------------------+ | '-INT-----' | +---SMALLINT------------------------------------------------------------------------------+ +--+-DECIMAL-+---+-------------------------------+----------------------------------------+ | +-DEC-----+ '-(--integer--+-----------+--)--' | | '-NUMERIC-' '-, integer-' | +-+-FLOAT--+----------------+-+-----------------------------------------------------------+ | | '-(--integer--)--' | | | +-REAL----------------------+ | | '-DOUBLE--+-----------+-----' | | '-PRECISION-' | +----+-BLOB----------------+-----+------------------------+-------------------------------+ | '-BINARY LARGE OBJECT-' '-(--integer--+---+---)--' | | +-K-+ | | '-M-' | +--+--+-CHARACTER-+---+----------------+--------------------------+---+-----------------+-+ | | '-CHAR------' '-(--integer--)--' | +-FOR BIT DATA----+ | | +-----+-VARCHAR-------------------+---(--integer--)------------+ +-FOR SBCS DATA---+ | | | '--+-CHARACTER-+---VARYING--' | +-FOR MIXED DATA--+ | | | '-CHAR------' | '-CCSID--integer--' | | '----+-CLOB-------------------+-----+------------------------+-' | | +-CHAR LARGE OBJECT------+ '-(--integer--+---+---)--' | | '-CHARACTER LARGE OBJECT-' +-K-+ | | '-M-' | +--+----GRAPHIC-----+----------------+----------+---+-----------------+-------------------+ | | '-(--integer--)--' | '-CCSID--integer--' | | +-----+-VARGRAPHIC------+---(--integer--)----+ | | | '-GRAPHIC VARYING-' | | | '----DBCLOB------+------------------------+--' | | '-(--integer--+---+---)--' | | +-K-+ | | '-M-' | +---DATE----------------------------------------------------------------------------------+ +---TIME----------------------------------------------------------------------------------+ +---TIMESTAMP-----------------------------------------------------------------------------+ +---DATALINK--+----------------+---+-----------------+------------------------------------+ | '-(--integer--)--' '-CCSID--integer--' | '---distinct-type-------------------------------------------------------------------------' >---------------------------------------------------------------| |
The CAST specification returns the cast operand (the first operand) cast to the type specified by the data type. If the data type of either operand is a distinct type, the privileges held by the authorization ID of the statement must include USAGE authority on the distinct type.
The supported casts are shown in Table 9, where the first column represents the data type of the cast operand (source data type) and the data types across the top represent the target data type of the CAST specification. If the cast is not supported, an error will occur.
When casting character or graphic strings to a character or graphic string with a different length, a warning is returned if truncation of other than trailing blanks occurs.
If a data type has length, precision, scale, or CCSID attributes, specify the attributes. If the attributes are not specified, the default values are used. For example, the default for CHAR is a length of 1, and the default for DECIMAL is a precision of 5 and a scale of 0. For the default values of the other data types, see CREATE TABLE. (For portability across operating systems, when specifying a floating-point data type, use REAL or DOUBLE instead of FLOAT.)
For information on which casts between data types are supported and the rules for casting to a data type see Casting Between Data Types.
SELECT EMPNO, CAST(SALARY AS INTEGER) FROM EMPLOYEE
UPDATE PERSONNEL SET RETIRE_YEAR = ? WHERE AGE = CAST( ? AS T_AGE )