SQL Reference

DECIMAL

Numeric to Decimal:

>>-+-DECIMAL-+--(--numeric-expression--------------------------->
   '-DEC-----'
 
>-----+-----------------------------------------------+--)-----><
      '-,--precision-integer--+--------------------+--'
                              '-,--scale-integer---'
 

Character to Decimal:

>>-+-DECIMAL-+--(--character-expression------------------------->
   '-DEC-----'
 
>-----+----------------------------------------------------------------------------+>
      '-,--precision-integer--+-------------------------------------------------+--'
                              '-,--scale-integer----+------------------------+--'
                                                    '-,--decimal-character---'
 
>----)---------------------------------------------------------><
 

The schema is SYSIBM.

The DECIMAL function returns a decimal representation of

• A number
• A character string representation of a decimal number
• A character string representation of a integer number.

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.

Numeric to Decimal

numeric-expression

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

precision-integer

An integer constant with a value in the range of 1 to 31.

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

• 15 for floating-point and decimal
• 19 for big integer
• 11 for large integer
• 5 for small integer.

scale-integer

An integer constant in the range of 0 to the precision-integer value. The default is zero.

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, where p and s are the second and third arguments. An error occurs if the number of significant decimal digits required to represent the whole part of the number is greater than p-s.

Character to Decimal

character-expression

An expression that returns a value that is a character string with a length not greater than the maximum length of a character constant (4 000 bytes). It cannot have a CLOB or LONG VARCHAR data type. Leading and trailing blanks are eliminated from the string. The resulting substring must conform to the rules for forming an SQL integer or decimal constant (SQLSTATE 22018).

The character-expression is converted to the database code page if required to match the code page of the constant decimal-character.

precision-integer

An integer constant with a value in the range 1 to 31 that specifies the precision of the result. If not specified, the default is 15.

scale-integer

An integer constant with a value in the range 0 to precision-integer that specifies the scale of the result. If not specified, the default is 0.

decimal-character

Specifies the single byte character constant that is used to delimit the decimal digits in character-expression from the whole part of the number. The character cannot be a digit plus ('+'), minus ('-') or blank and can appear at most once in character-expression (SQLSTATE 42815).

The result is a decimal number with precision p and scale s where p and s are the second and third arguments. Digits are truncated from the end if the number of digits right of the decimal character is greater than the scale s. An error occurs if the number of significant digits left of the decimal character (the whole part of the number) in character-expression is greater than p-s (SQLSTATE 22003). The default decimal character is not valid in the substring if the decimal-character argument is specified (SQLSTATE 22018).

Examples:

• 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).

    SELECTPRSTDATE + DECIMAL(:PERIOD,8)
      FROM PROJECT
  

• Assume that updates to the SALARY column are input through a window as a character string using comma as a decimal character (for example, the user inputs 21400,50). Once validated by the application, it is assigned to the host variable newsalary which is defined as CHAR(10).

   UPDATE STAFF
     SET SALARY = DECIMAL(:newsalary, 9, 2, ',')
     WHERE ID = :empid;
  

  The value of newsalary becomes 21400.50.

• Add the default decimal character (.) to a value.

     DECIMAL('21400,50', 9, 2, '.')
  

  This fails because a period (.) is specified as the decimal character but a comma (,) appears in the first argument as a delimiter.