Create Oracle View Based On Comparision Of Data Between Two Database Tables

I have below tables: I want to create View such that for descr = 'O' and for common id_isin field value from both tables, check the ratio field and take only the row where ratio f

Solution 1:

You need LEAST() function along with 3 subqueries combined with UNION clauses. Two of the subqueries should contain FULL JOIN among tables :

CREATEVIEW V_MEMBER_FUND ASSELECT 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'UNIONSELECT 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
  FULLJOIN 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 ISNULLOR i.fund_isin ISNULL )
UNIONSELECT t.fund_isin,
       t.member_descr,
       t.member_ratio,
       t.allocationassettype
  FROM IS_ID i
 RIGHTJOIN 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 ISNOTNULL

for the first case : Only need to return the minimum value in terms of member_ratio with i.member_descr = 'O' matches.

for the second case : Bidirectional(FULL JOIN) logic is told to be needed

for the third case : An outer join respective to the position of IS_ID_TST table(in the current case it's RIGHT JOIN)is needed along with non-equivalent values of member_desct to the value 'O'(even NULL values should be aliminated, and NVL() function is added for this aim )

And all those subqueries specified within the three cases should be combined with UNION in order to provide row-wise combination including the elimination of the repeated rows.

Demo

Solution 2:

I have come up with below query, Please check I have put comments on the query. Ask if doesn't meet you r requirement or for any clarification.

CREATEVIEW v_combined_data ASWITH combined_data
AS
(
SELECT t1.fund_isin
      ,t1.fund_quote_crny
      ,t1.member_descr
      ,t1.member_ratio
      ,t1.allocationassettype
      ,t2.fund_isin fund_isin_tst
      ,t2.fund_quote_crny fund_quote_crny_tst
      ,t2.member_descr member_descr_tst
      ,t2.member_ratio member_ratio_tst
      ,t2.allocationassettype allocationassettype_tst
FROM   is_id t1
FULLOUTERJOIN is_id_tst t2
ON     t1.fund_isin = t2.fund_isin
AND    t1.fund_quote_crny = t2.fund_quote_crny
AND    t1.member_descr = t2.member_descr
)
-- for member_descr = 'O' and for common fund_isin field value from both tables, -- check the member_ratio field and take only the row where member_ratio field value is low.SELECT d.fund_isin
      ,d.fund_quote_crny
      ,d.member_descr
      ,LEAST(d.member_ratio,d.member_ratio_tst) member_ratio
      ,d.allocationassettype
  FROM combined_data d
WHERE d.member_descr ='O'AND d.fund_isin ISNOTNULLAND d.fund_isin_tst ISNOTNULLUNIONALL--for member_descr = 'O' and if the fund_isin exist in one table but not in another then take those rows(bidirectional)--exists in IS_ID and not in IS_ID_TSTSELECT d.fund_isin
      ,d.fund_quote_crny
      ,d.member_descr
      ,d.member_ratio
      ,d.allocationassettype
  FROM combined_data d
WHERE d.member_descr ='O'ANDNOTEXISTS (SELECT1FROM combined_data ci
                   WHERE ci.fund_isin_tst = d.fund_isin
                     AND ci.fund_quote_crny_tst = d.fund_quote_crny
                     AND ci.member_descr_tst ='O')
UNIONALL--for member_descr = 'O' and if the fund_isin exist in one table but not in another then take those rows(bidirectional)--exists in IS_ID_TST and not in IS_IDSELECT d.fund_isin_tst
      ,d.fund_quote_crny_tst
      ,d.member_descr_tst
      ,d.member_ratio_tst
      ,d.allocationassettype_tst
  FROM combined_data d
WHERE d.member_descr_tst ='O'ANDNOTEXISTS (SELECT1FROM combined_data ci
                   WHERE ci.fund_isin = d.fund_isin_tst
                     AND ci.fund_quote_crny = d.fund_quote_crny_tst
                     AND ci.member_descr ='O')
UNIONALL--for all the rows where member_descr ! = 'O', take all those rows from table IS_ID_TSTSELECT d.fund_isin_tst
      ,d.fund_quote_crny_tst
      ,d.member_descr_tst
      ,d.member_ratio_tst
      ,d.allocationassettype_tst
  FROM combined_data d
WHERE d.fund_isin_tst ISNOTNULLAND (d.member_descr_tst !='O'OR d.member_descr_tst ISNULL);