By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE
IS_ID
(
FUND_ISIN VARCHAR2(12) NOT NULL,
FUND_QUOTE_CRNY VARCHAR2(5),
MEMBER_DESCR VARCHAR2(5),
MEMBER_RATIO NUMBER(19,8),
ALLOCATIONASSETTYPE VARCHAR2(100)
);
CREATE TABLE
IS_ID_TST
(
FUND_ISIN VARCHAR2(12) NOT NULL,
FUND_QUOTE_CRNY VARCHAR2(5),
MEMBER_DESCR VARCHAR2(5),
MEMBER_RATIO NUMBER(19,8),
ALLOCATIONASSETTYPE VARCHAR2(100)
);
BEGIN
INSERT INTO IS_ID (FUND_ISIN, FUND_QUOTE_CRNY, MEMBER_DESCR, MEMBER_RATIO, ALLOCATIONASSETTYPE) VALUES ('L000123', 'USD', 'O', 1.64039, 'Other total');
INSERT INTO IS_ID (FUND_ISIN, FUND_QUOTE_CRNY, MEMBER_DESCR, MEMBER_RATIO, ALLOCATIONASSETTYPE) VALUES ('L000129', 'USD', 'O', 0.0669, 'Other total');
INSERT INTO IS_ID (FUND_ISIN, FUND_QUOTE_CRNY, MEMBER_DESCR, MEMBER_RATIO, ALLOCATIONASSETTYPE) VALUES ('D123458', 'USD', 'O', 0.64039, 'Other total');
INSERT INTO IS_ID (FUND_ISIN, FUND_QUOTE_CRNY, MEMBER_DESCR, MEMBER_RATIO, ALLOCATIONASSETTYPE) VALUES ('D123458', 'USD', 'C', -0.05743, 'Other total');
END;
/
1 rows affected
BEGIN
INSERT INTO IS_ID_TST (FUND_ISIN, FUND_QUOTE_CRNY, MEMBER_DESCR, MEMBER_RATIO, ALLOCATIONASSETTYPE) VALUES ('L000123', 'USD', 'O', 0.0769, 'Other total');
INSERT INTO IS_ID_TST (FUND_ISIN, FUND_QUOTE_CRNY, MEMBER_DESCR, MEMBER_RATIO, ALLOCATIONASSETTYPE) VALUES ('L000129', 'USD', 'O', 1.54030, 'Other total');
INSERT INTO IS_ID_TST (FUND_ISIN, FUND_QUOTE_CRNY, MEMBER_DESCR, MEMBER_RATIO, ALLOCATIONASSETTYPE) VALUES ('M123456', 'USD', 'O', 5.64039, 'Other total');
INSERT INTO IS_ID_TST (FUND_ISIN, FUND_QUOTE_CRNY, MEMBER_DESCR, MEMBER_RATIO, ALLOCATIONASSETTYPE) VALUES ('F563458', 'USD', 'C', 0.84039, 'Other total');
INSERT INTO IS_ID_TST (FUND_ISIN, FUND_QUOTE_CRNY, MEMBER_DESCR, MEMBER_RATIO, ALLOCATIONASSETTYPE) VALUES ('G123456', 'USD', null, 0.04039, 'Other total');
INSERT INTO IS_ID_TST (FUND_ISIN, FUND_QUOTE_CRNY, MEMBER_DESCR, MEMBER_RATIO, ALLOCATIONASSETTYPE) VALUES ('L000123', 'USD', 'C', 5.0769, 'Other total');
END;
/
1 rows affected
CREATE VIEW V_MEMBER_FUND AS
SELECT i.fund_isin,
i.member_descr,
LEAST(i.member_ratio, t.member_ratio) AS member_ratio,
i.allocationassettype
FROM IS_ID i
JOIN IS_ID_TST t
ON t.fund_isin = i.fund_isin
AND t.member_descr = i.member_descr
WHERE i.member_descr = 'O'
UNION
SELECT LEAST(NVL(i.fund_isin,t.fund_isin),NVL(t.fund_isin,i.fund_isin)) AS fund_isin,
LEAST(NVL(i.member_descr,t.member_descr),NVL(t.member_descr,i.member_descr)) AS member_descr,
LEAST(NVL(i.member_ratio,t.member_ratio),NVL(t.member_ratio,i.member_ratio)) AS member_ratio,
LEAST(NVL(i.allocationassettype,t.allocationassettype),NVL(t.allocationassettype,i.allocationassettype)) AS allocationassettype
FROM IS_ID i
FULL JOIN IS_ID_TST t
ON t.fund_isin = i.fund_isin
WHERE (i.member_descr = 'O' OR t.member_descr = 'O' )
AND ( t.fund_isin IS NULL OR i.fund_isin IS NULL )
UNION
SELECT t.fund_isin,
t.member_descr,
t.member_ratio,
t.allocationassettype
FROM IS_ID i
RIGHT JOIN IS_ID_TST t
ON t.fund_isin = i.fund_isin
AND t.member_descr = i.member_descr
WHERE (NVL(i.member_descr,'XYZ') != 'O' OR NVL(t.member_descr,'XYZ') != 'O' )
AND t.fund_isin IS NOT NULL
SELECT * FROM V_MEMBER_FUND
| FUND_ISIN | MEMBER_DESCR | MEMBER_RATIO | ALLOCATIONASSETTYPE |
|---|---|---|---|
| D123458 | O | .64039 | Other total |
| F563458 | C | .84039 | Other total |
| G123456 | null | .04039 | Other total |
| L000123 | C | 5.0769 | Other total |
| L000123 | O | .0769 | Other total |
| L000129 | O | .0669 | Other total |
| M123456 | O | 5.64039 | Other total |