Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2002
    Posts
    3

    Post Unanswered: Adding Leading Zeros

    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

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189
    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

  3. #3
    Join Date
    Apr 2002
    Posts
    3
    Oops. Forgot to mention that I'm running SQLServer 7. Haven't found a comparable function yet.

    David

  4. #4
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189
    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

  5. #5
    Join Date
    Apr 2002
    Posts
    3
    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



    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •