3 Replies Latest reply on Oct 31, 2018 6:46 PM by Jason_(A_Non)

    Remove duplicate rows

    3679117

      Hello! I have a query which is returning multiple duplicate rows.

      Query is as follows:

      SELECT
      
      --,TO_CHAR(TO_DATE(LAST_DAY(ADD_MONTHS(CURRENT_DATE,-1)), 'DD-MON-YY'), 'YYYYMM') "YEAR_MONTH"
      --,extractValue(d.XMLRECORD,'/row/c181[position()=1]') "CONTRACT_ID"
      ,d.RECID "CONTRACT_ID"
      ,b.RECID "COLLATERAL_ID"
      ,(REGEXP_SUBSTR(b.RECID,'[^.]+',1,1)) "CUSTOMER_ID"
      ,NVL(extractValue(b.XMLRECORD,'/row/c32[@m="28"]'),'OA6') "COLLATERAL_TYPE"
      ,NVL(extractValue(b.XMLRECORD,'/row/c32[@m="29"]'),'Y') "COLLATERAL_OWNERSHIP"
      ,NVL(GET_AMOUNT_REVAL(extractValue(b.XMLRECORD,'/row/c7[position()=1]'),'RWF',extractValue(b.XMLRECORD,'/row/c9[position()=1]')),0) "COLLATERAL_AMOUNT_LCY"
      ,NVL(TO_CHAR(TO_DATE(extractValue(b.XMLRECORD,'/row/c32[@m="2"]'), 'YYYYMMDD'), 'DD-MON-YYYY'),TO_CHAR(TO_DATE('20180531', 'YYYYMMDD'), 'DD-MON-YYYY')) "COLLATERAL_LAST_VALUATION_DATE"
      ,NVL(TO_CHAR(TO_DATE(extractValue(b.XMLRECORD,'/row/c17[position()=1]'), 'YYYYMMDD'), 'DD-MON-YYYY'),TO_CHAR(TO_DATE('20180531', 'YYYYMMDD'), 'DD-MON-YYYY')) "COLLATERAL_EXPIRY_DATE"
      ,NVL(extractValue(b.XMLRECORD,'/row/c11[position()=1]'),0) "COLLATERAL_DISCOUNT_AMT_LCY"
      ,NVL(extractValue(b.XMLRECORD,'/row/c32[@m="30"]'),0) "COLLATERAL_DISCOUNT_RATE"
      ,NVL(extractValue(b.XMLRECORD,'/row/c9[position()=1]'),0) "COLLATERAL_MARKET_VALUE"
      ,NVL(extractValue(b.XMLRECORD,'/row/c32[@m="9"]'),0) "UPI_NUMBER"
      ,NVL(extractValue(b.XMLRECORD,'/row/c32[@m="6"]'),'NA') "NAME_PROPERTY_VALUER"
      ,NVL(extractValue(b.XMLRECORD,'/row/c32[@m="31"]'),0) "REG_NUMBER_VALUER"
      ,NVL(extractValue(b.XMLRECORD,'/row/c32[@m="32"]'),0) "RDB_REG_NUMBER"
      ,extractValue(b.XMLRECORD,'/row/c32[@m="33"]') "LTV_RATIO"
      ,DECODE(extractValue(b.XMLRECORD,'/row/c32[@m="24"]'),NULL,'N','Y') "INSURED"
      ,NVL(TO_CHAR(TO_DATE(extractValue(a.XMLRECORD,'/row/c32[@m="25"]'), 'YYYYMMDD'), 'DD-MON-YYYY'),TO_CHAR(TO_DATE('20180531', 'YYYYMMDD'), 'DD-MON-YYYY')) "INSURANCE_EXPIRY_DATE"
      ,NVL(extractValue(b.XMLRECORD,'/row/c32[@m="34"]'),'NA') "GAURANTEE_ISSUER"
      ,TO_CHAR(TO_DATE(extractValue(b.XMLRECORD,'/row/c41[position()=1]'),'YYMMDDHH24MI'),'DD-MON-YYYY HH24:MI:SS') "DATE_LAST_MODIFIED"
      
      
      FROM FBNK_COLLATERAL_RIGHT a,FBNK_COLLATERAL b, FBNK_LIMIT c, FBNK_ACCOUNT d
      --,XMLTABLE ('for $i in /row/c3 return $i' PASSING a.XMLRECORD) t
      
      
      WHERE a.RECID = (REGEXP_SUBSTR(b.RECID,'[^.]+',1,1) || '.' || REGEXP_SUBSTR(b.RECID,'[^.]+',1,2))
      --AND c.RECID = EXTRACTVALUE(t.COLUMN_VALUE, '/c3')
      --AND c.RECID = extractValue(a.XMLRECORD,'/row/c3[position()=1]')
      AND extractValue(c.XMLRECORD,'/row/c91[position()=1]') = extractValue(a.XMLRECORD,'/row/c3[position()=1]')
      AND d.RECID = extractValue(c.XMLRECORD,'/row/c91[position()=1]')
      AND b.RECID IN
      (
      SELECT MIN(w.RECID) FROM FBNK_COLLATERAL_RIGHT v,FBNK_COLLATERAL w, FBNK_LIMIT x, FBNK_ACCOUNT y
      WHERE v.RECID = (REGEXP_SUBSTR(w.RECID,'[^.]+',1,1) || '.' || REGEXP_SUBSTR(w.RECID,'[^.]+',1,2))
      AND extractValue(x.XMLRECORD,'/row/c91[position()=1]') = extractValue(v.XMLRECORD,'/row/c3[position()=1]')
      AND y.RECID = extractValue(x.XMLRECORD,'/row/c91[position()=1]')
      GROUP BY (y.RECID)
      )
      ORDER BY d.RECID
      

       

      How do I modify the query to not return duplicate rows?