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

    

-- case 1
-- when fund_isin with member_ratio = 'O' present in both is_id and is_id_tst table
-- and the value of is_id.member_ratio < is_id_tst.memebr_ratio
-- logic --
-- the from clasuse says take all the records from is_id table 
-- by corelate the fund_isin (t1.fund_isin = t.fund_isin)
-- the subquery then finds record by joining both table is_id and is_id_tst for member_ratio = 'O'
-- and where the member_ratio is smaller (is_id_tst.member_ratio > is_id.member_ratio)
-- extra condition on is_id_tst table is the member_ratio value should be greater than 0 for member_descr='O'
WITH ratio_lower_is_id
AS
(SELECT *
   FROM is_id t
  WHERE EXISTS 
  (SELECT 1
     FROM is_id_tst t2
     JOIN is_id t1
       ON t2.fund_isin = t1.fund_isin
    WHERE t1.fund_isin = t.fund_isin
      AND t2.member_descr = 'O'
      AND t1.member_descr = 'O'
      AND t2.member_ratio > 0
      AND t2.member_ratio > 
          t1.member_ratio)
),
-- case 2
-- applies the same logic as in case 1 but then take records from is_id_tst table
-- where the member_ratio having lower value for record with member_descr='O'
-- in comparison with the record present in is_id table for memebr_descr='O'
ratio_lower_is_id_tst
AS
(SELECT *
   FROM is_id_tst t
  WHERE t.member_ratio > 0
    AND EXISTS 
  (SELECT 1
     FROM is_id t2
     JOIN is_id_tst t1
       ON t2.fund_isin = t1.fund_isin
    WHERE t1.fund_isin = t.fund_isin
      AND t2.member_descr = 'O'
      AND t1.member_descr = 'O'
      AND t2.member_ratio > 
          t1.member_ratio)
),
-- case 3
-- take all records from is_id_tst table for all each unique fund_isin 
-- where the member_ratio value is < 0 for record member_descr='O'
-- and is avaialble in is_id_tst table irrespective of what record for the same
-- fund_isin available in is_id table
ratio_minus_is_id_tst
AS
(SELECT *
   FROM is_id_tst t
  WHERE EXISTS 
  (SELECT 1
     FROM is_id_tst t1
    WHERE t1.fund_isin = t.fund_isin
      AND t1.member_descr = 'O'
      AND t1.member_ratio < 0)
),
-- case 4
-- take all the records from is_id table 
-- where the fund_isin is not available in is_id_tst table
only_in_is_id
AS
(
SELECT *
  FROM is_id t1
 WHERE NOT EXISTS
   (SELECT 1 
      FROM is_id_tst t2
     WHERE t2.fund_isin = t1.fund_isin)
),
-- case 5
-- take all the records from is_id_tst table
-- where the fund_isin is not available in is_id table
only_in_is_id_tst
AS
(
SELECT *
  FROM is_id_tst t1
 WHERE NOT EXISTS
   (SELECT 1 
      FROM is_id t2
     WHERE t2.fund_isin = t1.fund_isin)
)
-- finally once all the sets as per each case available
-- take each of them and do a union all for the final result set
-- one level sub query required only if we want to sort the result otherwise can be removed
-- and only union all of all sets from with clause is enough
select *
  from 
(
-- case1
select *
  from ratio_lower_is_id
union all
-- case 2
select *
  from ratio_lower_is_id_tst
union all
-- case 3
select *
  from ratio_minus_is_id_tst
union all
-- case 4
select *
  from only_in_is_id
union all
-- case 5
select *
  from only_in_is_id_tst
)
order by fund_isin;