Numeric Format in SQL

[aph]

Hi,

How I can get the result in format:
for example here is a simple query
select sum(sal) from emp;
29025

But I need the result in
29,025.00

Thanks

[stecal]

col sal format 999,999

SQL> col num format 999,999.99
SQL> select * from test;


NUM
-----------
999,999.99


SQL> insert into test values (10000);

1 row created.

SQL> select * from test;


NUM
-----------
10,000.00

[victorp]

Hi!

select to_char(sum(sal), '999,990.99') from emp;
or
select to_char(sum(sal), '999G990D99') from emp;

[aph]

Thanks for the solutions, but the problem is I am creating flat spool file and I need the format for example from the left most start position like below:
0029025.00

Now if I use to_char function then it leaves 1 space from the left
0029025.00
And for some reason we can not change the sqlldr control file, we have to follow the bank standard provided format.

Please help to resolve this issue.
* You may not see one space blank left in this page, please run emp query to see what I am trying to explain.

Thanks in advance,

aph.

[balajiyes]

try to use LPAD/ RPAD function
Cheers!

[Ao]

One space on the left is the sign. If you try to convert negative number with to_char function you will see the minus sign rather than space.

select 'FORMAT: 000g000d00 PLUS ', to_char(23000.4, '000G000D00') from dual
union
select 'FORMAT: 000g000d00 MINUS ',to_char(-23000.4, '000G000D00') from dual
union
select 'FORMAT: S000g000d00 PLUS SIGN ',to_char(23000.4, 'S000G000D00') from dual
union
select 'FORMAT: S000g000d00 MINUS SIGN',to_char(-23000.4, 'S000G000D00') from dual

RESULT :
FORMAT: 000g000d00 MINUS -023.000,40
FORMAT: 000g000d00 PLUS 023.000,40
FORMAT: S000g000d00 MINUS SIGN -023.000,40
FORMAT: S000g000d00 PLUS SIGN +023.000,40


To avoid the space you can try the 'FM'

select to_char( 2000,'FM000g000d00') from dual;
( try this with negative number)

I hope this will help you.
Ao.

[Shestakov]

SQL> col a format '09999.999'
SQL> select 987.76 a from dual;

A
----------
00987.760

SQL> col a format '099,999.999'
SQL> select 987.76 a from dual;

A
------------
000,987.760