How to format sql result as amount - ksh

Shell Programming and Scripting
1

I am currently returning an sql result with a number value that I want to format as an amount. The sql runs smoothly on its own, but when run inside my ksh script an error is encountered:

ERROR at line 3:
ORA-01481: invalid number format model

My sql is --

select distinct
    to_char(amount,'$9,999,999,999.00')
from    account;

The amount field in the account table is already defined as a number. Is there a specific way for me to return this value with the $9,999,999,999.00 format?

Thanks in advance. :slight_smile:

2

Hi,

The problem is becuase of the $9,999,999,999. When you run this query in sql, it will work fine. When you run this from shell, shell interprets $9,999,999,999(since there is a $ in it) as a variable name and tries to replace the value in it.

Remove the $ in the to_char and run it, and you can prepend the $ later in the output.

Thanks
Guru.

3

How do you call the SQL? Do you echo to sqlplus, or do you use a here-doc?

4

Thanks for your reply. Does this mean that ksh doesn't interpret the $ symbol?

5

Hi
ksh does interpret the $ symbol. That is why by the time sql query gets executed, the $9 gets replaced with nothing and hence the error.

In order to overcome, use your query like this:

select distinct
to_char(amount,'9,999,999,999.00')
from account;

This will return the amount without the $ symbol. You can prepend the $ symbol to the amount later in the shell, once the sql query is executed.

Thanks
Guru.

6

Or, if you pass the SQL proper, you can get the dollar sign without post-processing. Eg:

sqlplus user/pass@sid << 'EOF'
select distinct
    to_char(amount,'$9,999,999,999.00')
from    account;
EOF

will not expand $9, but instead will pass the whole text as is.

7

I did your suggestion but I still encountered the error. I was able to work around it by taking out the $ sign. Thanks! :smiley: