A function is an operation denoted by a function name followed by one or more operands which are enclosed in parentheses. The operands of functions are called arguments. Most functions have a single argument that is specified by an expression. The result of a function is a single value derived by applying the function to the result of the expression.
Functions are classified as column functions or scalar functions. The argument of a column function is a set of values. An argument of a scalar function is a single value. If multiple arguments are allowed, each argument is a single value.
In the syntax of SQL, the term function is used only in the definition of an expression. Thus a function can be used only where an expression can be used. Additional restrictions apply to the use of column functions as specified in the following section and in Chapter 5, Queries.
The following information applies to all column functions, except for the COUNT(*) variation of the COUNT function.
The argument of a column function is a set of values derived from one or more columns. The scope of the set is a group or an intermediate result table as explained in Chapter 5, Queries. For example, the result of the following SELECT statement is the number of distinct values of JOB for employees in department D01:
SELECT COUNT(DISTINCT JOB)
FROM EMPLOYEE
WHERE WORKDEPT = 'D01'
The keyword DISTINCT is not considered an argument of the function but rather a specification of an operation that is performed before the function is applied. If DISTINCT is specified, duplicate values are eliminated. If ALL is implicitly or explicitly specified, duplicate values are not eliminated.
The DISTINCT operation can only be applied to values of a column. If DISTINCT is omitted, the values of the arguments are specified by an expression. That expression must not include a column function and must include at least one column-name, a requirement that is not satisfied by a reference to a view column derived from a constant or expression without a column-name. If a column_name is a correlated reference (which is allowed in a subquery of a HAVING clause) the expression must not include operators.
.-ALL-.
>>-AVG--(----+-+-----+--numeric_expression--+--)---------------><
'-DISTINCT--column_name--------'
|
The AVG function returns the average of a set of numbers.
The argument values must be numbers and their sum must be within the range of the data type of the result. The result can be null.
The data type of the result is the same as the data type of the argument values, except that:
If the data type of the argument values is decimal with precision p and scale s, the precision of the result is 31 and the scale is 31-p+s. Negative scale is not allowed.
The function is applied to the set of values derived from the argument values by the elimination of null values. If DISTINCT is specified, duplicate values are eliminated.
If the function is applied to an empty set, the result is a null value. Otherwise, the result is the average value of the set.
The order in which the summation part of the operation is performed is undefined, but every intermediate result must be within the range of the result data type.
If the type of the result is integer, the fractional part of the average is lost.
Using the PROJECT table, set the host variable AVERAGE (decimal(5,2)) to the average staffing level (PRSTAFF) of projects in department (DEPTNO) 'D11'.
SELECT AVG(PRSTAFF)
INTO :AVERAGE
FROM PROJECT
WHERE DEPTNO = 'D11'
Results in AVERAGE being set to 4.25 (that is, 17 / 4) when using the sample table.
Using the PROJECT table, set the host variable ANY_CALC to the average of each unique staffing level value (PRSTAFF) of projects in department (DEPTNO) 'D11'.
SELECT AVG(DISTINCT PRSTAFF)
INTO :ANY_CALC
FROM PROJECT
WHERE DEPTNO = 'D11'
Results in ANY_CALC being set to 4.66 (that is, 14 / 3) when using the sample table.
>>-COUNT----+-(--DISTINCT--column_name--)--+-------------------><
'-(*)--------------------------'
|
The COUNT function returns the number of rows or values in a set of rows or values.
The column_name must not identify a long string column. The result of the function is a large integer and must be within the range of large integers. The result cannot be null.
The argument of COUNT(*) is a set of rows. The result is the number of rows in the set.
The argument of COUNT(DISTINCT column_name) is a set of values. The function is applied to the set of values derived from the argument values by the elimination of null values and duplicate values. The result is the number of values in the set.
Using the EMPLOYEE TABLE, set the host variable FEMALE (int) to the number of rows where the value of the SEX column is 'F'.
SELECT COUNT(*)
INTO :FEMALE
FROM EMPLOYEE
WHERE SEX = 'F'
Results in FEMALE being set to 13 when using the sample table.
Using the EMPLOYEE table, set the host variable FEMALE_IN_DEPT (int) to the number of departments (WORKDEPT) that have at least one female as a member.
SELECT COUNT(DISTINCT WORKDEPT)
INTO :FEMALE_IN_DEPT
FROM EMPLOYEE
WHERE SEX = 'F'
Results in FEMALE_IN_DEPT being set to 5 when using the sample table. (There is at least one female in departments A00, C01, D11, D21, and E11.)
.-ALL-.
>>-MAX--(----+-+-----+--expression---------+--)----------------><
| (1) |
'-DISTINCT-------column_name--'
Notes:
|
The MAX function returns the maximum value in a set of values.
The argument values can be any values other than long strings.
The data type and length attribute of the result are the same as the data type and length attribute of the argument values. When the argument is a string, the result has the same CCSID as the argument. The result can be null.
The function is applied to the set of values derived from the argument values by the elimination of null values.
If the function is applied to an empty set, the result is a null value. Otherwise, the result is the maximum value in the set.
Using the EMPLOYEE table, set the host variable MAX_SALARY (decimal(7,2)) to the maximum monthly salary (SALARY / 12) value.
SELECT MAX(SALARY) /12 INTO :MAX_SALARY FROM EMPLOYEE
Results in MAX_SALARY being set to 4395.83 when using the sample table.
Using the PROJECT table, set the host variable LAST_PROJ (char(24)) to the project name (PROJNAME) that comes last in the collating sequence.
SELECT MAX(PROJNAME)
INTO :LAST_PROJ
FROM PROJECT
Results in LAST_PROJ being set to 'WELD LINE PLANNING' when using the sample table.
.-ALL-.
>>-MIN--(----+-+-----+--expression---------+--)----------------><
| (1) |
'-DISTINCT-------column_name--'
Notes:
|
The MIN function returns the minimum value in a set of values.
The argument values can be any values other than long strings.
The data type and length attribute of the result are the same as the data type and length attribute of the argument values. When the argument is a string, the result has the same CCSID as the argument. The result can be null.
The function is applied to the set of values derived from the argument values by the elimination of null values.
If the function is applied to an empty set, the result is a null value. Otherwise, the result is the minimum value in the set.
Using the EMPLOYEE table, set the host variable COMM_SPREAD (decimal(7,2)) to the difference between the maximum and minimum commission (COMM) for the members of department (WORKDEPT) 'D11'.
SELECT MAX(COMM) - MIN(COMM)
INTO :COMM_SPREAD
FROM EMPLOYEE
WHERE WORKDEPT = 'D11'
Results in COMM_SPREAD being set to 1118 (that is, 2580 - 1462) when using the sample table.
Using the PROJECT table, set the host variable FIRST_FINISHED (char(10)) to the estimated ending date (PRENDATE) of the first project scheduled to be completed.
SELECT MIN(PRENDATE)
INTO :FIRST_FINISHED
FROM PROJECT
Results in FIRST_FINISHED being set to '1982-09-15' when using the sample table.
.-ALL-.
>>-SUM--(----+-+-----+--numeric_expression--+--)---------------><
'-DISTINCT--column_name--------'
|
The SUM function returns the sum of a set of numbers.
The argument values must be numbers and their sum must be within the range of the data type of the result.
The data type of the result is the same as the data type of the argument values except that the result is a large integer if the argument values are small integers and double precision floating-point if the argument values are single precision floating-point. If the data type of the argument values is decimal, the precision of the result is 31 and the scale is the same as the scale of the argument values. The result can be null.
The function is applied to the set of values derived from the argument values by the elimination of null values. If DISTINCT is specified, duplicate values are eliminated.
If the function is applied to an empty set, the result is a null value. Otherwise, the result is the sum of the values in the set.
Using the EMPLOYEE table, set the host variable JOB_BONUS (decimal(9,2)) to the total bonus (BONUS) paid to clerks (JOB='CLERK').
SELECT SUM(BONUS)
INTO :JOB_BONUS
FROM EMPLOYEE
WHERE JOB = 'CLERK'
Results in JOB_BONUS being set to 2800 when using the sample table.
Assume that a table SALES has the following columns and values:
| Name: | CUSTOMER | SALES | MONTHS |
|---|---|---|---|
| Type: | smallint | int | int |
| Desc: | Customer number | Total value of purchases by this customer | Number of months that customer has bought something |
| Values: | 101 | 1000 | 5 |
|
| 102 | 500 | 1 |
|
| 103 | 300 | 3 |
Set the host variable TOT_AVG_SALE (integer) to the sum of the average monthly sales per customer.
SELECT SUM(SALES / MONTHS)
INTO :TOT_AVG_SALE
FROM SALES
Results in TOT_AVG_SALE being set to 800. Note that the expression for each row is calculated before it is added to the sum.
A scalar function can be used wherever an expression can be used. The restrictions on the use of column functions do not apply to scalar functions. For example, the argument of a scalar function can be a function; that is, scalar functions can be nested. However, the restrictions that apply to the use of expressions and column functions also apply when an expression or column function is used within a scalar function. For example, the argument of a scalar function can be a column function only if a column function is allowed in the context in which the scalar function is used.
The restrictions on the use of column functions do not apply to scalar functions because a scalar function is applied to a single value rather than a set of values. For example, the result of the following SELECT statement has as many rows as there are employees in department D11:
SELECT EMPNO, LASTNAME, YEAR(CURRENT DATE - BIRTHDATE)
FROM EMPLOYEE
WHERE WORKDEPT = 'D11'
>>-CHAR---(--+--+-date_expression-+---+--------+-+---)---------><
| '-time_expression-' +-,ISO---+ |
| +-,USA---+ |
| +-,EUR---+ |
| +-,JIS---+ |
| '-,LOCAL-' |
+-timestamp_expression--------------+
'-decimal_expression----------------'
|
The CHAR function returns a string representation of a datetime value or decimal value.
The first argument must be a decimal number, timestamp, date, or time. The second argument, if applicable, is the name of a string format.
The result of the function is a fixed-length character string. The CCSID of the string is the default CCSID (based on the default subtype value, CHARSUB) of the application server. If the first argument can be null, the result can be null; if the first argument is null, the result is the null value.
The other rules depend on the data type of the first argument:
The second argument must not be specified. The result is the fixed length character string representation of the argument. The first character of the result is a minus sign if the argument is negative; otherwise, the first character is blank.
The result includes a decimal point, sign, and p digits, where p is the precision of the argument. The length of the result is 2+p.
The second argument is not applicable and must not be specified.
The result is the character string representation of the timestamp. The length of the result is 26.
Omission of the second argument is an implicit specification of the string format specified at installation time. The installation default for the string format can be overridden by preprocessor options. If LOCAL is implicitly or explicitly specified, a date installation exit must be installed.
The result is the character string representation of the date in the format specified by the second argument. If LOCAL is specified, the length of the result is the length specified in the SYSOPTIONS catalog during start-up. Otherwise, the length of the result is 10.
Omission of the second argument is an implicit specification of the string format specified at installation time. The installation default can be overridden by preprocessor options. If LOCAL is implicitly or explicitly specified, a time exit must be installed.
The result is the character string representation of the time in the format specified by the second argument. If LOCAL is specified, the length of the result is the length specified in the SYSOPTIONS catalog during start-up. Otherwise, the length of the result is 8.
Assume the column PRSTDATE has an internal value equivalent to 1988-12-25.
CHAR(PRSTDATE, USA)
Results in the value '12/25/1988'.
Assume the column STARTING has an internal value equivalent to 17.12.30, and the host variable HOUR_DUR (decimal(6,0)) is a time duration with a value of 050000 (that is, 5 hours).
CHAR(STARTING, USA)
Results in the value '5:12 PM'.
CHAR(STARTING + :HOUR_DUR, USA)
Results in the value '10:12 PM'.
Assume the column RECEIVED (timestamp) has an internal value equivalent to the combination of the PRSTDATE and STARTING columns.
CHAR(RECEIVED)
Results in the value '1988-12-25-17.12.30.000000'.
>>-DATE--(--expression--)-------------------------------------->< |
The DATE function returns a date from a value.
The argument must be a timestamp, a date, a positive number less than or equal to 3652059, a valid string representation of a date, or a character string of length 7.
If the argument is a character string of length 7, it must represent a valid date in the form yyyynnn, where yyyy are digits denoting a year, and nnn are digits between 001 and 366 denoting a day of that year.
The result of the function is a date. The data type is DATE. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The other rules depend on the data type of the argument:
The result is the date part of the timestamp.
The result is that date.
The result is the date that is n-1 days after January 1, 0001, where n is the number that would occur if the INTEGER function were applied to the argument.
The result is the date represented by the character string.
Notes:
Assume that the column RECEIVED (timestamp) has an internal value equivalent to '1988-12-25-17.12.30.000000'.
DATE(RECEIVED)
Results in an internal representation of '1988-12-25'.
DATE('1988-12-25')
Results in an internal representation of '1988-12-25'.
DATE('25.12.1988')
Results in an internal representation of '1988-12-25'.
DATE(35)
Results in an internal representation of '0001-02-04'.
>>-DAY--(----+-date_expression---------------+--)--------------><
+-timestamp_expression----------+
+-date_duration_expression------+
'-timestamp_duration_expression-'
|
The DAY function returns the day part of a value.
The argument must be a date, timestamp, date duration, or timestamp duration. If a decimal number, the argument must be:
to be properly interpreted.
The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The other rules depend on the data type of the argument:
The result is the day part of the value, which is an integer between 1 and 31.
The result is the day part of the value, which is an integer between -99 and 99. A nonzero result has the same sign as the argument.
Using the PROJECT table, set the host variable END_DAY (smallint) to the day that the WELD LINE PLANNING project (PROJNAME) is scheduled to stop (PRENDATE).
SELECT DAY(PRENDATE)
INTO :END_DAY
FROM PROJECT
WHERE (PROJNAME) = 'WELD LINE PLANNING'
Results in END_DAY being set to 15 when using the sample table.
Assume that the column DATE1 (date) has an internal value equivalent to 2000-03-15 and the column DATE2 (date) has an internal value equivalent to 1999-12-31.
DAY(DATE1 - DATE2)
Results in the value 15.
>>-DAYS--(----+-date_expression--------+--)--------------------><
+-timestamp_expression---+
'-date_string_expression-'
|
The DAYS function returns an integer representation of a date.
The argument must be a date, a timestamp, or a valid string representation of a date.
Notes:
The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The result is 1 more than the number of days from January 1, 0001 to D, where D is the date that would occur if the DATE function were applied to the argument.
Using the PROJECT table, set the host variable EDUCATION_DAYS (int) to the number of elapsed days (PRENDATE - PRSTDATE) estimated for the project (PROJNO) 'IF2000'.
SELECT DAYS(PRENDATE) - DAYS(PRSTDATE)
INTO :EDUCATION_DAYS
FROM PROJECT
WHERE (PROJNO) = 'IF2000'
Results in EDUCATION_DAYS being set to 396 when using the sample table.
Using the PROJECT table, set the host variable TOTAL_DAYS (int) to the sum of elapsed days (PRENDATE - PRSTDATE) estimated for all projects in department (DEPTNO) 'E21'.
SELECT SUM(DAYS(PRENDATE) - DAYS(PRSTDATE))
INTO :TOTAL_DAYS
FROM PROJECT
WHERE (DEPTNO) = 'E21'
Results in TOTAL_DAYS being set to 1584 when using the sample table.
>>-DECIMAL--(--numeric_expression------------------------------->
>-----+-----------------------------------------+--)-----------><
| .-,0-------------. |
'-,precision_integer--+----------------+--'
'-,scale_integer-'
|
The DECIMAL function returns a decimal representation of a number.
The default for the precision_integer depends on the data type of the numeric_expression:
The result of the function is a decimal number with precision of p and scale of s, where p and s are the second and third arguments. If the first argument can be null, the result can be null; if the first argument is null, the result is the null value.
The result is the same number that would occur if the first argument were assigned to a decimal column or variable with a precision of p and a scale of s. An error occurs if the number of significant decimal digits required to represent the whole part of the number is greater than p-s.
Use the DECIMAL function in order to force a DECIMAL data type (with a precision of 5 and a scale of 2) to be returned in a select-list for the EDLEVEL column (data type = SMALLINT) in the EMPLOYEE table. The EMPNO column should also appear in the select list.
SELECT EMPNO, DECIMAL(EDLEVEL,5,2)
FROM EMPLOYEE
Assume the host variable PERIOD is of type INTEGER. Then, in order to use its value as a date duration it must be "cast" as decimal(8,0).
SELECT PRSTDATE + DECIMAL(:PERIOD,8)
FROM PROJECT
>>-DIGITS--(----+-integer_expression-+--)----------------------><
'-decimal_expression-'
|
The DIGITS function returns a character string representation of a number.
The argument is an expression that returns a value of an integer, small integer, or decimal data type.
The result of the function is a fixed-length character string. The CCSID of the string is the default CCSID (based on the default subtype value, CHARSUB) of the application server. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The result is a string of digits that represents the absolute value of the argument without regard to its scale. Thus, the result does not include a sign or a decimal point. The result includes any necessary leading zeros so that the length of the string is:
Using the EMP_ACT table, set the host variable TIME_DISPLAY (char(5)) to the time (EMPTIME) that employee number (EMPNO) '000130' is to spend on an activity (ACTNO) 90.
SELECT DIGITS(EMPTIME)
INTO :TIME_DISPLAY
FROM EMP_ACT
WHERE EMPNO = '000130' AND ACTNO = 90
TIME_DISPLAY will be set to '00100' when using the sample table.
Return activity number (ACTNO) from the EMP_ACT table as a character string in a select list. The EMPNO and PROJNO columns should also appear in the select list.
SELECT EMPNO, PROJNO, DIGITS(ACTNO)
FROM EMP_ACT
>>-FLOAT--(--numeric_expression--)----------------------------->< |
The FLOAT function returns a floating-point representation of a number.
The argument is an expression that returns a value of any numeric data type.
The result of the function is a double precision floating-point number. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The result is the same number that would occur if the argument were assigned to a double precision floating-point column or variable.
Using the EMPLOYEE table, find the ratio of salary to commission for employees whose commission is not zero. The columns involved (SALARY and COMM) have DECIMAL data types. To eliminate the possibility of out-of-range results, FLOAT is applied to SALARY so that the division is carried out in floating point:
SELECT EMPNO, FLOAT(SALARY)/COMM
FROM EMPLOYEE
WHERE COMM > 0
>>-HEX--(expression)------------------------------------------->< |
The HEX function returns a hexadecimal representation of a value.
The argument is an expression that returns a value of any data type other than a long string.
The result of the function is a character string. The CCSID of the string is the default CCSID (based on the default subtype value, CHARSUB) of the application server. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The result is a string of hexadecimal digits. The first two represent the first byte of the argument, the next two represent the second byte of the argument, and so forth. If the argument is a datetime value, the result is the hexadecimal representation of the internal form of the argument.
If the argument is a single-byte character string (SBCS), the length of the argument must be 127 or less, and the length of the result is twice the defined (maximum) length of the argument. If the argument is a double-byte character string (DBCS), the length of the argument must be 63 or less, and the length of the result is four times the defined (maximum) length of the argument.
The result is fixed-length if the argument is fixed length. If the argument is varying-length, the result is also varying-length.
Using the DEPARTMENT table set the host variable HEX_MGRNO (char(12)) to the hexadecimal representation of the manager number (MGRNO) for the 'PLANNING' department (DEPTNAME).
SELECT HEX(MGRNO)
INTO :HEX_MGRNO
FROM DEPARTMENT
WHERE DEPTNAME = 'PLANNING'
HEX_MGRNO will be set to 'F0F0F0F0F2F0' when using the sample table.
Suppose COL_1 is a column with a data type of char(1) and a value of 'B'. The hexadecimal representation of the letter 'B' is X'C2'. HEX(COL_1) returns a two-character string 'C2'.
Suppose COL_3 is a column with a data type of decimal(6,2) and a value of 40.1. HEX(COL_3) returns the internal representation, an eight-character string '0004010C'.
>>-HOUR--(----+-time_expression---------------+--)-------------><
+-timestamp_expression----------+
+-time_duration_expression------+
'-timestamp_duration_expression-'
|
The HOUR function returns the hour part of a value.
The argument must be a time, timestamp, time duration, or timestamp duration. If a decimal number, the argument must be:
to be properly interpreted.
The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The other rules depend on the data type of the argument:
The result is the hour part of the value, which is an integer between 0 and 24.
The result is the hour part of the value, which is an integer between -99 and 99. A nonzero result has the same sign as the argument.
Using the CL_SCHED sample table, select all the classes that start in the afternoon.
SELECT * FROM CL_SCHED
WHERE HOUR(STARTING) BETWEEN 12 AND 17
>>-INTEGER--(--numeric_expression--)--------------------------->< |
The INTEGER function returns an integer representation of a number.
The argument is an expression that returns a value of any numeric data type.
The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The result is the same number that would occur if the argument were assigned to a large integer column or variable. If the whole part of the argument is not within the range of integers, an error occurs. The decimal part of the argument is truncated if present.
Using the EMPLOYEE table, select a list containing salary (SALARY) divided by education level (EDLEVEL). Truncate any decimal in the calculation. The list should also contain the values used in the calculation and employee number (EMPNO). The list should be in descending order of the calculated value.
SELECT INTEGER(SALARY / EDLEVEL), SALARY, EDLEVEL, EMPNO
FROM EMPLOYEE
ORDER BY 1 DESC
>>-LENGTH--(--expression--)------------------------------------>< |
The LENGTH function returns the length of a value.
The argument is an expression that returns a value of any data type. The expression cannot be a long string host variable.
The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The result is the length of the argument. The length of strings includes blanks. The length of a varying-length string is the actual length, not the maximum length.
The length of a graphic string is the number of DBCS characters. The length of all other values is the number of bytes used to represent the value:
Note that no special consideration is given for mixed character strings. Shift-in, shift-out, and each byte of a DBCS character within a mixed string are all considered to be single bytes.
Assume the host variable ADDRESS is a varying-length character string with a value of '895 Don Mills Road'.
LENGTH(:ADDRESS)
Returns the value 18.
Assume that START_DATE is a column of type DATE.
LENGTH(START_DATE)
Returns the value 4.
Assume that START_DATE is a column of type DATE.
LENGTH(CHAR(START_DATE, EUR))
Returns the value 10.
>>-MICROSECOND--(----+-timestamp_expression----------+--)------><
'-timestamp_duration_expression-'
|
The MICROSECOND function returns the microsecond part of a value.
The argument must be a timestamp or timestamp duration. If a decimal number, the argument must be DECIMAL(20,6) for timestamp duration to be properly interpreted.
The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The other rules depend on the data type of the argument:
The result is the microsecond part of the value, which is an integer between 0 and 999 999.
The result is the microsecond part of the value, which is an integer between -999 999 and 999 999. A nonzero result has the same sign as the argument.
Assume a table TABLEA contains two columns, TS1 and TS2, of type TIMESTAMP. Select all rows in which the microseconds portion of TS1 is not zero and the seconds portion of TS1 and TS2 are identical.
SELECT * FROM TABLEA
WHERE MICROSECOND(TS1) <> 0 AND SECOND(TS1) = SECOND(TS2)
>>-MINUTE--(----+-time_expression---------------+--)-----------><
+-timestamp_expression----------+
+-time_duration_expression------+
'-timestamp_duration_expression-'
|
The MINUTE function returns the minute part of a value.
The argument must be a time, timestamp, time duration, or timestamp duration. If a decimal number, the argument must be:
to be properly interpreted.
The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The other rules depend on the data type of the argument:
The result is the minute part of the value, which is an integer between 0 and 59.
The result is the minute part of the value, which is an integer between -99 and 99. A nonzero result has the same sign as the argument.
Using the CL_SCHED sample table, select all classes with a duration less than 50 minutes.
SELECT * FROM CL_SCHED
WHERE HOUR(ENDING - STARTING) = 0 AND
MINUTE(ENDING - STARTING) < 50
>>-MONTH--(----+-date_expression---------------+--)------------><
+-timestamp_expression----------+
+-date_duration_expression------+
'-timestamp_duration_expression-'
|
The MONTH function returns the month part of a value.
The argument must be a date, timestamp, date duration, or timestamp duration. If a decimal number, the argument must be:
to be properly interpreted.
The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The other rules depend on the data type of the argument:
The result is the month part of the value, which is an integer between 1 and 12.
The result is the month part of the value, which is an integer between -99 and 99. A nonzero result has the same sign as the argument.
Select all rows from the EMPLOYEE table for people who were born (BIRTHDATE) in DECEMBER.
SELECT * FROM EMPLOYEE WHERE MONTH(BIRTHDATE) = 12
>>-SECOND--(----+-time_expression---------------+--)-----------><
+-timestamp_expression----------+
+-time_duration_expression------+
'-timestamp_duration_expression-'
|
The SECOND function returns the seconds part of a value.
The argument must be a time, timestamp, time duration, or timestamp duration. If a decimal number, the argument must be:
to be properly interpreted.
The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The other rules depend on the data type of the argument:
The result is the seconds part of the value, which is an integer between 0 and 59.
The result is the seconds part of the value, which is an integer between -99 and 99. A nonzero result has the same sign as the argument.
Assume that the host variable TIME_DUR (decimal(6,0)) has the value 153045.
SECOND(:TIME_DUR)
Returns the value 45.
Assume that the column RECEIVED (timestamp) has an internal value equivalent to 1988-12-25-17.12.30.000000.
SECOND(RECEIVED)
Returns the value 30.
>>-STRIP-------------------------------------------------------->
.-,B--,' '-----------------------.
>----(--+-character_string--+--------------------------------+-+---)->
| | .-,' '-------------. | |
| '--+-,L-+---+------------------+-' |
| +-,T-+ '-,strip_character-' |
| '-,B-' |
| .-,B--,'G< >'--------------------. |
'-graphic_string--+--------------------------------+---'
| .-,'G< >'----------. |
'--+-,L-+---+------------------+-'
+-,T-+ '-,strip_character-'
'-,B-'
>--------------------------------------------------------------><
|
The STRIP function returns a value in which blanks, or another specified character, have been removed from the end or the beginning of a string.
Note that the argument cannot have a subtype of mixed.
The default value is B.
The default is either a single character space or a graphic double character space (X'4040') depending on the data type of string.
The data type and CCSID of the result depends on the data type of the
string argument. The possible data types and CCSIDs are shown
in the following table:
| Input Data Type | Output Data Type | Output CCSID |
|---|---|---|
| CHAR(n) | VARCHAR(n) | same as that of string |
| VARCHAR(n) | VARCHAR(n) | same as that of string |
| GRAPHIC(n) | VARGRAPHIC(n) | same as that of string |
| VARGRAPHIC(n) | VARGRAPHIC(n) | same as that of string |
| DATE | VARCHAR(n)1 | CCSID of the default subtype |
| TIME | VARCHAR(n)1 | CCSID of the default subtype |
| TIMESTAMP | VARCHAR(26) | CCSID of the default subtype |
| ||
The defined length of the result is identical to the defined length of the string argument.
If the first argument can be null, then the result can be null. If the first argument is null, the result is the null value.
Assume the host variable HELLO (char(9)) has a value of ' Hello '.
STRIP(:HELLO)
Returns the value 'Hello'.
STRIP(:HELLO, T)
Returns the value ' Hello'.
Assume the host variable BALANCE (char(9)) has a value of '000345.50'.
STRIP(:BALANCE,L,'0', )
Returns the value '345.50'.
This example shows the treatment of a graphic string.
STRIP(G'<(XXLLMMNNXXXX)>', T,G'<(XX)>')
Returns the value G'<(XXLLMMNN)>'
This example shows that spaces are treated like any other character. Therefore, if spaces precede the character that is to be stripped from the start of the string, then nothing is stripped.
STRIP(' 00123.400',B,'0')
Returns the value ' 00123.4'.
>>-SUBSTR---(--string_expression,--start_integer_expression----->
>-----+----------------------------+--)------------------------><
'-,length_integer_expression-'
|
The SUBSTR function returns a substring of a string. If any argument of the SUBSTR function can be null, the result can be null; if any argument is null, the result is the null value. The CCSID of the result is the same as that of string.
A substring of string is zero or more contiguous characters of string. If string is a graphic string, a character is a DBCS character. If string is a character string, a character is a byte.
The SUBSTR function accepts mixed data strings. However, because SUBSTR operates on a strict byte-count basis, the result will not necessarily be a properly formed mixed data string.
The SUBSTR function also accepts a datetime argument type for extended flexibility in extracting datetime substring values.
| Note: | TIMESTAMP expressions always have an implicit length of 26 and datatype of CHAR. If the statement is a dynamically prepared one, DATE and TIME expressions each have an implicit length of 254 and a data type of VARCHAR. If the statement is not a dynamically prepared one, DATE and TIME expressions each have an implicit data type of CHAR and a length which is determined by the value for datetime formats in the SYSTEM.SYSOPTIONS catalog table. If the datetime format is LOCAL, the length is the LOCAL length in the SYSTEM.SYSOPTIONS catalog table; otherwise, it is 8 for TIME and 10 for DATE. |
If length is explicitly specified, string is effectively padded on the right with the necessary number of blank characters so that the specified substring of string always exists.
The default for length is the number of characters from the character specified by the start to the last character of string. However, if string is a varying-length string with an actual length less than start (for example, SUBSTR('abcde', 7), the default is zero and the result is the empty string.
If string is a character string:
LENGTH(string) - start + 1
If string is a graphic string:
LENGTH(string) - start + 1
If string is a fixed-length string, omission of length is an implicit specification of LENGTH(string) - start + 1. If string is a varying-length string, omission of length is an implicit specification of zero or LENGTH(string) - start + 1, whichever is greater.
Assume the host variable NAME (varchar(50)) has a value of 'KATIE AUSTIN' and the host variable SURNAME_POS (int) has a value of 7.
SUBSTR(:NAME, :SURNAME_POS)
Returns the value 'AUSTIN'
SUBSTR(:NAME, :SURNAME_POS, 1)
Returns the value 'A'.
Select all rows from the PROJECT table for which the project name (PROJNAME) starts with the word 'OPERATION '.
SELECT * FROM PROJECT
WHERE SUBSTR(PROJNAME,1,10) = 'OPERATION '
The space at the end of the constant is necessary to preclude initial words such as 'OPERATIONS'.
Assume there is a host variable VC300 (VARCHAR(300)), the host variable START (int) has a value of 30, and the host variable LNGTH (int) has a value of 250. Obtain a substring of VC300 starting at START with a length of LNGTH.
Attempt 1:
SUBSTR(:VC300, :START, :LNGTH)
This is not allowed because LNGTH is a host variable and the resulting size is assumed to be that of the source string (300). Thus the size of the host variable exceeds the maximum allowed size of 254.
Attempt 2:
SUBSTR(SUBSTR(:VC300, :START, 254), 1, :LNGTH)
This attempt is successful.
SUBSTR(:VC300, :START, 254)
produces a CHAR(254) result whose value is taken from position 30 to position 273 of VC300 (and contains trailing blanks if VC300 is less than 273 bytes long).
SUBSTR(inner_result, 1, :LNGTH)
produces a VARCHAR(254) result whose value is taken from position one to position 250 of the inner_result. The length of the result is 250.
Attempt 3:
SUBSTR(:VC300, 299)
This is not allowed because the result is a varying-length string with length attribute 300 which is longer than 254.
>>-TIME--(----+-time_expression--------+--)--------------------><
+-timestamp_expression---+
'-time_string_expression-'
|
The TIME function returns a time from a value.
The argument must be a timestamp, a time, or a valid string representation of a time.
The result of the function is a time. The data type is TIME. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The other rules depend on the data type of the argument:
The result is the time part of the timestamp.
The result is that time.
The result is the time represented by the character string.
Notes:
Select all notes from the IN_TRAY sample table that were received at least one hour later in the day (any day) than the current time.
SELECT * FROM IN_TRAY WHERE TIME(RECEIVED) >= CURRENT TIME + 1 HOUR
>>-TIMESTAMP---------------------------------------------------->
>----(--+-+-timestamp_expression----------------+-------------+---)->
| +-timestamp_str_expression------------+ |
| +-yyyymmddhhmmss_character_expression-+ |
| '-390_storeclock_character_expression-' |
'--+-date_expression-----+---+-,time_expression-----+-'
'-date_str_expression-' '-,time_str_expression-'
>--------------------------------------------------------------><
|
The TIMESTAMP function returns a timestamp from a value or a pair of values.
The rules for the arguments depend on whether the second argument is specified.
It must be an expression that returns a timestamp, a valid string representation of a timestamp, a character string of length 8, or a character string of length 14.
A character string of length 8 is assumed to be a |System/390 Store Clock value.
A character string of length 14 must be a string of digits that represents a valid date and time in the form yyyyxxddhhmmss, where yyyy is the year, xx is the month, dd is the day, hh is the hour, mm is the minute, and ss is the seconds.
The first argument must be an expression that returns either a date or a valid string representation of a date. The second argument must be an expression that returns either a time or a valid string representation of a time.
The result of the function is a timestamp. The data type is TIMESTAMP. If either argument can be null, the result can be null; if either argument is null, the result is the null value.
The other rules depend on whether the second argument is specified:
The result is a timestamp with the date specified by the first argument and the time specified by the second argument. The microsecond part of the timestamp is zero.
The result is that timestamp.
The result is the timestamp represented by that character string. The interpretation of a character string as a Store Clock value will yield a timestamp with a year between 1900 to 2042 as described in the IBM System/370 Principles of Operation manual.
Notes:
Assume the column START_DATE (date) has a value equivalent to 1988-12-25, and the column START_TIME (time) has a value equivalent to 17.12.30.
TIMESTAMP(START_DATE, START_TIME)
Returns the value '1988-12-25-17.12.30.000000'.
>>-TRANSLATE---------------------------------------------------->
>----(--+-char_string_exp--| char_string_exp options |--+---)--><
'-| graphic_string_exp |------------------------'
char_string_exp options
.-,'ABC...XYZ','abc...xyz'----------------------------.
|---+-----------------------------------------------------+-----|
| .-,X'000102...FDFEFF'-------------. |
'-,to_string_exp--+---------------------------------+-'
| .-,' '------. |
'-,from_string_exp--+-----------+-'
'-,pad_char-'
graphic_string_exp
|---graphic_string_exp--,to_string_exp--,from_string_exp-------->
.-,G'< >'---.
>-----+-----------+---------------------------------------------|
'-,pad_char-'
|
The TRANSLATE function returns a value in which one or more characters in a string expression may have been translated into other characters.
If the length attribute of to_string_exp is less than the length attribute of from_string_exp, then to_string-exp is padded to the longer length using either the pad_char or a space.
If the length attribute of to_string_exp is more than the length attribute of from_string_exp, the extra characters in to_string_exp are ignored, without a warning.
If there are duplicate characters in from_string_exp, the first one scanning from the left is used. No warning is issued.
The default value for from_string_exp is a string of 256 characters starting with the character X'00' and ending with the character X'FF' (decimal 255).
The default pad_char is a space.
| Note: | None of the arguments can have a subtype of mixed. |
If to_string_exp is not supplied, then from_string_exp must not be supplied. In this case, char_string_exp is simply translated to upper case. This is done based on the folding rules specified in the SYSCHARSETS catalog table.
If the length attribute of to_string_exp is less than the length attribute of from_string_exp, then to_string_exp is padded to the longer length using either the pad_char or a graphic space.
If the length attribute of to_string_exp is more than the length attribute of from_string_exp, the extra characters in to_string_exp are ignored, without a warning.
If there are duplicate characters in from_string_exp, the first one scanning from the left is used. No warning is issued.
The default pad_char is a graphic space.
The result string is built character by character from char_string_exp, translating characters in from_string_exp to the corresponding character in to_string_exp. For each character in char_string_exp, the same character is searched for in from_string_exp. If the character is found to be the nth character in from_string_exp, the resulting string will contain the nth character from to_string_exp. If to_string_exp is less than n characters long, the resulting string will contain the pad character. If the character is not found in from_string_exp, it is moved to the result string untranslated.
The data type and CCSID of the result depends on the data type of the
string argument. The possible data types and CCSIDs are shown
in the following table:
| Input Data Type | Output Data Type | Output CCSID |
|---|---|---|
| CHAR(n) | VARCHAR(n) | same as that of string |
| VARCHAR(n) | VARCHAR(n) | same as that of string |
| GRAPHIC(n) | VARGRAPHIC(n) | same as that of string |
| VARGRAPHIC(n) | VARGRAPHIC(n) | same as that of string |
| DATE | VARCHAR(n)1 | CCSID default of the subtype |
| TIME | VARCHAR(n)1 | CCSID default of the subtype |
| TIMESTAMP | VARCHAR(26) | CCSID default of the subtype |
| ||
The length of the result is identical to the length of the string argument. If any argument can be null, the result can be null; if any argument is null, the result is the null value.
The use of an argument expression (for example, column or host variable) defined as mixed character is not allowed.
Assume the host variable SITE (varchar(30)) has a value of 'Pivabiska Lake Place'.
TRANSLATE(:SITE)
Returns the value 'PIVABISKA LAKE PLACE'.
TRANSLATE(:SITE,'$','L')
Returns the value 'Pivabiska $ake Place'.
TRANSLATE(:SITE,'$$','Ll')
Returns the value 'Pivabiska $ake P$ace'.
TRANSLATE(:SITE,'pLA','Place','.')
Returns the value 'pivAbiskA LAk. pLA..'.
Produce a list that includes the first three columns from all rows in the IN_TRAY sample table and order the list on SUBJECT in a case insensitive manner.
SELECT SUBJECT, RECEIVED, SOURCE, TRANSLATE(SUBJECT)
FROM IN_TRAY
ORDER BY 4
This shows the treatment of a graphic string.
TRANSLATE(G' <(JJOOHHNN)>',G' <(AACCKK)>',G' <(OOHHNN)>')
Returns the value G'<(JJAACCKK)>'.
.----------------.
V |
>>-VALUE--(--expression-------,expression---+--)---------------><
|
The VALUE function returns the first non-null result in a series of expressions.
If any argument is numeric, all arguments must be numeric (SMALLINT, INTEGER, DECIMAL, and FLOAT). If any argument is a character string, all arguments must be character-compatible strings (CHAR, VARCHAR, DATE, TIME, TIMESTAMP). If any argument is a graphic string, all arguments must be graphic strings (GRAPHIC and VARGRAPHIC).
The arguments are evaluated in the order in which they are specified, and the result value of the function is equal to the first argument that is not NULL. If the arguments can be null, the result can be null; if all the arguments are null, the result is the null value.
MIN(31, 5 + MAX(10-4, 8-5, 5-2)) = 11
If scale + 'n' is greater than 31, then there is a potential for a conversion error because there may be a value whose integer part will not fit and an error occurs. For example, in VALUE(X, Y) where X is DECIMAL(27, 2) and Y is DECIMAL(14, 9) the resultant data type is DECIMAL(31,9). If the value of 'X' is 1000000000000000000000000.02 and the value of 'Y' is 30000.000000004, then a decimal overflow occurs because 'X' will not fit in DECIMAL(31,9).
Select all the values from all the rows in the DEPARTMENT table. If the value for department manager (MGRNO) is missing (that is, null) then return a value of 'ABSENT'.
SELECT DEPTNO, DEPTNAME, VALUE(MGRNO, 'ABSENT'), ADMRDEPT
FROM DEPARTMENT
Select the employee number (EMPNO) and salary (SALARY) from all the rows in the EMPLOYEE table. If the salary is missing (that is, null) then return a value of zero.
SELECT EMPNO, VALUE(SALARY,0)
FROM EMPLOYEE
>>-VARGRAPHIC--(--expression--)-------------------------------->< |
The VARGRAPHIC function returns a graphic string representation of a character string.
The argument must be a character string (CHAR, VARCHAR) or a character compatible string (DATE, TIME, TIMESTAMP). If varying-length, the maximum length must not be greater than 127. If the argument is a character string constant that is to be interpreted as mixed data, it must contain properly paired shift control characters.
The result of the function is a varying-length graphic string. If the subtype of the argument is SBCS then the CCSID of the result is the system default CCSID for graphic data. If the subtype of the argument is mixed, then the CCSID of the result is the graphic CCSID which makes up the DBCS portion of the mixed argument CCSID. For details, see the DB2 Server for VM System Administration or DB2 Server for VSE System Administration manual for the table showing mixed CCSIDs and their corresponding DBCS (and SBCS) component CCSIDs.
If the argument can be null, the result can be null; if the argument is null, the result is the null value. The result includes all DBCS characters of the argument and the DBCS equivalent of all single-byte characters of the argument. The first character of the result is the first logical character of the argument, the second character of the result is the second logical character of the argument, and so on. The result does not include X'0E' or X'0F'.
The DBCS equivalent of X'40' is X'4040'. The DBCS equivalent of every single-byte character (nn) other than X'40' is X'42nn'.
The length of the result depends on the number of logical characters in the argument. If the length or maximum length of the argument is n bytes, the maximum length of the result is n (DBCS characters).
VARGRAPHIC will convert a mixed data value to GRAPHIC in the following manner:
Using the EMPLOYEE table, set the host variable VAR_DESC (vargraphic(24)) to the VARGRAPHIC equivalent of the first name (FIRSTNME) for employee number (EMPNO) '000050'.
SELECT VARGRAPHIC(FIRSTNME)
INTO :VAR_DESC
FROM EMPLOYEE
WHERE EMPNO = '000050'
VAR_DESC will be set to the GRAPHIC form of 'JOHN' when using the sample table. The hex representation of this is: X'42D142D642C842D5'.
>>-YEAR--(----+-date_expression---------------+--)-------------><
+-timestamp_expression----------+
+-date_duration_expression------+
'-timestamp_duration_expression-'
|
The YEAR function returns the year part of a value.
The argument must be a date, timestamp, date duration, or timestamp duration. If a decimal number, the argument must be:
to be properly interpreted.
The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The other rules depend on the data type of the argument specified:
The result is the year part of the value, which is an integer between 1 and 9 999.
The result is the year part of the value, which is an integer between -9 999 and 9 999. A nonzero result has the same sign as the argument.
Select all the projects in the PROJECT table that are scheduled to start (PRSTDATE) and end (PRENDATE) in the same calendar year.
SELECT * FROM PROJECT
WHERE YEAR(PRSTDATE) = YEAR(PRENDATE)
Select all the projects in the PROJECT table that are scheduled to take less than one year to complete.
SELECT * FROM PROJECT
WHERE YEAR(PRENDATE - PRSTDATE) < 1