Hi!

I'm having a small problem (dummy as I am in SQL). In our banking the
account numbers system will changes in certain date (IBANDATE). Now,
when customer wants to view his transactions over long period that
contains this date, he/she must see the old or new account number
depending from transaction date. Also he/she can do search over entire
table using old or new account number and still getting correct results
back (depending from SDATE)

I thougth to be clever and created a view (included below). This works
fine with small data, but when I tried it out with bigger number of
transactions (35000), then the view is very slow (9 sec).

Should I stop using the ACCID in STAT_HISTORY and use the ACCOUNT
number. Then I must modify all my filters to use
WHERE STAT_HISTORY.ACCOUNT=ACCOUNT.ACCOUNT or
STAT_HISTORY.ACCOUNT=ACCOUNT.OLDACCOUNT

Or are there other and better solutions with view




CREATE TABLE ACCOUNT (
ID INTEGER NOT NULL,
ACCOUNT ACCOUNT NOT NULL, //new account string
OLDACCOUNT ACCOUNT, //old account string
....
IBANDATE DATE,
PRIMARY KEY (ID)
);

/* Transactions table */
CREATE TABLE STAT_HISTORY (
ACCID INTEGER NOT NULL,
SDATE DATE NOT NULL,
ROW INTEGER NOT NULL,
.........
PRIMARY KEY (ACCID,SDATE,ROW)
);

CREATE VIEW V_STAT_HISTORY(ACCID,ACCOUNT,SDATE,ROW,...)
AS

SELECT accid,account.ACCOUNT ,
SDATE,
ROW ,
....
from stat_history
join account on stat_history.accID=account.id
where account.ibandate<=SDATE
union
SELECT accid,account.OLDACCOUNT ,
SDATE,
ROW ,
....
from stat_history
join account on stat_history.accID=account.id
where account.ibandate>SDATE

Riho Ellermaa