Hi :),

I have a table that contains two columns/fiels :
- AMOUNT NUMERIC(18,4)
- IS_PERCENTAGE SMALLINT

The IS_PERCENT field acts as a 'flag' on how to interpret the AMOUNT
field. If IS_PERCENTAGE is '1', then the AMOUNT field contains a
percentage value, otherwise it contains a currency value.

AMOUNT = 12345, IS_PERCENT = 0 ---> $12,345
AMOUNT = 10, IS_PERCENT = 1 ---> 10%

These fields are used repeatedly all around the database, and was
decided as the 'better' approach, compared to dealing with two separate
columns (i.e. CURRENCY_AMOUNT and PERCENTAGE_AMOUNT). The argument was
that keeping the value in separate column will create a more complicated
logic, not to mention the null handling headaches.

Anyway, it is working fine, but there's this little formatting issue
that i haven't been able to solve. We're using VIEWs to present the data
to the users, so there are tons of views with the following definition :
...
create table my_view (
....
amount
) as select ...,
case (IS_PERCENT) when 1 then (AMOUNT || '%') else ('$' || AMOUNT) end
from my_table
...

This works to a certain degree, so for example,
AMOUNT = 10, IS_PERCENT = 1 ---> will be displayed as '10%'
AMOUNT = 12345, IS_PERCENT = 0 ---> will be displayed as '$12345.0000'

The problem is that i need the second one to be displayed as
'$12,345.00' instead of '$12345.0000', and this will have to be done on
server side (for the view definition). Is this possible?

It's not possible to do this on the client side since the returned view
column is already a String, so forcing a displayMask or displayFormat in
Delphi won't work.

Thank you very much in advance,
sugi.