DECIMAL or DEC scalar function

The DECIMAL function returns a decimal representation of either a number or a character-string or graphic-string representation of a number, an integer, or a decimal number.

Numeric to Decimal:

String to Decimal:

The schema is SYSIBM.

Numeric to decimal

numeric-expression

An expression that returns a value of any built-in numeric data type.

precision

An integer constant with a value greater than or equal to 1 and less than or equal to 31.

The default for precision depends on the data type of the numeric-expression:

5 for small integer
11 for large integer
19 for big integer
15 for floating point or decimal
31 for decimal floating point

scale

An integer constant that is greater than or equal to zero and less than or equal to precision. The value specifies the scale of the result. The default value is 0.

The result of the function is the same number that would occur if the argument were assigned to a decimal column or variable with precision p and scale s, where p and s are specified by the second and third arguments. An error occurs if the number of significant digits required to represent the whole part of the number is greater than p-s.

String to decimal

string-expression: An expression that returns a value of a character or graphic string (except a CLOB or DBCLOB) with a length attribute that is not greater than 255 bytes. The string must contain a valid string representation of a number. Leading and trailing blanks are removed from the string, and the resulting substring must conform to the rules for forming a valid string representation of an SQL integer or decimal constant.
precision: An integer constant with a value in the range 1 to 31. The value of this second argument specifies the precision of the result. If not specified, the default is 15.
scale: An integer constant that is greater than or equal to zero and less than or equal to precision. The value specifies the scale of the result. The default value is 0.
decimal-character: A single-byte character constant used to delimit the decimal digits in string-expression from the whole part of the number. The character cannot be a digit, plus (+), minus (-), or blank. The default value is period (.) or comma (,); the default value cannot be used in string-expression if a different value for decimal-character is specified.

Start of change Digits are truncated from the end of the decimal number if the number of digits to the right of the decimal separator character is greater than the scale s. An error is returned if the number of significant digits to the left of the decimal character (the whole part of the number) in string-expression is greater than p-s. End of change

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 null.

Notes

Syntax alternatives:: To increase the portability of applications when the precision is specified, use the CAST specification. For more information, see CAST specification.

Examples

Example 1:

Represent the average salary of the employees in DSN8C10.EMP as an 8-digit decimal number with two of these digits to the right of the decimal point.

   SELECT DECIMAL(AVG(SALARY),8,2)
     FROM DSN8C10.EMP;

Example 2:

Assume that updates to the SALARY column are input as a character string that uses comma as the decimal character. For example, the user inputs 21400,50. The input value is assigned to the host variable NEWSALARY that is defined as CHAR(10), and the host variable is used in the following UPDATE statement:

   UPDATE DSN8C10.EMP
     SET SALARY = DECIMAL (:NEWSALARY,9,2,',')
     WHERE EMPNO = :EMPID;