CODEWITHSUNDEEP
sqloracle-databaseformattingcurrencyto-char
5120bee
5120bee

Reputation: 709

Formatting a '$' dollar sign to an SQL Selection Statement with SUM() in it?

I'm trying to format a '$' dollar sign to column "c" which is the SUM of all rows of two other columns multiplied together. I first turn it into a character as follows:

   SELECT TO_CHAR(SUM(a * b)) AS "c"
   FROM table1;

which works fine, but when I format it by adding 'L9,999.99' formatter it gives an error:

   SELECT TO_CHAR(SUM(a * b, 'L9,999.99') AS "c"
   FROM table1;

So I tried doing a CAST:

   SELECT TO_CHAR(CAST(SUM(a * b) AS NUMERIC(10,2)) AS "c"
   FROM table1;

But nothing is working.

How can I add a '$' dollar sign to a column with a SUM() function using TO_CHAR and/or CAST?

For reference, my intended output is to turn:

    C
    10
    10.05
    11.50

into

    C
    $10
    $10.05
    $11.50

Upvotes: -2

Views: 3221

Answers (2)

Azizur Rahman
Azizur Rahman

Reputation: 1

When running the folowing query: select TO_CHAR(SUM(Balance, '99,999,999,999.99')) from EAB ;

The result was: ERROR : ORA-00909: invalid number of arguments

Another query and the following error: select SUM(TO_CHAR(Balance, '99,999,999,999.99')) from EAB ; ERROR :ORA-01722: invalid number

Upvotes: 0

miracle173
miracle173

Reputation: 1983

Expanding the comment of JJJ: There was a paranthese missing, the following should work:

SELECT TO_CHAR(SUM(a * b), 'L9,999.99') AS "c"
FROM table1;

The statement 

SELECT TO_CHAR(SUM(a * b, 'L9,999.99') AS "c"
FROM table1;

will raise a compile time error:

 ORA-00909: invalid number of arguments

Upvotes: 0

Related Questions