0

For exmaple: select 'A='||(0.22*0.11)||'' as A from dual; it does return A=.0242 expected A=0.0242enter code here

Uday
  • 149
  • 6

1 Answers1

2

If you use to_char function with desired format mask, then you get

SQL> select 'A='|| to_char(0.22*0.11, 'fm999G990D0000')||'' as A from dual;

A
---------------
A=0,0242

SQL>

As of a "generic" format model: you can't "dynamically" set it, but - if you use 9 instead of 0 after decimal point, you might get what you wanted:

SQL> select 'A='|| to_char(88.223*99.112, 'fm999G990D9999999999')||'' as A from dual;

A
---------------------
A=8.743,957976

SQL>
  • Benefit: it returns result you want
  • Drawback: how many 9s will you have to put in there? You can't tell - set it to the "worst" case you expect (such as 10 of them in my example)
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • 1
    select 'A='|| to_char(88.223*99.112, 'fm999G990D0000')||'' as A from dual; Seems rounding value to 4 digits after decimal. Can we have generic one? expected value = 8,743.957976‬ – Uday Sep 23 '22 at 03:28
  • Agree, thank you very much for all your efforts on this. Why ORACLE does truncated preZero when we us || symbol? If we don't concatenate it gives you the correct value. – Uday Sep 23 '22 at 09:22
  • You're welcome. Behavior you describe depends on a tool you use. For example, in SQL*Plus you can use SET NUMFORMAT to format numbers in desired way. Or, GUI tools have their own options. – Littlefoot Sep 23 '22 at 10:04