1. Get hold of FreeUDFLib, and declare the DollarVal function.

DECLARE EXTERNAL FUNCTION F_DOLLARVAL
DOUBLE PRECISION
RETURNS CSTRING(254) FREE_IT
ENTRY_POINT 'DollarVal' MODULE_NAME 'FreeUDFLib.dll'

2. Define a computed field in your table:

ALTER TABLE MY_TABLE
ADD Z_AMOUNT COMPUTED BY (
case is_percent
when 1 then amount || '%'
when 0 then f_dollarval(amount)
end
);

3. Select Z_Amount instead of amount. This is not updateable.

regards, Colin

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