Thursday 8 January 2015

Forcing numeric values to display two digits after decimal


This is the simplest way to display two digits after decimal through SQL query.
Here :p_amount is passed as parameter to the query.Use Toad or SQL Developer to execute the query.

SELECT    (TRUNC (:p_amount))
       || DECODE ((:p_amount - TRUNC (:p_amount)),
                  0, '.00',
                  DECODE (LENGTH ((:p_amount - TRUNC (:p_amount))),
                          2,  SUBSTR ((:p_amount - TRUNC (:p_amount)), 1, 3)
                           || '0',
                          SUBSTR ((:p_amount - TRUNC (:p_amount)), 1, 3)
                         )
                 )
  FROM DUAL;