Thread: Most significant digit number formatting
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
Attachment
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@seaworthysys.com> wrote in message news:4379F997.6050703@seaworthysys.com... > 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 >
codeWarrior wrote:
>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);
>
>
>
Interesting. I had tried that. After your message I tried again and
encountered this interesting anomaly: while the ROUND function used in a
query run in the SQL window of PgAdmin III does in fact force output of
trailing zero decimal digits to the extent specified, i.e.,
SELECT
project_number,
labor_hours,
TO_CHAR(labor_hours, '999.999'),
ROUND(labor_hours,3)
FROM time_data
LIMIT 5
"05-08",1974.0000," ###.###",1974.000
"05-100",10810.5000," ###.###",10810.500
"05-125",285.0000," 285.000",285.000
"05-150",404.5000," 404.500",404.500
"05-200",44.0000," 44.000",44.000
Running the same query though a TQuery dataset object in Borland Delphi
using the BDE truncates the trailing zeros from ROUND:
000-05-08 1974 ###.### 1974
000-05-100 10810.5 ###.### 10810.5
000-05-125 285 285.000 285
000-05-150 404.5 404.500 404.5
000-05-200 44 44.000 44
That is why I didn't realize ROUND was an option, but for me it still is
not since the report is produced by a Delphi application. I suppose I
can accomplish this formatting programmatically within the Delphi
application, but I was hoping to have the data base do it directly.
Thanks,
Berend Tober
>"Berend Tober" <btober@seaworthysys.com> wrote in message
>news:4379F997.6050703@seaworthysys.com...
>
>
>>Say I want to format calculated numeric output to uniformly have a
>>specific number of decimal places,
>>
...
Attachment
Got it now.... Delphi is interfering with the numeric formatting -- obviously... this is considered normal for numeric data types that trailing zeroes are removed... they are insignificant anyway.... To solve your issue: I guess the thing to do is to cast the result as text to preserve the formatting but this will be a string instead of a number... SELECT round(12.0109, 3)::text; Greg... "Berend Tober" <btober@seaworthysys.com> wrote in message news:437CAFD4.9070805@seaworthysys.com... > > codeWarrior wrote: > >>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); >> >> >> > Interesting. I had tried that. After your message I tried again and > encountered this interesting anomaly: while the ROUND function used in a > query run in the SQL window of PgAdmin III does in fact force output of > trailing zero decimal digits to the extent specified, i.e., > > SELECT > project_number, > labor_hours, > TO_CHAR(labor_hours, '999.999'), > ROUND(labor_hours,3) > FROM time_data > LIMIT 5 > > "05-08",1974.0000," ###.###",1974.000 > "05-100",10810.5000," ###.###",10810.500 > "05-125",285.0000," 285.000",285.000 > "05-150",404.5000," 404.500",404.500 > "05-200",44.0000," 44.000",44.000 > > Running the same query though a TQuery dataset object in Borland Delphi > using the BDE truncates the trailing zeros from ROUND: > > 000-05-08 1974 ###.### 1974 > 000-05-100 10810.5 ###.### 10810.5 > 000-05-125 285 285.000 285 > 000-05-150 404.5 404.500 404.5 > 000-05-200 44 44.000 44 > > That is why I didn't realize ROUND was an option, but for me it still is > not since the report is produced by a Delphi application. I suppose I > can accomplish this formatting programmatically within the Delphi > application, but I was hoping to have the data base do it directly. > > Thanks, > Berend Tober > > >>"Berend Tober" <btober@seaworthysys.com> wrote in message >>news:4379F997.6050703@seaworthysys.com... >> >> >>>Say I want to format calculated numeric output to uniformly have a >>>specific number of decimal places, >>> > ... > -------------------------------------------------------------------------------- > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >