--- In firebird-support@yahoogroups.com, sugi <truesaint@c...> wrote:

> 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.

Sugi,

It is generally bad practice to do formatting on the server side. You
can do it using a UDF function that you will have to either find
yourself or write yourself, but as soon as you convert it to a
string, you have to then cast it back to a numeric data type to do
any sort of dynamic subtotal work in the client. You have also put
limits on the maximum possible number which may or may not be a
problem.

Adam