CODEWITHSUNDEEP
sqloracle-databaseoracle-apex
Dev
Dev

Reputation: 33

Using to_char to format currency?

So I want the salary returned in the result set to be formatted to this currency type but I believe I am using the to_char in the wrong spot but I'm not sure how. I'm also not sure if I have to use it in both instances of Salary either. Any help would be much appreciated!!

SELECT EMPNO, ENAME, JOB, SAL * 52 TO_CHAR('$999,999')
FROM EMP
WHERE SAL * 52 >
(SELECT AVG(SAL *52) FROM EMP);

I receive the
ORA-00923: FROM keyword not found where expected error from Oracle Apex.

It runs fine without the to_char.

Upvotes: 1

Views: 6062

Answers (3)

Caius Jard
Caius Jard

Reputation: 74605

You need to pass the data you want formatting into to_char also:

SELECT TO_CHAR(123, '$999,999') from dual

So pass your sal*52 in as the first argument

Incidentally you may want to use a format more like:

TO_CHAR(Sal*52, 'L999G999D99')

Rather than hard coding symbols and grouping separators in, using these letters will take the symbol and grouping separator from the region settings of the database. If your app will have an international audience you should appreciate that not everyone uses a comma to group thousands and a period for decimals. Some people write 123.456,99 for "one hundred twenty three thousand four hundred and fifty six [dollars] 99 [cents]" example

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions181.htm has more info on to char

Might also be worth mentioning if you're getting unexpected leading spaces after to_char()ing, start your format mask with FM eg

TO_CHAR(123, 'FML999G999D99')

FM is Fill Mode, turns off a facility in oracle that automatically pads data with leading spaces so it matches the width of the formatting mask. It's handy for reports if youre dumping data in a fixed-width format, to have it padded by spaces, so all your amounts are eg 9 characters wide, but it's unnecessary or undesirable in other contexts

Upvotes: 2

D-Shih
D-Shih

Reputation: 46219

you can try to let your value in first parameter second is your format. 

SELECT EMPNO, ENAME, JOB, to_char(SAL * 52,'$999,999')
FROM EMP
WHERE SAL*52 > (SELECT AVG(SAL * 52) FROM EMP) ;

Upvotes: 1

Gauravsa
Gauravsa

Reputation: 6524

Are you looking for this:

SELECT EMPNO, ENAME, JOB, TO_CHAR(SAL * 52, 'FM999,999') as SALVAL 
FROM EMP
WHERE SAL * 52 >
(SELECT AVG(SAL *52) FROM EMP);

Upvotes: 1

Related Questions