DB2 10.5 for Linux, UNIX, and Windows

SMALLINT scalar function

The SMALLINT function returns a small integer representation of either a number or a string representation of a number.

Numeric to Smallint:


>>-SMALLINT--(--numeric-expression--)--------------------------><

String to Smallint:


>>-SMALLINT--(--string-expression--)---------------------------><

The schema is SYSIBM.

Numeric to Smallint:

numeric-expression: An expression that returns a value of any built-in numeric data type.
The result is the same number that would occur if the argument were assigned to a small integer column or variable. The fractional part of the argument is truncated. If the whole part of the argument is not within the range of small integers, an error is returned (SQLSTATE 22003).

String to Smallint:

string-expression: An expression that returns a value that is a character-string or Unicode graphic-string representation of a number with a length not greater than the maximum length of a character constant.
The result is the same number that would result from CAST(string-expresssion AS SMALLINT). Leading and trailing blanks are eliminated and the resulting string must conform to the rules for forming an integer, decimal, floating-point, or decimal floating-point constant (SQLSTATE 22018). If the whole part of the argument is not within the range of small integers, an error is returned (SQLSTATE 22003). The data type of string-expression must not be CLOB or DBCLOB (SQLSTATE 42884).

The result of the function is a small integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.

Note: The CAST specification should be used to increase the portability of applications. For more information, see "CAST specification".

Example

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 the employee number (EMPNO).

   SELECT SMALLINT(SALARY / EDLEVEL), SALARY, ESDLEVEL, EMPNO     
     FROM EMPLOYEE