CA IDMS Scalar Functions

This section describes the scalar functions provided by CA IDMS including their purpose, syntax, parameters, usage considerations, and examples.

ABS-function

Syntax

►►─ ABS ( value-expression ) ────────►◄

ABS returns the absolute value of the value-expression, which must have a numeric data type.

The result has the same data type as the value-expression. If the value-expression is null, the result is the null value. If a data error occurs, an exception is raised.

Example The following statement returns 125:

SELECT ABS(-125)
   FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

ACOS-function

Syntax

►►─ ACOS ( value-expression ) ────────►◄

ACOS returns the arccosine of the value-expression as an angle expressed in radians. ACOS is the inverse function of the COS function.

The value-expression must be of any numeric data type and must have a value in the range of -1 to 1. It is converted to a double precision floating-point number for processing by this function.

The result of the function is a double precision floating-point number. If the value-expression is null, the result is the null value. If a data error occurs, an exception is raised.

Example

The following statement returns 7.9539883018414370E-01:

SELECT ACOS(0.7)
   FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

ASIN-function

Syntax

►►─ ASIN ( value-expression ) ────────►◄

ASIN returns the arcsine of the value-expression as an angle expressed in radians ASIN is the inverse function of the SIN function.

The value-expression must be of any numeric type and must have a value in the range of -1 to 1. It is converted to a double precision floating-point number for processing by this function.

The result of the function is a double precision floating-point number. If the value-expression is null, the result is the null value. If a data error occurs, an exception is raised.

Example The following statement returns 1.5707963267948966E+00:

SELECT ASIN(1)
   FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

ATAN-function

Syntax

►►─ ATAN ( value-expression ) ────────►◄

ATAN returns the arctangent of the value-expression as an angle expressed in radians. ATAN is the inverse function of the TAN function.

The value-expression must be of any numeric data type. It is converted to a double precision floating-point number for processing by this function.

The result of the function is a double precision floating-point number. If the value-expression is null, the result is the null value. If a data error occurs, an exception is raised.

Example The following statement returns 1.2490457723982544E+00

SELECT ATAN(3)
   FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

ATAN2-function

Syntax

►►─ ATAN2 ( value-expression1, value-expression2 ) ────────►◄

Parameters

value-expression1: Specifies a numeric value-expression. See Expansion of Value-expression.
value-expression2: Specifies a numeric value-expression. See Expansion of Value-expression.

ATAN2 returns the arctangent of x and y coordinates, given by value-expression1 and value-expression2 respectively, as an angle expressed in radians.

Both value-expressions must be of any numeric data type and cannot both be 0. They are converted to double precision floating-point numbers for processing by this function.

The result of the function is a double precision floating-point number. If the value-expression is null, the result is the null value. If a data error occurs, an exception is raised.

Example: The following statement returns 1.2490457723982544E+00

SELECT ATAN2(1,3)
   FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

CAST-function

Syntax

►►── CAST ( ─┬─ value-expression ─┬─ AS data-type ) ────►◄
             └── NULL ────────────┘

The CAST function forces conversion of the value-expression to a specified data type.

CAST allows:

All conversions that can be made without the CAST function
A numeric value to character value conversion, which creates a display version of the numeric data type and follows truncation rules for the numeric data type

When an approximate numeric value is cast to a character value, the value is converted to an external floating point representation. Trailing zeros removed from the mantissa except in the first place to the right of the decimal point.

The maximum number of digits in the mantissa is 6 for a REAL value and 13 for a DOUBLE PRECISION value. The exponent is an integer value with at least one digit. A negative value in either the mantissa or the exponent is preceded by a sign character.

These are examples of casting REAL data values to character values:

REAL data value	Character value through CAST
1.0098999E+02	1.009899E2
1.9899997E+00	1.989999E0
0.0000000E+00	0.0E0
9.9999964E-02	9.999996E-2

When an exact numeric value is cast to a character value, the numeric value is left-justified, with leading zeros removed except in the first place to the left of the decimal point and trailing zeros removed except in the first place to the right of the decimal point. Negative values are preceded by a sign character.

These are examples of casting exact numeric values:

Exact numeric value	Character value through CAST
001234.56	1234.56
00.123456	0.123456
-6.7000	-6.7
666.0000	666.0
666	666

A character value to numeric value conversion extracts the numeric value from the string in either decimal or floating point notation. The character value can have leading or trailing blanks but cannot have extraneous characters (for example, more than one sign or more than one decimal point).

A character value to graphics value conversion converts the character string to its DBCS equivalent and truncates or pads the result to conform to the length in the data type specification.

Note: For more information about assignment rules in conversions, see Comparison, Assignment, Arithmetic, and Concatenation Operations.

Parameters

NULL: Forces conversion of a null value to a specified data type.
AS data-type: Identifies the data type to which the value-expression or null value is to be converted. Expansion of data-type is presented under Expansion of Data-type.

CEIL or CEILING-function

Syntax

►►─┬─ CEIL ─────┬─ (value-expression) ────►◄
   └─ CEILING ──┘

CEILING returns the smallest integer value that is greater than or equal to the value-expression. CEIL and CEILING are identical.

The value-expression must be of any numeric data type.

The result of the function has the same data type as the value-expression except that the scale is 0 if the value-expression is of type (UNSIGNED) DECIMAL or (UNSIGNED) NUMERIC. For example, a value-expression with a data type of NUMERIC(3,2) results in NUMERIC(3,0). If the value-expression is null, the result is the null value. If a data error occurs, an exception is raised.

Example

The following statement returns: 13 2.0000000000000000E+00 -12

SELECT CEILING(12.55), CEILING(123.1E-2), CEILING (-12.55)
   FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

CHAR-function

Syntax

►►─ CHAR ( value-expression ─┬───────────────────────────────┬─ ) ─►◄
                             ├─ , ── ISO ────────────────────┤
                             ├─ , ── USA ────────────────────┤
                             ├─ , ── EUR ────────────────────┤
                             ├─ , ── JIS ────────────────────┤
                             └─ , ── exact-numeric-literal ──┘

CHAR obtains a character string representation from the value in value-expression. The syntax and semantics for the CHAR function depends on the data type of value-expression.

Data type of value-expression is an exact numeric data: INTEGER, SMALLINT, OR LONGINT.
CHAR returns a fixed-length character string representation of the exact numeric value of value-expression. Specifying a second parameter is not allowed. The result is left-justified and contains n characters corresponding to the digits of the value of value-expression with a preceding minus sign if the value-expression is negative. The length of the returned string depends on the data type of value-expression. A SMALLINT data type has a result length of 6. An INTEGER has a result length of 11 and a LONGINT has a result length of 20.

Example:

     SELECT CHAR(FIXLENGTH), LENGTH(CHAR(FIXLENGTH)) AS LEN_SMALLINT
            , CHAR(NUMROWS) , LENGTH(CHAR(NUMROWS))   AS LEN_INTEGER
     FROM SYSTEM.TABLE WHERE NAME = 'TABLE';

     *+ CHAR(FUNCTION)  LEN_SMALLINT  CHAR(FUNCTION)  LEN_INTEGER
     *+ --------------  ------------  --------------  -----------
     *+ 256                        6  33                       11
     *+ 0                          6  0                        11

Data type of value-expression is a fixed point, packed or zoned decimal: (UNSIGNED) DECIMAL, (UNSIGNED) NUMERIC.
CHAR returns a fixed-length character string representation of the value of value-expression. Specifying a second parameter is not allowed. If value-expression has a precision of p and a scale of s, the result contains p+2 characters as follows: a blank or minus sign, depending on the sign of value-expression, p-s digits followed by a period and finally s digits. The result is left-justified.

Example:

SELECT VAC_TIME, CHAR(-VAC_TIME)
     , LENGTH(CHAR(VAC_TIME))
  FROM DEMOEMPL.EMP_VACATION WHERE VAC_TIME > 300
*+
*+               VAC_TIME  CHAR(FUNCTION)          (CONST)
*+               --------  --------------           -------
*+                 340.00  -340.0                        33
*+                 396.00  -396.0                        33
*+                 484.00  -484.0                        33
*+

Data type of value-expression is a floating-point data type: REAL, FLOAT or DOUBLE PRECISION
CHAR returns a fixed-length character string representation of the floating point value of value-expression. Specifying a second parameter is not allowed. The result is left justified and contains 24 characters.

Example:

SELECT AVGROWLENGTH, CHAR(AVGROWLENGTH)
       ,  LENGTH(CHAR(AVGROWLENGTH)) AS L24
  FROM SYSTEM.TABLE WHERE NAME = 'TABLE';
*+
*+   AVGROWLENGTH  CHAR(FUNCTION)               L24
*+   ------------  --------------               ---
*+   2.5600000E+02  2.56E2                        24
*+   0.0000000E+00  0.0E0                         24

Data type of value-expression is a character data type CHAR, VARCHAR.
CHAR returns a fixed-length character string representation of the value of value-expression. An exact-numeric-literal can be specified as a second parameter, in which case it defines the length of the result. The value of exact-numeric-literal must be in the range 0-255. If the length of value-expression is lower than exact-numeric-literal the result will be padded with blanks on the right, else if the length is larger, truncation will occur and, if nonblank characters are truncated, a warning message is issued.

Example:

SELECT CHAR(NAME,4), LENGTH(CHAR(NAME, 4)) AS LEN
  FROM SYSTEM.TABLE WHERE NAME = 'TABLE';
*+ DB001043 T375 C1M322: String truncation
*+ DB001043 T375 C1M322: String truncation
*+
*+ CHAR(FUNCTION)     LEN
*+ --------------     ---
*+ TABL                 4
*+ TABL                 4

Data type of value-expression is DATE, TIME, or TIMESTAMP.

If no format (ISO, USA, EUR, JIS) is specified for the character string, the result is returned in ISO format or, if the SQL statement is embedded in a program, the format specified in a precompiler option.

Note: For information about specifying precompiler options, see the CA IDMS SQL Programming Guide.

Parameters

ISO

Specifies that the format of the result should comply with the standard of the International Standards Organization (ISO). Formats used when ISO is specified are:

Data type	Format	Example
DATE	yyyy-mm-dd	1990-12-15
TIME	hh.mm.ss	16.43.17
TIMESTAMP	yyyy-mm-dd-hh.mm.ss.nnnnnn	1990-12-15-16.43.17.123456

USA

Specifies that the format of the result should comply with the standard of the IBM USA standard. Formats used when USA is specified are:

Data type	Format	Example
DATE	mm/dd/yyyy	12/15/1990
TIME	hh:mm AM hh:mm PM	4:43 PM
TIMESTAMP	yyyy-mm-dd-hh.mm.ss.nnnnnn	1990-12-15-16.43.17.123456

Data type

Format

Example

DATE

mm/dd/yyyy

12/15/1990

TIME

hh:mm AM

hh:mm PM

4:43 PM

TIMESTAMP

yyyy-mm-dd-hh.mm.ss.nnnnnn

1990-12-15-16.43.17.123456

EUR

Specifies that the format of the result should comply with the standard of the IBM European standard. Formats used when EUR is specified are:

Data type	Format	Example
DATE	dd.mm.yyyy	15.12.1990
TIME	hh.mm.ss	16.43.17
TIMESTAMP	yyyy-mm-dd-hh.mm.ss.nnnnnn	1990-12-15-16.43.17.123456

JIS

Specifies that the format of the result should comply with the standard of the Japanese Industrial Standard Christian Era. Formats used when JIS is specified are:

Data type	Format	Example
DATE	yyyy-mm-dd	1990-12-15
TIME	hh:mm:ss	16:43:17
TIMESTAMP	yyyy-mm-dd-hh.mm.ss.nnnnnn	1990-12-15-16.43.17.123456

CHAR_LENGTH or CHARACTER_LENGTH-functions

Syntax

►►─┬─ CHAR_LENGTH ───────┬─ ( value-expression ) ────►◄
   └─ CHARACTER_LENGTH ──┘

CHAR_LENGTH (or CHARACTER_LENGTH) obtains the length of the value in value-expression.

The result of the CHAR_LENGTH (or CHARACTER_LENGTH) function is an integer.

The length of a value depends on its data type:

Data type	Length
BINARY	The number of bytes which contain the value
CHARACTER VARCHAR	The actual number of characters in the string, including blanks
GRAPHIC VARGRAPHIC	The number of DBCS characters

Data type

Length

BINARY

The number of bytes which contain the value

CHARACTER

VARCHAR

The actual number of characters in the string, including blanks

GRAPHIC

VARGRAPHIC

The number of DBCS characters

COALESCE-function

The COALESCE scalar function is identical to the VALUE scalar function, so they are listed together. See VALUE or COALESCE-function for more information.

CONCAT-function

Syntax

►►─ CONCAT ( value-expression1, value-expression2 ) ────────►◄

Parameters

value-expression1: Specifies a character string value-expression. See Expansion of Value-expression for more information.
value-expression2: Specifies a character string value-expression. See Expansion of Value-expression for more information.

CONCAT (value-expression1, value-expression2) is equivalent to value-expression1 || value-expression2. '||' is the concatenation operator and concatenates the value-expression2 to value-expression1.

value-expression1 and value-expression2 can be of BINARY, CHARACTER, VARCHAR, GRAPHICS, or VARGRAPHIC data type.

If any of the value-expressions are null, the result is the null value.

Example

The following statement returns 'A1B2C3':

SELECT  CONCAT(CONCAT('A1', 'B2'), 'C3')
   FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

CONVERT-function

Syntax

►►─ CONVERT ( value-expression, data-type ) ────────►◄

CONVERT is semantically equivalent with CAST. See CAST-function for more information.

Example

The following statement returns 1.1999999999999999E+00:

SELECT CONVERT (1.2, DOUBLE PRECISION)
        FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

COS-function

Syntax

►►─ COS ( value-expression ) ────────►◄

COS returns the cosine of the value-expression, which must be an angle expressed in radians. COS is the inverse function of the ACOS function.

The value-expression must be of any numeric data type. It is converted to a double precision floating-point number for processing by this function.

The result of the function is a double precision floating-point number. If the value-expression is null, the result is the null value. If a data error occurs, an exception is raised.

Example

The following statement returns 7.0000000000000037E-01

SELECT  COS(7.9539883018414370E-01 )
   FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

COSH-function

Syntax

►►─ COSH( value-expression ) ────────►◄

COSH returns the hyperbolic cosine of the value-expression, which must be an angle expressed in radians.

The value-expression must be of any numeric data type. It is converted to a double precision floating-point number for processing by this function.

The result of the function is a double precision floating-point number. If the value-expression is null, the result is the null value. If a data error occurs, an exception is raised.

Example

The following statement returns 1.5430806348152437E+00:

SELECT  COSH (1)
     FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

COT-function

Syntax

►►─ COT ( value-expression ) ────────►◄

COT returns the cotangent of the value-expression, which must be an angle expressed in radians.

The value-expression must be of any numeric data type. It is converted to a double precision floating-point number for processing by this function.

The result of the function is a double precision floating-point number. If the value-expression is null, the result is the null value. If a data error occurs, an exception is raised.

Example

The following statement returns 1.0000000000000000E+00:

SELECT COT(PI() / 4)
   FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

CURDATE-function

Syntax

►►─ CURDATE () ────────►◄

CURDATE is equivalent to the special-register CURRENT DATE. See Expansion of Special-register for more information.

Example

The following statement returns the current date two times:

SELECT CURDATE(), CURRENT DATE
   FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

CURTIME-function

Syntax

►►─ CURTIME () ───────►◄

CURTIME is equivalent to the special-register CURRENT TIME. See Expansion of Special-register for more information.

Example

The following statement returns the current time two times:

SELECT CURTIME() , CURRENT TIME
   FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

DATABASE-function

Syntax

►►─ DATABASE () ────────►◄

DATABASE is equivalent to the special-register CURRENT DATABASE. See Expansion of Special-register for more information.

Example

The following statement returns the current database 'SYSDICT':

SELECT DATABASE()
 FROM SYSTEM.SCHEMA WHERE NAME='SYSTEM';

DATE-function

Syntax

►►─ DATE ( value-expression ) ────────►◄

DATE obtains the date from the value in value-expression.

The result of the DATE function depends on the type of value in value-expression:

Value-expression	Result
TIMESTAMP value	The date part of the timestamp
DATE value	The date
Numeric value	The date that is n-1 days after January 1, 0001, where n is the number that would result if the INTEGER function were applied to value-expression
Character string in the form yyyynnn where yyyy denotes a year and nnn is in the range 001 to 366 denoting a day of that year	The date represented by the character string

DAY or DAYOFMONTH-function

Syntax

►►─┬─ DAY ─────────┬ ( value-expression ) ────────►◄
   └─ DAYOFMONTH ──┘

DAY obtains the day part of the value in value-expression.

Value-expression must be a date, timestamp, or date duration.

The result of the DAY function is an integer, as shown in the next table.

Value-expression	Result
TIMESTAMP value	1 to 31 (the day part of the timestamp)
DATE value	1 to 31 (the day part of the date)
Date duration	The day part of the value (an integer in the range -99 to 99 with the same sign as value-expression if the result is not 0)

Example

The following statement returns 25:

SELECT DAYOFMONTH ('2002-12-25')
   FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

DAYNAME-function

Syntax

►►─ DAYNAME ( value-expression ) ────────►◄

DAYNAME returns a character string containing the English name of the day specified by value-expression.

value-expression must be a DATE or TIMESTAMP data type or must be a CHARACTER or VARCHAR data type and represent a valid string representation of a date or timestamp.

The result is of CHARACTER(12) data type.

The result is null if value-expression is null.

Example

The following statement returns the names of all days from now to now + 6 days "Tuesday Wednesday Thursday Friday Saturday Sunday Monday:"

SELECT  DAYNAME(NOW() + 0 DAY),
          DAYNAME(NOW() + 1 DAY),
          DAYNAME(NOW() + 2 DAY),
          DAYNAME(NOW() + 3 DAY),
          DAYNAME(NOW() + 4 DAY),
          DAYNAME(NOW() + 5 DAY),
          DAYNAME(NOW() + 6 DAY)
FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

DAYOFWEEK-function

Syntax

►►─ DAYOFWEEK ( value-expression ) ────────►◄

DAYOFWEEK returns the day of the week where 1 is Sunday and 7 is Saturday.

value-expression must be a DATE or TIMESTAMP data type or must be a CHARACTER or VARCHAR data type and represent a valid string representation of a date or timestamp.

The result is an INTEGER data type.

The result is null if value-expression is null.

Example

The following statement returns 4, which represents Wednesday:

SELECT DAYOFWEEK ('2002-12-25')
   FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

DAYOFYEAR-function

Syntax

►►─ DAYOFYEAR ( value-expression ) ────────►◄

DAYOFYEAR returns the day of the year where 1 is January 1.

value-expression must be a DATE or TIMESTAMP data type or must be a CHARACTER or VARCHAR data type and represent a valid string representation of a date or timestamp.

The result is an INTEGER data type and in the range of 1 to 366.

The result is null if value-expression is null.

Example

The following statement returns 365:

SELECT DAYOFYEAR ('2002-12-31')
        FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

DAYS-function

Syntax

►►─ DAYS ( value-expression ) ────────►◄

DAYS obtains an integer representation of the date in value-expression.

Value-expression must be a date, timestamp, or valid character string representation of a date.

The result of the DAYS function is d + 1 days from January 1, 0001, where d is the date that would result if the DATE function were applied to value-expression.

DECIMAL-function

Syntax

►►─ DECIMAL ( value-expression ─┬──────────────────────────────────┬──────►◄
                                └─ , ─── precision ┬───────────────┤
                                                   └─ , ── scale ──┘

DECIMAL obtains a decimal representation of the value in value-expression.

Value-expression must be numeric.

The result of the DECIMAL function is a decimal number. The following table shows the default precision and scale of the result if precision is not specified.

Value-expression data type	Default precision	Default scale
LONGINT	19	0
INTEGER	10	0
SMALLINT	5	0
Other numeric data types	Same as value-expression	Same as value-expression

Parameters

precision: Specifies the number of digits in the result. Precision must be an integer in the range of 1 to 31.
scale: Specifies the number of digits to the right of the decimal point in the result. Scale must be an integer in the range of 0 to the value of precision.

DEGREES-function

Syntax

►►─ DEGREES ( value-expression ) ────────►◄

DEGREES returns the number of degrees calculated from the value-expression expressed in radians. The value-expression must be of any numeric data type. It is converted to a double precision floating-point number for processing by this function.

The result of the function is a double precision floating-point number.

If the value-expression is null, the result is the null value. If a data error occurs, an exception is raised.

Example

The following statement returns 8.9999999999999985E+01:

SELECT DEGREES(PI() / 2)
   FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

DIGITS-function

Syntax

►►─ DIGITS  ( value-expression ) ────────►◄

DIGITS obtains a character string representation of the value in value-expression.

Value-expression must be an integer or decimal number.

The result of the DIGITS function is a fixed-length string of digits that represents the absolute value of value-expression and ignores scale. Thus, the result has no sign and no decimal point. The result includes leading zeros.

The length of the result is:

5 if value-expression has a data type of SMALLINT
10 if value-expression has a data type of INTEGER
19 if value-expression has a data type of LONGINT
The precision of value-expression if it contains a decimal number

EXP-function

Syntax

►►─ EXP ( value-expression ) ─────────►◄

EXP returns a value that is calculated as the base of the natural logarithm (e), raised to a power specified by the value-expression. EXP is the inverse function of LOG.

The value-expression must be of any numeric data type. It is converted to a double precision floating-point number for processing by this function.

The result of the function is a double precision floating-point number. If the value-expression is null, the result is the null value. If a data error occurs, an exception is raised.

Example

The following statement returns 2.7182818284590451E+00:

SELECT   EXP (1)
   FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

FLOAT-function

Syntax

►►─ FLOAT ( value-expression ) ─────────►◄

FLOAT obtains a floating-point representation of the value in value-expression.

Value-expression must be a number.

The result of the FLOAT function is a double precision floating-point number. It is the same number that would result if value-expression were assigned to a column with a data type of DOUBLE PRECISION.

FLOOR-function

Syntax

►►─ FLOOR ( value-expression ) ─────────►◄

FLOOR returns the largest integer value that is less than or equal to the value-expression.

The value-expression must be of any numeric data type.

The result of the function has the same data type as the value-expression except that the scale is 0 if the value-expression is of type (UNSIGNED) DECIMAL or (UNSIGNED) NUMERIC. For example, a value-expression with a data type of NUMERIC (3,2) results in NUMERIC(3,0). If the value-expression is null, the result is the null value. If a data error occurs, an exception is raised.

Example

The following statement returns 12 1.0000000000000000E+00 -13

SELECT FLOOR  (12.55), FLOOR  (123.1E-2), FLOOR   (-12.55)
   FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

HEX-function

Syntax

►►─ HEX ( value-expression ) ─────────►◄

HEX obtains a hexadecimal representation of the value in value-expression.

The result of the HEX function is a character string of hexadecimal digits. The resulting value and length of the result field depend on the data-type of value-expression. For a character operand, the first two digits represent the first byte of value-expression, the next two digits represent the second byte of value-expression, and so on.

The length of the result is limited to 32,767 digits.

If value-expression is a numeric character string, its length will vary as noted below. If it is neither a numeric nor a graphic character string, the length of the result is twice the length of value-expression (as defined by the length function) or twice the maximum length of a varying-length string. If value-expression is a graphic character string, the length of the result is four times the length of value-expression (four times the maximum length of a varying-length string)

If value-expression is a fixed-length string, and the length of the result is less than 255, the result is a fixed-length string. Otherwise, the result is a varying-length string with a maximum length equal to:

Twice the fixed or maximum length.
Four times the fixed or maximum length

If value-expression is some kind of numeric data-type, the length of the resulting character string depends on the data-type in the following way:

BINARY—twice the length of value-expression.
(UNSIGNED) DECIMAL—twice the length of the (UNSIGNED) DECIMAL value (The length of an (UNSIGNED) DECIMAL value is equal to precision plus 1, divided by 2).
DOUBLE PRECISION—twice the length of the DOUBLE PRECISION value (16 bytes, for example).
FLOAT—twice the length of the FLOAT value (If precision is less than or equal to 24, the length of a FLOAT value is 4 bytes. If precision is greater than 24, the length of a FLOAT value is 8 bytes).
SMALLINT—twice the length of a SMALLINT value (4 bytes, for example).
INTEGER—twice the length of an INTEGER value (8 bytes, for example).
LONGINT/BIGINT—twice the length of a LONGINT/BIGINT value (16 bytes, for example).
(UNSIGNED) NUMERIC—twice the length of the (UNSIGNED) NUMERIC value (The length of an (UNSIGNED) NUMERIC value is equal to the precision).
REAL—twice the length of the REAL value (8 bytes, for example).

If value-expression is a numeric constant, the following rules apply:

If the value-expression is less than or equal to the maximum positive number that can be stored in a fullword (2147483647), the result will always be an 8 character hex string
Example: HEX(2147483647) will display as 7FFFFFFF
All other numeric constants will be displayed as packed decimal, like for the DECIMAL data-type
Example: HEX(2147483648) will display as 02147483648C

Note: Since HEX(00000000) will return 00000000, it will never match a value of HEX(decimal-field), even if the value of that decimal field is 0. In this case, one should use the CAST on the numeric constant so that the value types are consistent

Example: Suppose the decimal-field is defined as DECIMAL(8) and contains 0, the following WHERE clause will include the row in the result table:

SELECT decimal-field, HEX(decimal-field) FROM rec-name
WHERE HEX(decimal-field) = HEX(CAST(0 AS DECIMAL(8)));

HOUR-function

Syntax

►►─ HOUR  ( value-expression ) ─────────►◄

HOUR obtains the hours part of the value in value-expression.

Value-expression must be a time, timestamp, or time duration

The result of the HOUR function is an integer, as shown in the following table.

Value-expression	Result
TIMESTAMP value	0 to 24 (the hours part of the timestamp)
TIME value	0 to 24 (the hours part of the time)
Time duration	The time part of the value (an integer in the range -99 to 99 with the same sign as value-expression if the result is not 0)

IFNULL-function

Syntax

►►─ IFNULL ( value-expression1, value-expression2 ) ─────────►◄

Parameters

value-expression1: Specifies a value-expression. See Expansion of Value-expression.
value-expression2: Specifies a value-expression. See Expansion of Value-expression.

IFNULL returns the first value-expression that is not null. IFNULL is similar to the VALUE and COALESCE scalar functions with the exception that IFNULL is limited to only two value-expressions instead of multiple value-expressions.

Note: For more information, see VALUE or COALESCE-function.

Example

The following statement will show '**NULL**' for any row with a null value for SEGMENT, otherwise the name of the segment will be shown:

SELECT SCHEMA, NAME, IFNULL (SEGMENT, '**NULL**')
        FROM SYSTEM.TABLE

INSERT-function

Syntax

►►─ INSERT ( value-expression1, start, length, value-expression2 ) ─────────►◄

Parameters

value-expression1: Specifies a character string value-expression. See Expansion of Value-expression.
value-expression2: Specifies a character string value-expression. See Expansion of Value-expression.
start: Specifies a numeric value-expression. See Expansion of Value-expression.
length: Specifies a numeric value-expression. See Expansion of Value-expression.

INSERT returns a string constructed from value-expression1, where beginning at start, length characters have been deleted and value-expression2 has been inserted.

value-expression1 specifies the source string and must be a CHARACTER or VARCHAR data type. If the length of value-expression1 is 0, the result the null value.

Start must be of any numeric data type, but only the integer part is considered. The integer part of start specifies the starting point within value-expression1 where the deletion of characters and the insertion of value-expression2 is to begin. The integer part of start must be in the range of 1 to the length of value-expression1 plus one.

Length must be of any numeric data type, but only the integer part is considered. The integer part of length specifies the number of characters that are to be deleted from value-expression1, starting at start. The integer part of length must be in the range of 0 to the length of value-expression1.

value-expression2 specifies the string to be inserted into value-expression1, starting at start. The string to be inserted must be a CHARACTER or VARCHAR data type.

The result is always of VARCHAR data type.

The length of the result is given by the following formula:

 LENGTH(value-expression1) + LENGTH(value-expression2)
-  min(length,  LENGTH(value-expression1) - start + 1)

If both start and length are constants, the maximum length of the result is calculated during compilation of the INSERT invocation using the above formula, otherwise the maximum length is 8000.

The result is null if either value-expression1 or value-expression2 is null. If the insert cannot be done, because of invalid parameters, an exception is raised.

Example 1

The following statement appends the string 'DEF' to the string 'ABC' giving 'ABCDEF':

SELECT INSERT ('ABC', 4  , 0,'DEF')
                     FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

Because both the start and length parameters of the INSERT function are constants, the maximum length of the result VARCHAR string is 6.

Example 2

The following statement prefixes the string 'DEF' with the string 'ABC' giving 'ABCDEF':

SELECT SUBSTR(INSERT ('DEF', 1 * 1 , 0,'ABC'), 1, 20)
        FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

Because the start position is not a constant, but an expression, the maximum length of the result VARCHAR string of INSERT is 8000. The SUBSTR function is used to limit the final result to 20 characters.

Example 3

The following statement replaces the character at position 3 in string 'ABCDEF' with the string 'XYZ' returning 'ABXYZDEF':

SELECT INSERT ('ABCDEF',3  , 1,'XYZ')
        FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

Because both the start and length parameters of the INSERT function are constants, the maximum length of the result VARCHAR string is 8.

INTEGER-function

Syntax

►►─ INTEGER ( value-expression ) ─────────►◄

INTEGER obtains an integer representation of the value in value-expression.

Value-expression must be a number. The whole number part of value-expression must be in the range of integers.

The result of the INTEGER function is the same number that would result if value-expression were assigned to a column with a data type of INTEGER.

LEFT-function

Syntax

►►─ LEFT  ( value-expression, length ) ─────────►◄

Parameters

value-expression: Specifies a character string value-expression. See Expansion of Value-expression.
length: Specifies a numeric value-expression. See Expansion of Value-expression.

LEFT obtains a substring of the value in value-expression, starting with character position 1.

Value-expression must be a character or graphics string.

The result of the LEFT function is a character string when value-expression is a character string; the result is a graphics string when value-expression is a graphics string.

Length is a value expression that must be an integer not less than 1, and must not exceed the length of the string in value-expression. (The length of a value with a data type of VARCHAR or VARGRAPHIC is its maximum length.)

If the substring is less than the specified length, CA IDMS pads the result with blanks.

If length is not specified, the substring begins at start and ends at the end of the string.

If length is null, the result of the function is null.

LEFT ( value-expression, length ) is equivalent to SUBSTR ( value-expression, 1, length ).

LENGTH-function

Syntax

►►─ LENGTH ( value-expression ) ─────────►◄

LENGTH obtains the length of the value in value-expression.

The result of the LENGTH function is an integer.

The length of a value depends on its data type, as shown in the following table.

Data type	Length
DOUBLE PRECISION	8 bytes
FLOAT	4 bytes if precision <= 24 8 bytes if precision > 24
REAL	4 bytes
BINARY	The number of bytes containing the value
CHARACTER VARCHAR	The actual number of characters in the string, including blanks
DATE	10 bytes
TIME	8 bytes
TIMESTAMP	26 bytes
DECIMAL	The number of bytes containing the value
INTEGER	4 bytes
LONGINT	8 bytes
NUMERIC	The number of bytes containing the value
SMALLINT	2 bytes
UNSIGNED DECIMAL	The number of bytes containing the value
UNSIGNED NUMERIC	The number of bytes containing the value
GRAPHIC VARGRAPHIC	The number of DBCS characters

LOCATE-function

Syntax

►►─ LOCATE ( value-expression1, value-expression2 ┬─────────────┬────── ) ────►◄
                                                  └ , ─ start ──┘

Parameters

value-expression1: Specifies a character string value-expression. See Expansion of Value-expression.
value-expression2: Specifies a character string value-expression. See Expansion of Value-expression.
start: Specifies a numeric value-expression. See Expansion of Value-expression.

LOCATE returns an integer value representing the location of the first value expression within the second value expression. The value expressions must each be either CHARACTER or VARCHAR. If the first value expression does not appear in the second value expression, the result of LOCATE is 0. Otherwise, the result of LOCATE is the byte position of the first matching character within the second string.

Start specifies the character position within the second value-expression at which the search for the first value-expression is to start.

Start is a value-expression that must be an integer not greater than the length of the string in the value-expression2.

LOG-function

Syntax

►►─ LOG ( value-expression ) ─────────►◄

LOG returns a value that is calculated as the natural logarithm of value-expression. LOG is the inverse function of EXP.

The value-expression must be of any numeric data type. It is converted to a double precision floating-point number for processing by this function.

The result of the function is a double precision floating-point number. If the value-expression is null, the result is the null value. If a data error occurs, an exception is raised.

Example

The following statement returns 1.0986122886681095E+00:

SELECT LOG (3)
  FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM'

LOG10-function

Syntax

►►─ LOG10 ( value-expression ) ─────────►◄

LOG10 returns a value that is calculated as the base 10 logarithm of value-expression.

The value-expression must be of any numeric data type. It is converted to a double precision floating-point number for processing by this function.

The result of the function is a double precision floating-point number. If the value-expression is null, the result is the null value. If a data error occurs, an exception is issued.

Example

The following statement returns 3.0000000000000000E+00:

SELECT LOG (1000)
  FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM'

LOWER or LCASE-function

Syntax

►►─┬─ LOWER  ─┬─ ( value-expression ) ─────────►◄
   └─ LCASE ──┘

LOWER operates on CHARACTER or VARCHAR value-expressions. The result is a string of equal length where all upper case characters have been folded into lower case.

Example

The following statement returns 'joe carpenter':

SELECT LCASE('JOE CARPENTER')
   FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

LTRIM-function

Syntax

►►─ LTRIM  ( value-expression ) ─────────►◄

LTRIM removes leading blanks from value-expression.

Value-expression must be a character string. It is the equivalent of TRIM (leading FROM value-expression).

MICROSECOND-function

Syntax

►►─ MICROSECOND  ( value-expression ) ─────────►◄

MICROSECOND obtains the microsecond part of the value in value-expression.

Value-expression must be a timestamp.

The result of the MICROSECOND function is an integer in the range 0 to 999999, representing the microsecond part of the timestamp.

MINUTE-function

Syntax

►►─ MINUTE ( value-expression ) ─────────►◄

MINUTE obtains the minutes part of the value in value-expression.

Value-expression must be a time, timestamp, or time duration.

The result of the MINUTE function is an integer, as shown in the following table.

Value-expression	Result
TIMESTAMP value	0 to 59 (the minutes part of the timestamp)
TIME value	0 to 59 (the minutes part of the time)
Time duration	The minute part of the value (an integer in the range -99 to 99 with the same sign as value-expression if the result is not 0)

MOD-function

Syntax

►►─ MOD ( value-expression1, value-expression2 ) ─────────►◄

Parameters

value-expression1: Specifies a numeric value-expression. See Expansion of Value-expression.
value-expression2: Specifies a numeric value-expression. See Expansion of Value-expression.

MOD returns the remainder of dividing value-expression1 by value-expression2 using the formula:

MOD(v1, v2) = v1 - Truncated_Integer(v1/v2) * v2

with Truncated_Integer(v1 / v2) the truncated integer result of the division.

Both value-expressions must be of any numeric data type. The second value-expression cannot be zero.

If the value-expression is null, the result is the null value. If a data error occurs, an exception is issued.

The data type of the result follows these rules:

If both value-expressions are INTEGER or SMALLINT, the data type of the result is INTEGER.
If one of the value-expressions is LONGINT, and the other is INTEGER, SMALLINT, or LONGINT, the data type of the result is also LONGINT.
If one value-expression is an INTEGER, SMALLINT, or LONGINT and the other is an (UNSIGNED) DECIMAL or (UNSIGNED) NUMERIC, the data type of the result is (UNSIGNED) DECIMAL or (UNSIGNED) NUMERIC with the same precision and scale as the (UNSIGNED) DECIMAL or (UNSIGNED) NUMERIC value-expression.
If both value-expressions are (UNSIGNED) DECIMAL or (UNSIGNED) NUMERIC, the data type of the result is equal to the data type of value-expression1. The precision and scale of the result are given by the following formulas:
```
Prec. result = min(prec.1-scale.1, prec.2-scale.2 ) + max(scale.1, scale.2)
```
```
   Scale.result =  max(scale1, scale2)
```
If either value-expression is a floating-point number, REAL, FLOAT, or DOUBLE PRECISION, the data type of the result is double precision floating-point.

The processing of this function is always done in floating-point. Both value-expressions are converted to double precision floating-point numbers.

Example 1

The following statement returns 1:

SELECT MOD(10, 3    )
   FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

Example 2

The following statement returns 1.0000000000000000E+00:

SELECT MOD(10E0, 3    )
   FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

Example 3

The following statement returns 1.0:

SELECT MOD(10.0, 3    )
   FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

Example 4

The following statement returns 1.00:

SELECT MOD(10.00  , 3    )
   FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

MONTH-function

Syntax

►►─ MONTH ( value-expression ) ─────────►◄

MONTH obtains the month part of the value in value-expression.

Value-expression must be a date, timestamp, or date duration.

The result of the MONTH function is an integer, as shown in the following table.

Value-expression	Result
TIMESTAMP value	1 to 12 (the month part of the timestamp)
DATE value	1 to 12 (the month part of the date)
Date duration	The date part of the value (an integer in the range -99 to 99 with the same sign as value-expression if the result is not 0)

MONTHNAME-function

Syntax

►►─ MONTHNAME ( value-expression ) ─────────►◄

MONTHNAME returns a character string containing the English name of the month specified by value-expression.

value-expression must be a DATE or TIMESTAMP data type or must be a CHARACTER or VARCHAR data type and represent a valid string representation of a date or timestamp.

The result is of CHARACTER(12) data type.

The result is null if value-expression is null.

Example

The following statement returns the names of all months from now to now + 11 months "January February March April May June July August September October November December:"

SELECT   MONTHNAME(NOW() + 0  MONTH),
         MONTHNAME(NOW() + 1  MONTH),
         MONTHNAME(NOW() + 2  MONTH),
         MONTHNAME(NOW() + 3  MONTH),
         MONTHNAME(NOW() + 4  MONTH),
         MONTHNAME(NOW() + 5  MONTH),
         MONTHNAME(NOW() + 6  MONTH),
         MONTHNAME(NOW() + 7  MONTH),
         MONTHNAME(NOW() + 8  MONTH),
         MONTHNAME(NOW() + 9  MONTH),
         MONTHNAME(NOW() + 10 MONTH),
         MONTHNAME(NOW() + 11 MONTH),
         MONTHNAME(NOW() + 12 MONTH)
FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM'

NOW-function

Syntax

►►─ NOW ( ) ─────────►◄

NOW is equivalent to the special-register CURRENT TIMESTAMP. See Expansion of Special-register for more information.

Example

The following statement returns the current date and time two times:

SELECT NOW(), CURRENT TIMESTAMP
   FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

OCTET_LENGTH-function

Syntax

►►─ OCTET_LENGTH ( value-expression ) ─────────►◄

OCTET_LENGTH obtains the length in bytes of the value in value-expression.

The result of the OCTET_LENGTH function is an integer.

The length of a value depends on its data type:

Data type	Length
DOUBLE PRECISION	8
FLOAT	4 if precision <= 24 8 if precision > 24
REAL	4
BINARY	The number of bytes which contain the value
CHARACTER VARCHAR	The actual number of bytes in the string, including blanks
DATE	10
TIME	8
TIMESTAMP	26
DECIMAL	The number of bytes which contain the value
INTEGER	4
LONGINT	8
NUMERIC	The number of bytes which contain the value
SMALLINT	2
UNSIGNED DECIMAL	The number of bytes which contain the value
UNSIGNED NUMERIC	The number of bytes which contain the value
GRAPHIC VARGRAPHIC	Two times the number of DBCS characters

PI-function

Syntax

►►─ PI ( ) ─────────────────►◄

PI returns the constant value of pi as a floating point value. The value returned is 3.141592653589793238.

Example

The following statement returns 3.1415926535897933E+00:

SELECT PI()
   FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

POSITION-function

Syntax

►►─ POSITION ( value-expression IN value-expression ) ─────────►◄

POSITION returns an integer value representing the location of the first value expression within the second value expression. The value expressions must each be either CHARACTER or VARCHAR. If the first value expression does not appear in the second value expression, the result of POSITION is 0. Otherwise, the result of POSITION is the byte position of the first matching character within the second string.

It is the equivalent of LOCATE ( value-expression, value-expression, 1).

POWER-function

Syntax

►►─ POWER ( value-expression1, value-expression2 ) ─────────►◄

Parameters

value-expression1: Specifies a numeric value-expression. See Expansion of Value-expression.
value-expression2: Specifies a numeric value-expression. See Expansion of Value-expression.

POWER returns the value of value-expression1 to the power of value-expression2.

The data types of value-expression1 and value-expression2 must be numeric data types. The internal processing of this function is done using double precision floating-point arithmetic.

The data type of the result of the function depends on the data types of value-expression1 and value-expression2:

The result is of INTEGER type if value-expression1 and value-expression2 are SMALLINT or INTEGER. The result is LONGINT if one of the value-expressions is LONGINT and the other LONGINT, INTEGER or SMALLINT, otherwise, the result is DOUBLE PRECISION.

The result is null if either value-expression1 or value-expression2 is null. If the calculation resulted in a data error, an exception is raised.

Example 1

The following statement returns the value 625:

SELECT POWER(25,2)
   FROM SYSTEM.TABLE WHERE NAME = 'SCHEMA'

Example 2

The following SELECT returns the value 6.2500000000000000E+02:

SELECT POWER(25.0,2)
   FROM SYSTEM.TABLE WHERE NAME = 'SCHEMA'

PROFILE-function

Syntax

►►─ PROFILE ( value-expression ) ─────────►◄

PROFILE obtains the value associated with an attribute of the current user session.

Value-expression must be a character string.

The result of the PROFILE function is a CHARACTER value with a length of 32. If value-expression does not correspond to an attribute keyword for the session, the function returns a null value.

Note: For more information about attributes of a user session, see the discussion of system profiles in CA IDMS System Tasks and Operator Commands Guide.

QUARTER-function

Syntax

►►─ QUARTER ( value-expression ) ─────────►◄

QUARTER returns the quarter of the year in which the date, specified by value-expression, occurs.

value-expression must be a DATE or TIMESTAMP data type or must be a CHARACTER or VARCHAR data type and represent a valid string representation of a date or timestamp.

The result is an INTEGER data type and is in the range of 1 to 4.

The result is null if value-expression is null.

Example

The following statement returns 4 because December is in the last quarter of the year:

SELECT QUARTER('2002-12-31')
        FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM'

RADIANS-function

Syntax

►►─ RADIANS ( value-expression ) ─────────►◄

RADIANS returns the number of radians corresponding to the number of degrees specified by value-expression.

The value-expression must be of any numeric data type. It is converted to a double precision floating-point number for processing by this function.

The result of the function is a double precision floating-point number.

If the value-expression is null, the result is the null value. If a data error occurs, an exception is raised.

Example

The following statement returns 3.1415926535897931E+00, which is an approximate value of PI:

SELECT RADIANS(180)
   FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

RAND-function

Syntax

►►─ RAND (┬──────────────────┬) ──────────►◄
          └ value-expression ┘

RAND returns a random floating-point value between 0 and 1. value-expression is optional and specifies a seed value. If no seed value is specified, 1 will be used as seed value.

If specified, the value-expression must be of any numeric data type. It is converted to an INTEGER number for processing by this function.

The result of the function is a double precision floating-point number.

If a data error occurs, an exception is raised.

Within the context of an IDMS task, the optional seed value is only evaluated once during the very first call of the random generator with a seed value. The series of generated random numbers will be equal for equal seed values when executed under different IDMS tasks.

Example

The following statement returns random floating-point numbers between 0 and 1:

SELECT RAND (200), RAND()
   FROM SYSTEM.SCHEMA;-- WHERE NAME = 'SYSTEM';

REPEAT-function

Syntax

►►─ REPEAT ( value-expression, count ) ─────────►◄

REPEAT returns a string constructed as count times value-expression repeated.

Parameters

value-expression: Specifies the string to be repeated and must be a CHARACTER or CHAR data type.
count: Specifies an expression of any numeric data type, but only the integer part is considered. The integer part of count specifies the number of times to repeat value-expression.

The result of the function is VARCHAR.

The length of the result is the length of expression times count. If the actual length of the result string exceeds the maximum for the return type, an error occurs.

If count is a constant, the maximum length of the result is calculated during compilation of the REPEAT function invocation, otherwise the maximum is 16000.

The result is null if either value-expression or count is null. If the repeat cannot be done because of invalid parameters, an exception is raised.

Example 1

The following statement returns 'ABCDABCDABCDABCD':

SELECT REPEAT('ABCD', 4)
        FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

Example 2

The following statement returns a string with length 0:

SELECT REPEAT('ABCD', 0)
        FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

Example 3

The following statement returns <null> because count is negative:

SELECT REPEAT('ABCD', -2)
        FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

REPLACE-function

Syntax

►►─ REPLACE ( value-expression1, value-expression2, value-expression3 ) ──────►◄

Parameters

value-expression1: Specifies a character string value-expression. See Expansion of Value-expression.
value-expression2: Specifies a character string value-expression. See Expansion of Value-expression.
value-expression3: Specifies a character string value expression. See Expansion of Value-expression.

REPLACE replaces all occurrences of value-expression2 in value-expression1 with value-expression3. If value-expression2 was not found in value-expression1, value-expression1 is returned unchanged.

value-expression1 is a non-null expression that specifies the source string.

value-expression2 is a non-null expression that specifies the string to be replaced in the source string.

value-expression3 is an expression that specifies the replacement string. A null value will cause value-expression1 to be returned unchanged. The arguments must all have data types that are compatible with VARCHAR, that is CHARACTER or VARCHAR. The actual length of each string must be less than or equal to 8000. The data type of the result is VARCHAR and the resulting length must be less than or equal to 8000. The length of the result is given by the following formula, where n is the number of occurrences of value-expression2 in value-expression1:

   LENGTH(value-expression1)
+  (n * (LENGTH(value-expression3)  - LENGTH(value-expression2)))

The result is null if either value-expression1, value-expression2, or value-expression3 is null. If the replace cannot be done because of invalid parameters, that is, in case one or more of the lengths exceed the limit, an exception is raised.

Example 1

In this example, the result is '$$$$123.0$$$$99'.

Replace all characters '*' in the string '**123.0**99' with '$$'.

SELECT REPLACE('**123.0**99', '*', '$$')
 FROM SYSTEM.SCHEMA WHERE NAME ='SYSTEM'

Example 2

List the departments of the EMPSCHM.DEPARTMENT table in alphabetical order, but ignore any spaces when sorting. The REPLACE function removes all spaces in the SORT_NAME column of the result.

SELECT *, REPLACE (DEPT_NAME_0410, ' ', '') SORT_NAME
   FROM EMPSCHM.DEPARTMENT
   ORDER BY SORT_NAME;

Example 3

In this example, the result is 'LOTS OF **FOO**LISH TALK'.

Replace string 'FOO' in the string 'LOTS OF FOOLISH TALK' with '**FOO**'.

SELECT REPLACE('LOTS OF FOOLISH TALK', 'FOO', '**FOO**')
     FROM SYSTEM.SCHEMA WHERE NAME ='SYSTEM'

RIGHT-function

Syntax

►►─ RIGHT ( value-expression, count ) ─────────►◄

RIGHT returns a string constructed from the specified number of rightmost count characters of value-expression.

Parameters

value-expression: Specifies the string from which the result is constructed and must be a CHARACTER or VARCHAR data type.
count: Specifies of any numeric data type, but only the integer part is considered. The integer part of count specifies the length of the result. The integer part of count must be an integer between 0 and n, where n is the length of value-expression.

The result is null if either value-expression1 or count is null. If count is larger than the length of value-expression1, or if an error occurs, an exception is raised.

Example 1

The following statement returns the string 'CD':

SELECT RIGHT ('ABCD', 2 )
        FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

Example 2

The following statement returns a string with length 0:

SELECT RIGHT ('ABCD', 0 )
        FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

ROUND-function

Syntax

►►─ ROUND ( value-expression1, value-expression2 ) ─────────►◄

Parameters

value-expression1: Specifies a numeric value-expression. See Expansion of Value-expression.
value-expression2: Specifies a numeric value-expression. See Expansion of Value-expression.

ROUND returns value-expression1 rounded to value-expression2 places to the right of the decimal point if value-expression2 is positive, or, to the left of the decimal point if value-expression2 is zero or negative.

The value-expression1 must be of any numeric data type.

The value-expression2 must be of any numeric data type but will be converted internally to INTEGER. The integer value of value-expression2 specifies the number of places to the right of the decimal point for the result if value-expression2 is not negative. If value-expression2 is negative, value-expression1 is rounded to 1 + the absolute integer value of value-expression2 number of places to the left of the decimal point. If the absolute integer value of value-expression2 is larger than the number of digits to the left of the decimal point, the result is 0.

If value-expression1 is positive, rounding is to the next higher positive number. If value-expression1 is negative, rounding is to the next lower negative number.

The result of the function has the same data type and attributes as the value-expression1 except that the precision is increased by one if the value-expression is of (UNSIGNED) DECIMAL or (UNSIGNED) NUMERIC data type and the precision is less than 31.

If any of the value-expressions are null, the result is the null value. If a data error occurs, an exception is raised.

Example 1

The following statement returns 627.46380 627.46400 627.46000 50000 627.00000 630.00000 600.00000: 1000.00000 0.00000:

SELECT  ROUND(627.46381, 4) ,
           ROUND(627.46381, 3) ,
           ROUND(627.46381, 2) ,
           ROUND(627.46381, 1) ,
           ROUND(627.46381, 0) ,
           ROUND(627.46381,-1) ,
           ROUND(627.46381,-2) ,
           ROUND(627.46381,-3) ,
           ROUND(627.46381,-4)
FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

Example 2

The following statement returns -627.46380 -627.46400 -627.46000 -627.50000 -627.00000 -630.00000 -600.00000 :

SELECT  ROUND(-627.46381, 4) ,
           ROUND(-627.46381, 3) ,
           ROUND(-627.46381, 2) ,
           ROUND(-627.46381, 1) ,
           ROUND(-627.46381, 0) ,
           ROUND(-627.46381,-1) ,
           ROUND(-627.46381,-2)
FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

RTRIM-function

Syntax

►►─ RTRIM ( value-expression ) ─────────►◄

RTRIM removes trailing blanks from value-expression. Value-expression must be a character string. It is the equivalent of TRIM (trailing FROM value-expression).

SECOND-function

Syntax

►►─ SECOND ( value-expression ) ─────────►◄

SECOND obtains the seconds part of the value in value-expression.

Value-expression must be a time, timestamp, or time duration.

The result of the SECOND function is an integer, as shown in the following table.

Value-expression	Result
TIMESTAMP value	0 to 59 (the seconds part of the timestamp)
TIME value	0 to 59 (the seconds part of the time)
Time duration	The time part of the value (an integer in the range -99 to 99 with the same sign as value-expression if the result is not 0)

SIGN-function

Syntax

►►─ SIGN ( value-expression ) ─────────►◄

SIGN returns an indicator of the sign of value-expression. The possible values for the indicator are:

-1 if value-expression is less than zero
0 if value-expression is zero
1 if value-expression is greater than zero

value-expression must be of any numeric data type except (UNSIGNED) DECIMAL or (UNSIGNED) NUMERIC with a scale and precision of 31. The data type and attributes of the result of the function are the same as the value-expression except when the value-expression is (UNSIGNED) DECIMAL or (UNSIGNED) NUMERIC. The precision is incremented if the value-expression's precision and scale are equal. This is to allow for the return values of the function.

If the value-expression is null, the result is the null value. If a data error occurs, an exception is raised.

Example

The following statement returns -1 0 1:

SELECT SIGN (1 - 10), SIGN ( 0), SIGN (1 +10)
    FROM SYSTEM.TABLE WHERE NAME = 'SYSTEM'

SIN-function

Syntax

►►─ SIN ( value-expression ) ─────────►◄

SIN returns the sine of the value-expression, which must be an angle expressed in radians. SIN is the inverse function of the ASIN function.

The value-expression must be of any numeric data type. It is converted to a double precision floating-point number for processing by this function.

The result of the function is a double precision floating-point number. If the value-expression is null, the result is the null value. If a data error occurs, an exception is raised.

Example

The following statement returns 1.0000000000000000E+00:

SELECT  SIN( PI() / 2)
    FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

SINH-function

Syntax

►►─ SINH ( value-expression ) ─────────►◄

SINH returns the hyperbolic sine of the value-expression, which must be an angle expressed in radians.

The value-expression must be of any numeric data type. It is converted to a double precision floating-point number for processing by this function.

The result of the function is a double precision floating-point number. If the value-expression is null, the result is the null value. If a data error occurs, an exception is raised.

Example

The following statement returns 1.1548739357257750E+01:

SELECT  SIN( PI())
     FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

SPACE-function

Syntax

►►─ SPACE ( value-expression ) ─────────►◄

SPACE returns a character string that consists of value-expression number of blanks. value-expression is of any numeric data type, but only the integer part is considered. The integer part specifies the number of blanks that makes up the result, and it must be between 0 and 30000.

The result is of VARCHAR data type. The length of the result is the integer part of value-expression.

If value-expression is a constant, the maximum length of the result is calculated during compilation of the SPACE function invocation, otherwise the maximum is 30000.

The result is null if value-expression is null. An error occurs if value-expression is larger than 30000.

Example

The following statement returns 10 blanks:

SELECT SPACE (10)
        FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

SQRT-function

Syntax

►►─ SQRT ( value-expression ) ─────────►◄

SQRT returns the square root of the value-expression. The value-expression must be of any numeric data type. It is converted to a double precision floating-point number for processing by this function.

The result of the function is a double precision floating-point number. If the value-expression is null, the result is the null value. If a data error occurs, an exception is raised.

Example 1

The following statement returns 4.0000000000000000E+00:

SELECT  SQRT(16)
    FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

Example 2

The following statement returns <null> because the square root of a negative number does not exist:

SELECT  SQRT(-16)
    FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

SUBSTR or SUBSTRING-function

Syntax

►►┬ SUBSTR ────┬( value-expression,start ─┬────────────────┬────────►◄
  └ SUBSTRING ─┘                          └── , ── length ─┘

►►─ SUBSTRING ( value-expression FROM start ─┬────────────────┬) ───►◄
                                             └── FOR length ──┘

SUBSTR or SUBSTRING obtains a substring of the value in value-expression.

Value-expression must be a character or graphics string.

The result of the SUBSTR function is a character string when value-expression is a character string; the result is a graphics string when value-expression is a graphics string.

Parameters

start

Specifies the position of the first character of the result.

Start is a value expression that must be an integer less than or equal to the length of the string in value-expression.

If start is null, the result of the function is null.

length

Specifies the length of the result.

Length is a value expression that must be an integer not less than one. The sum of length and start must not exceed 1 + the length of the string in value-expression. (The length of a value with a data type of VARCHAR or VARGRAPHIC is its maximum length.)

If the substring is less than the specified length, CA IDMS pads the result with blanks.

If length is not specified, the substring begins at start and ends at the end of the string.

If length is null, the result of the function is null.

TAN-function

Syntax

►►─ TAN ( value-expression ) ─────────►◄

TAN returns the tangent of the value-expression, which must be an angle expressed in radians. TAN is the inverse function of the ATAN function.

The value-expression must be of any numeric data type. It is converted to a double precision floating-point number for processing by this function.

The result of the function is a double precision floating-point number. If the value-expression is null, the result is the null value. If a data error occurs, an exception is raised.

Example

The following statement returns 1.0000000000000000E+00:

SELECT  TAN ( PI()/4 )
   FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

TANH-function

Syntax

►►─ TANH ( value-expression ) ─────────►◄

TANH returns the hyperbolic tangent of the value-expression, which must be an angle expressed in radians.

The value-expression must be of any numeric data type. It is converted to a double precision floating-point number for processing by this function.

The result of the function is a double precision floating-point number. If the value-expression is null, the result is the null value. If a data error occurs, an exception is raised.

Example

The following statement returns 6.5579420263267255E-01:

SELECT  TANH ( PI()/4 )
   FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

TIME-function

Syntax

►►─ TIME ( value-expression ) ─────────►◄

TIME obtains the time from the value in value-expression.

Value-expression must be a timestamp, time, or character string.

The result of the TIME function is a time:

Value-expression	Result
TIMESTAMP value	The time part of the timestamp
TIME value	That time
CHARACTER value in a valid time format	The time represented by the character string

TIMESTAMP-function

Syntax

►►─ TIMESTAMP (┬ value-expression ─────┬) ─────────►◄
               └─ , ─ value-expression─┘

TIMESTAMP obtains a timestamp from a value or pair of values.

If one value expression is specified, it must be:

A value with the TIMESTAMP data type
A valid character string representation of a timestamp
An eight-character string in the form of a System/370 Store Clock value
A 14-character string in the form yyyymmddhhnnss where yyyy is the year, mm is the month, dd is the day, hh is the hour, nn is the minutes, and ss is the seconds
Note: A timestamp represented by a 14-character string has a microsecond part of zero. The interpretation of an eight-character string is a timestamp, as discussed under Store Clock value in IBM System/370 Principles of Operations.

If two value expressions are specified, the first value-expression must be a date or valid character string representation of a date, and the second value-expression must be a time or valid character string representation of a time.

The result of the function is a value with the TIMESTAMP data type:

If two value expressions are specified, the result is a timestamp with the date specified in the first value and the time specified in the second value
If one value expression is specified and it is a character string, the result is the timestamp represented by the character string

TRIM-function

Syntax

►►─ TRIM ( ┬────────────────────────────────────────┬─ value-expression-2 ── ) ─►◄
           ├─────────────┬─ value-expression-1 FROM ┘
           ├─ LEADING  ──┤
           ├─ TRAILING ──┤
           └─ BOTH ──────┘

TRIM removes leading or trailing (or both) pad characters to be removed from a CHARACTER or VARCHAR value-expression.

The optional value expression defines the pad character to be removed. It must specify a one-character value. In the absence of a trim specifier, BOTH is assumed. In the absence of an explicit pad character, BLANK is assumed.

Parameters

leading: Indicates the orientation of the TRIM function.
trailing: Indicates the orientation of the TRIM function.
both: Indicates the orientation of the TRIM function.

TRUNCATE-function

Syntax

►►─ TRUNCATE ( value-expression1, value-expression2 ) ─────────►◄

Parameters

value-expression1: Specifies a numeric value-expression. See Expansion of Value-expression.
value-expression2: Specifies a numeric value-expression. See Expansion of Value-expression.

TRUNCATE returns value-expression1 truncated to value-expression2 places to the right of the decimal point if value-expression2 is positive or 0. If value-expression2 is negative, value-expression1 is truncated to the absolute value of value-expression2 places to the left of the decimal point. If the absolute value of value-expression2 is not smaller than the number of digits to the left of the decimal point, the result is 0.

value-expression1 must be of any numeric data type.

value-expression2 must be of any numeric data type but will be internally converted to INTEGER.

The result of the function has the same data type and attributes as value-expression1.

If any of the value-expressions are null, the result is the null value. If a data error occurs, an exception is raised.

Example

The following statement returns 627.46380 627.46300 627.46000 627.40000 627.00000 620.00000 600.00000 0.00000 0.00000:

SELECT  TRUNCATE(627.46381, 4) ,
              TRUNCATE(627.46381, 3) ,
              TRUNCATE(627.46381, 2) ,
              TRUNCATE(627.46381, 1) ,
              TRUNCATE(627.46381, 0) ,
              TRUNCATE(627.46381,-1) ,
              TRUNCATE(627.46381,-2) ,
              TRUNCATE(627.46381,-3) ,
              TRUNCATE(627.46381,-4)
FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

UCASE or UPPER-function

Syntax

►►─┬─ UCASE ─────┬─ ( value-expression ) ────►◄
   └─ UPPER ─────┘

UCASE (or UPPER) operates on CHARACTER or VARCHAR value-expressions. The result is a string of equal length where all lower case characters have been folded into upper case.

USER-function

Syntax

►►─ USER () ───────────►◄

USER is equivalent to the special-register USER. See Expansion of Special-register for more information.

Example

The following statement returns ABCDE01, user executing the SELECT statement:

SELECT USER()
   FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';

VALUE or COALESCE-function

Syntax

                   ┌────────── , ─────────┐
►►┬ VALUE ────┬ ( ─▼── value-expression ──┴ ) ─────────►◄
  └ COALESCE ─┘

The VALUE scalar function is used to substitute a value for the null value.

The data types of the value-expressions must be compatible. Character strings are not converted to date/time values. Therefore, if any value-expression is a date, each value-expression must be a date, if any value-expression is a time, each value-expression must be a time, if any value-expression is a timestamp, each value-expression must be a timestamp, and if any value-expression is a character string, each value-expression must be a character string.

The value-expressions are evaluated in the order they are specified, and the result of the function is equal to the first value-expression that is not null. The result can be null only if all value-expressions are null.

The result is defined as "equal to" a value-expression because that value-expression is converted or extended, if necessary, to conform to the data type of the function. The data type of the result is derived from the data types of the specified value-expressions as follows:

Strings: If any value-expression is a varying length string, the result is a varying length string whose maximum length is equal to the longest string that can result from the application of the function.

If all value-expressions are fixed length strings, the result is a fixed length string whose length is equal to the longest string that can result from the application of the function.

Date/time values: If the value-expressions are dates, the result is a date. If the value-expressions are times, the result is a time. If the value-expressions are timestamps, the result is a timestamp.

Numbers: If the value-expressions are numbers, the result is the numeric data type that would occur if all value-expressions were part of a single arithmetic expression. If that data type is decimal or numeric, it has precision of p and scale of s so s is the largest result scale of any value-expression, and p is s + n, where n is the largest integral part of any value-expression. Conversion errors are possible if s + n is greater than 31.

VARGRAPHIC-function

Syntax

►►─ VARGRAPHIC ( value-expression ) ───────────►◄

The VARGRAPHIC function is supported only in active DBCS environments.

The VARGRAPHIC function is used to obtain a graphic string representation of a character string. Value-expression must be a binary or character string. If it is binary, the value is treated as graphic, and no data conversion takes place.

If the value-expression must be a character string, the characters are converted to their DBCS equivalent. If the string contains shift-in and shift-out characters, they must be properly paired under the rules for mixed data.

The result of the function is a varying length graphic string. If the value-expression can be null, the result can be null; if the value-expression is null, the result is the null value.

If value-expression is character, it is interpreted as a mixed data string. The result includes all DBCS characters of the value-expression and the DBCS equivalent of all single byte characters of the value-expression. The first character of the result is the first logical character of the value-expression, the second character of the result is the second logical character of the value-expression, and so forth. The result does not include shift-in and shift-out characters.

The length of the result depends on the number of logical characters in the value-expression. If the length or maximum length of the value-expression is n bytes, the maximum length of the result is n (DBCS characters).

WEEK-function

Syntax

►►─ WEEK ( value-expression ) ───────────►◄

WEEK returns the week of the year for the specified value-expression. The function uses the ISO definition: a week starts with Monday and comprises 7 days. Week 1 is the first week of the year that contains a Thursday (or the first week that contains January 4).

value-expression must be a DATE or TIMESTAMP data type or must be a CHARACTER or VARCHAR data type and represent a valid string representation of a date or timestamp.

The result is an INTEGER data type and is in the range of 1 to 53.

The result is null if value-expression is null.

Example

The following statement returns 52 1:

SELECT WEEK ('2000-01-01'), WEEK('2000-01-03')
        FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM'

XMLPOINTER-function

Returns a BINARY(4) value that is a pointer to a LOB (Large Object) that holds the serialized value of XML-value-expression.

The XMLPOINTER function is used in programs that need to process serialized XML values. The structure of the LOB is a variable-length storage object. It starts with a signed integer of 32 bit and contains the LOB data length (max 2 GB), followed by the LOB data.

Syntax

►►─ XMLPOINTER ─── (─── XML-value-expression ───) ────────────────►◄

Notes:

If XML-value-expression is NULL or empty, XMLPOINTER returns a NULL value.
The storage object of the LOB is allocated from a CA IDMS CV storage pool or from the batch address space for local mode programs. The storage object is only addressable in client programs that run in the same CA IDMS CV as the database server or in batch local mode programs.
The program invoking the XMLPOINTER function must free the storage of the LOB when it is no longer needed. If no free storage is done, the storage associated with the LOB is freed at task termination.
Client programs that cannot access the LOB returned by XMLPOINTER can use XMLSERIALIZE (returns a maximum of 30,000 characters) or the table procedure SYSCA.XMLSLICE to process XML values.
XMLPOINTER is not part of the SQL standard. It is a CA IDMS extension to facilitate access to XML LOBs.

Example

The following statement returns pointers to XML LOB objects:

SELECT XMLPOINTER(XMLFOREST(NAME as "Name"
                          , SCHEMA as "Schema")
                 ) AS "PointerToLob"
 FROM  SYSTEM.TABLE

where schema = 'DEMOPROJ'

The result is similar to the following:

*+
*+ PointerToLob
*+ ------------
*+ 20003008
*+ 20003088

XMLSERIALIZE-function

Returns a value of character string or binary string. Serialization is an operation on an XML value that transforms the XML value in a continuous character string representation. Serialization is the inverse operation of parsing.

Syntax

►►─ XMLSERIALIZE ─── ( ───┬── CONTENT ──┬────────────────────────►
                          └── DOCUMENT ─┘

 ►──── XML-value-expression ── AS string-data-type ─── ) ────────►◄

Parameters

string-data-type: Must be one of the character data types of data type: CHAR(n), CHARACTER(n), VARCHAR(n), CHAR VARYING(n).

Note: The DOCUMENT option is not functional in this feature. Therefore, CONTENT should always be specified.

Example

Use of XMLSERIALIZE to serialize an XML value as a character string of 50 characters.

 SELECT NAME
      , XMLSERIALIZE(CONTENT
                    XMLELEMENT(NAME "Schema"
                             , XMLATTRIBUTES (NAME AS "Name"
                                            , CUSER AS "User"))
                    AS CHAR(50)) AS "Serialized XML"
  FROM SYSTEM.SCHEMA WHERE NAME IN ('SYSTEM', 'SYSDICT') ;

NAME        Serialized XML
----        --------------
SYSTEM      <Schema Name="SYSTEM" User="ABCDE01"</Schema>
SYSDICT     <Schema Name="SYSDICT" User="VWXYZ01"</Schema>

YEAR-function

Syntax

►►─ YEAR ( value-expression ) ───────────►◄

YEAR obtains the year part of the value in value-expression.

value-expression must be a date, timestamp, or date duration.

The result of the YEAR function is an integer, as shown in the following table.

Value-expression	Result
TIMESTAMP value	1 to 9999 (the year part of the timestamp)
DATE value	1 to 9999 (the year part of the date)
Date duration	The year part of the value (an integer in the range -9999 to 9999 with the same sign as value-expression if the result is not 0)