A developer (working in Forms 6) needs to retrieve a logical record which contains
the LOAN_TBL data plus a count of related records in other tables. Complicating this
is the COLLATERAL_TBL, whose record count needs to be split by the TYPE_CODE. If a
loan doesn't have any records in a related table, or of a particular type in the
COLLATERAL_TBL, then there should still be a logical record for that loan but the
associated count should be zero or null. The following set of views works but
performance sucks. Any alternative suggestions?
CREATE OR REPLACE VIEW TYPE1 AS
SELECT COUNT(COLLATERAL_TBL.LOAN_ID) TYPE1_COUNT,
LOAN_TBL.LOAN_ID TYPE1_ID
FROM LOAN_TBL, COLLATERAL_TBL
WHERE COLLATERAL_TBL.LOAN_ID(+) = LOAN_TBL.LOAN_ID
AND COLLATERAL_TBL.TYPE(+) = 1
GROUP BY LOAN_TBL.LOAN_ID;
CREATE OR REPLACE VIEW TYPE2 AS
SELECT COUNT(COLLATERAL_TBL.LOAN_ID) TYPE2_COUNT,
LOAN_TBL.LOAN_ID TYPE2_ID
FROM LOAN_TBL, COLLATERAL_TBL
WHERE COLLATERAL_TBL.LOAN_ID(+) = LOAN_TBL.LOAN_ID
AND COLLATERAL_TBL.TYPE(+) = 2
GROUP BY LOAN_TBL.LOAN_ID;
CREATE OR REPLACE VIEW RECORD_COUNT AS
SELECT COUNT(TRANS.LOAN_ID) TRANSACTION_COUNT,
TYPE1_COUNT,
TYPE2_COUNT,
LOAN_TBL.LOAN_ID COUNT_ID
FROM LOAN_TBL, TRANS_TBL, TYPE1, TYPE2
WHERE TRANS_TBL.LOAN_ID(+) = LOAN_TBL.LOAN_ID
AND TYPE1_ID(+) = LOAN_TBL.LOAN_ID
AND TYPE2_ID(+) = LOAN_TBL.LOAN_ID
GROUP BY LOAN_TBL.LOAN_ID, TYPE1_COUNT, TYPE2_COUNT;
CREATE OR REPLACE VIEW LOAN_RECORD_VIEW AS
SELECT * FROM LOAN_TBL, RECORD_COUNT
WHERE LOAN_TBL.LOAN_ID = COUNT_ID;
Received on Wed Sep 08 1999 - 14:22:57 CDT
