Posted by Razvan Socol on 02/15/06 20:18

Hi, rola

Your portfolio table SHOULD NOT contain the checking_openamt,
savings_openamt and cd_openamt columns. These columns should be
computed using a view, like this:

CREATE VIEW portfolio_view AS
SELECT p.portfolio_id, p.portfolio_opendate,
s.checking_openamt, s.savings_openamt, s.cd_openamt
FROM portfolio p LEFT JOIN (
SELECT portfolio_id,
MONTH(account_opendate) as account_month,
YEAR(account_opendate) as account_year,
SUM(CASE WHEN product='checking' THEN account_openamt ELSE 0 END)
as checking_openamt,
SUM(CASE WHEN product='savings' THEN account_openamt ELSE 0 END)
as savings_openamt,
SUM(CASE WHEN product='cd' THEN account_openamt ELSE 0 END)
as cd_openamt
FROM account
GROUP BY portfolio_id, MONTH(account_opendate),
YEAR(account_opendate)
) s ON p.portfolio_id=s.portfolio_id
AND MONTH(portfolio_opendate)=account_month
AND YEAR(portfolio_opendate)=account_year

It is possible to use an UPDATE statement with SUM() in a subquery to
fill these values in the portfolio table, but this should not be done
because it creates redundancy and this leads to possible
inconsistencies. To ensure that you don't have inconsistencies, you can
write the UPDATE-s in a trigger, but you also need to ensure that the
destination columns won't be updated directly. In conclusion, it's best
to do this using a view.

Razvan

[Back to original message]