Oracle 11g Release 2

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
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