Say I want to format calculated numeric output to uniformly have a
specific number of decimal places, say 3 digits right of the decimal
point. My current understanding is that the TO_CHAR function is the
appropriate way to accomplish this kind of numeric formatting. So for
instance I might write
SELECT
project_number,
TO_CHAR(SUM(labor_hours), '999.999') AS total_hours
FROM labor_data
This is great as far as accomplishing the decimal part formatting, but
it is only good provided I've included enough place holders for the
integer part, i.e., in this example for numbers less than 1000, e.g.,
project_number total_hours
05-100 ###.### (actual value is 10810.5)
05-125 285.000
05-150 404.500
05-200 44.000
05-54 66.000
05-59 ###.### (actual value is 2245.75)
So what I'm asking for is advice on how to create the least-significant
digit formatting specifically, but without having to worry about
exceeding the most-significant digit formatting specification. I don't
see that explained in the documentation on TO_CHAR.
I suppose on approach might to guess what the biggest number might be,
and then include an order of magintude larger, e.g.
TO_CHAR(SUM(labor_hours), '999999.999') . But you know, good old Dr.
Murphy, will undoubtly intervene and inevitably create a situation in
which whatever reasonable provisions were made originally, the limits
will at some point be exceeded, causing the world as we know it to come
to an end.
Regards,
Berend Tober
