PDA

View Full Version : Adding Leading Zeros

dplittle
04-29-02, 14:55
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?

David
alligatorsql.com
04-29-02, 15:48
Hello,

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
dplittle
04-29-02, 15:53
Oops. Forgot to mention that I'm running SQLServer 7. Haven't found a comparable function yet.

David
alligatorsql.com
04-29-02, 16:17
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
dplittle
04-29-02, 16:36
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