I need to make sure that numbers returned form a SELECT have at least one digit before the decimal point. Right now I have the following SQL statement in a DTS
SELECT PIN, ROUND (SUM(AREA/43560), 2) AS Expr1
FROM table GROUP BY PIN ORDER BY PIN
This produces lines like this ...
1-0005 -01-001,6250.410000
1-0008 -01-001,940.810000
1-0010 -01-001,9.230000
1-0010 -01-001A,.730000
1-0010 -01-002,73.520000
1-0010 -01-003,.680000
I need the output to look like this (check lines 4 and 6) ...
1-0005 -01-001,6250.410000
1-0008 -01-001,940.810000
1-0010 -01-001,9.230000
1-0010 -01-001A,0.730000
1-0010 -01-002,73.520000
1-0010 -01-003,0.680000
Any ideas?
DavidHello,
when you use Oracle you can convert the sum to a char with a special format mask
SELECT PIN, TO_CHAR(ROUND (SUM(AREA/43560), 2), '0.99' AS Expr1
Hope that helps ?
Regards
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com|||Oops. Forgot to mention that I'm running SQLServer 7. Haven't found a comparable function yet.
David|||Hello again,
that is important :)
SELECT PIN, CAST(ROUND (SUM(AREA/43560), 2) AS MONEY) AS Expr1
I am using the enterprise manager and it look ok ...
Hope this help ?
Regards
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com|||When I tried
CAST(ROUND (SUM(AREA/43560), 2) AS MONEY)
I still got the following results
6250.4100
940.8100
9.2300
.7300
73.5200
.6800
:confused:
However, taking your lead, I tried
CAST(ROUND (SUM(AREA/43560), 2) AS CHAR)
and got
6250.410000
940.810000
9.230000
0.730000
73.520000
0.680000
:)
Works for me! Thanks!
David
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment