CODEWITHSUNDEEP
phpsqloracle-databasenumber-formatting
Jack
Jack

Reputation: 165

What is the best method to always get 2 decimal places in Oracle?

What is the best method to always get 2 decimal places in values returned by Oracle?

At the moment I am wrapping all database related PHP functions in number_format etc. but I want to move these to be directly inside the SQL query.

Even better is there an environment variable I can set or similar when I am connecting to Oracle so that I do not have to do this?

function OrderNetTotal($id) {
    global $dbh;

    $sth = $dbh->prepare("SELECT net_total FROM order_totals WHERE order_no = $id");

    $sth->execute();
    $result = $sth->fetchAll();

    return number_format((float)$result[0]['0'], 2, '.', '');
}

Note: I cannot edit database schema etc.

Upvotes: 3

Views: 264

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49112

Depends whether you just want to display the number in a format or you want to do some arithmetic.

To display, use TO_CHAR with format modifier.

For example,

SQL> SELECT TO_CHAR(123456.7890, 'fm9999999.99') FROM dual;

TO_CHAR(123
-----------
123456.79

The data type of the output is string and not a number.

To round off, and let the data type remain as number, use ROUND.

For example,

SQL> SELECT ROUND(123456.7890, 2) FROM dual;

ROUND(123456.7890,2)
--------------------
           123456.79

You could use DECODE to not round off when the number is zero and use NVL to handle the null values.

SQL> WITH data AS
  2    ( SELECT 123.456 num FROM dual UNION
  3    SELECT NULL FROM dual UNION
  4    SELECT 0 FROM dual
  5    )
  6  SELECT num, NVL(DECODE(num, 0, 0, ROUND(num, 2)), 0) num FROM data;

       NUM        NUM
---------- ----------
         0          0
   123.456     123.46
                    0

Upvotes: 1

Daan
Daan

Reputation: 12246

Well you could just use ROUND()

$sth = $dbh->prepare("SELECT ROUND(net_total,2) FROM order_totals WHERE order_no = $id");

Or even better:

$sth = $dbh->prepare("SELECT TO_CHAR(net_total,'99.99') FROM order_totals WHERE order_no = $id");

Upvotes: 3

Related Questions