Hello. can anybody show me give me some guide line on conditional fields.

Create table ledger (
id integer,
type varchar(2),
amount numeric(9,2)
)

I want the amount field show +/- or null/actual depending on type. Due to a
programming library's limitation, the field/s should exist at the table or
a view, I found there are 4 solution.

1. Create view using sub-select.
create view (acc_type, cr_amt, dr_amt) as
select type,
(select amount from ledger 2 where type = 'cr' and 2.id = id) as
cr_amt,
(select amount from ledger 2 where type = 'dr' and 2.id = id) as
dr_amt
from ledger

A modified version is do a self join.

2. Create "computed" field on table which select the same table.
alter table ledger add cr_amt computed by (select amount from ledger 2
where 2.type = 'cr' and 2.id = id)
alter table ledger add dr_amt computed by (select amount from ledger where
2.type = 'dr' and 2.id = id)

3. Create procedure execute the condition and return the result.
alter table ledger add amt computed by (select amt from cr_amt(amount, type))
alter table ledger add amt computed by (select amt from dr_amt(amount, type))

CREATE PROCEDURE cr_amt(amount numeric(9,2), type varchar(2)) RETURN (AMT
NUMERIC(9,2)) AS
BEGIN
IF (type = 'cr') THEN
AMT = amount;
ELSE
AMT = 0;
SUSPEND;
END

4. Use trigger to handle the condition and add 2 dummy field to hold cr_amt
and dr_amt.
Since this file would use frequently, I want to find the fastest way to do
it. Any comment is welcome.

Best regards,

Almond Wong