It sounds like you've been talking to COBOL'ers. I have learned that
they will generally complicate things all to heck rather than deal with
a null or a variable-size data structure.

In my not so humble and oft overstated opinion, you're in trouble and
its time to start digging. Double purposing columns is always a recipe
for complexity.

I gather that your views are presented by a Delphi app. Delphi's data
aware controls are capable of formatting the received data, provided it
has not been monkeyed with already. Even when using something other
than data aware controls, Delphi has a good set of formatting tools.
Nulls are a non-issue that make logic simpler rather than more complex,
in the long run.

Using views is a good idea. Casting everything to strings in the view
in the DBMS and formatting it there is generally a bad idea, except for
some data migration designs (generally involving obscure COBOL data
types like COMP4).

If you cannot convince people to adopt a cleaner data model, you can
still coerce things into working without altering the data structure.
However, it may require refactoring the application structure.

First, I would change the views to return both the AMOUNT (as a longint,
double, or currency, which ever provides the precision nearest your
requirements) and the IS_PERCENT columns (as a string).

If you are using a data aware control, you would then create an event
method in your display field (can't remember which one now) to format
your text on display. The event you need to set up may have been
"onDisplay". I don't have a working copy of Delphi any more or I'd look
it up. I remember I had to dig a bit in the documentation on the data
aware grid controls. In this method, you would format your text for
display, based on the flag.

Alternatively, if you are using controls that are not data aware, you
would just retrieve the column with amountField.AsCurrency or
amountField.AsInteger and format it using Delphi's string formatting
functions, based on isPercentField.AsString.

On Tue, 2005-07-12 at 06:58 +0700, sugi 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.