If it is a numeric data column -- you probably want to use the "round" function: SELECT round(1200.01, 3); SELECT round(12.009, 2); "Berend Tober" <btober@xxxxxxxxxxxxxxxx> wrote in message news:4379F997.6050703@xxxxxxxxxxxxxxxxxxx > 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 > > > -------------------------------------------------------------------------------- > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
