Number Formatting 3

[paljnad]

Hello,

I am on Oracle 9i and I am trying to format the output in a query as follows:

SELECT DISTINCT

SUM(CASE
WHEN b.year = '2005' THEN
to_char(b.ytd_amount, 9999999999999999999999.99')
ELSE
to_char(0, '99.99')
END) AS Y2005
FROM table b, table a

The result set does not seem to be formatting the ytd_amount because a 0 will show up as 0 and not 0.00 and 150 will show up as 150 and not 150.00.

 

[jaxtell]

It works for me, but I'm using 10g. Try this instead.

SELECT DISTINCT 
              
                SUM(CASE
                      WHEN b.year = '2005' THEN
                       to_char(b.ytd_amount, 9999999999999999999999.99')
                      ELSE
                       to_char(0, '0.00')
                    END) AS Y2005
  FROM table b, table a

You could just have

SUM(CASE
                      WHEN b.year = '2005' THEN
                       to_char(b.ytd_amount, 9999999999999999999999.99')
                      ELSE
                       '0.00'
                    END) AS Y2005

if you didn't oversimplify your example. If that doesn't work, I'd guess it is a formating problem with the application, not the result from the database.

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[BJCooperIT]

The format mask is not forcing any position to display. Does this help?

SQL> SELECT to_char(150,'9999999999.99') all_nines_150,
  2         to_char(0,  '99.99')         all_nines_9,
  3         to_char(150,'9999909999.99') forced_integer_150,
  4         to_char(0,  '90.99')         forced_integer_0
  5    FROM dual
  6  
SQL> /

ALL_NINES_150  ALL_NINES_9 FORCED_INTEGER_150 FORCED_INTEGER_0
-------------- ----------- ------------------ ---------------- 
        150.00         .00           00150.00             0.00

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[LKBrwnDBA]

Also works fine in 9.2.0.5


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

9.2.0.5:

SQL> SELECT to_char(150,'9999999999.99') all_nines_150,
  2         to_char(0,  '99.99')         all_nines_9,
  3         to_char(150,'9999909999.99') forced_integer_150,
  4         to_char(0,  '90.99')         forced_integer_0
  5*   FROM dual
  6  /

ALL_NINES_150  ALL_NI FORCED_INTEGER FORCED
-------------- ------ -------------- ------
        150.00    .00       00150.00   0.00

SQL>

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[paljnad]

I am on Oracle9i Enterprise Edition Release 9.2.0.8.0.

And I am trying this:
SELECT DISTINCT

SUM(CASE
WHEN b.year = '2005' THEN
to_char(b.ytd_amount,'9999999999.99')
ELSE
'0.00'
END) AS Y2005
FROM table b, table a

and the o/p is as follows:

-1.3
0
3.1
3.8
9.2
10
57.72
78.4
115.3
150
209.65
230.77
303.64
378.4
596.99
807.69
896.43
1038.46
1685.85
14458.53
14513.15
15000.05
5999999.94

 

[jaxtell]

Try this

SELECT DISTINCT 
                to_char(SUM(CASE
                      WHEN b.year = '2005' THEN
                       to_char(b.ytd_amount,'9999999999.99')
                      ELSE
                       '0.00'
                    END), '999999999990.99') AS Y2005
  FROM table b, table a

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[paljnad]

Thank you - that worked !