Gettin error ORA-01732: data manipulation operation not legal on this view

Question

Hi Gurus I am getting the error ORA-01732: data manipulation operation not legal on this view

when executing the below query

UPDATE (SELECT CR.AMOUNT AS AMOUNT,
                  CASE
                  WHEN MRG.AMOUNT_USD=0
                  THEN CR.AMOUNT
                  ELSE MRG.AMOUNT_USD
                  END AS AMOUNT_BILAT,
                  CR.ISUPDATED
                  FROM CRS_TT_BILAT_EXCL_MERGE1 MRG,CRS_T_CURRENT_RATES1 CR
                  WHERE SUBSTR(CR.DNIS_CD,1,3)=MRG.DNIS_CD
                  AND CR.PRODUCT_CUST_ID = MRG.PRODUCT_CUST_ID
                  AND CR.ISUPDATED <> 'Y'
                  AND ROWNUM = 1)
                  SET AMOUNT = AMOUNT_BILAT;
                  CR.ISUPDATED = 'Y';

I have simplified the above code from the below query

UPDATE CRS_T_CURRENT_RATES1 CR
        SET CR.AMOUNT =
          (SELECT 
                  CASE
                  WHEN MRG.AMOUNT_USD=0
                  THEN CR.AMOUNT
                  ELSE MRG.AMOUNT_USD
                  END
                  FROM CRS_TT_BILAT_EXCL_MERGE1 MRG
                  WHERE SUBSTR(CR.DNIS_CD,1,3)=MRG.DNIS_CD
                  AND CR.PRODUCT_CUST_ID = MRG.PRODUCT_CUST_ID
                  AND ROWNUM = 1),

                  CR.ISUPDATED = 'Y'

           WHERE EXISTS
            (SELECT 1 FROM CRS_TT_BILAT_EXCL_MERGE1 MRG WHERE MRG.DNIS_CD = SUBSTR(CR.DNIS_CD, 1,3) AND CR.PRODUCT_CUST_ID = MRG.PRODUCT_CUST_ID )
            AND
            CR.ISUPDATED <> 'Y';

I was trying to optimize the 2nd query, since the second query uses two selects i was trying to replace that with a single query. Can any one please help me on this?

Answer 1

UPDATE: Using MERGE and ROWNUM won't work.

MERGE can help you avoid repeating SQL:

MERGE INTO CRS_T_CURRENT_RATES1 CR
USING
(
    SELECT AMOUNT_USD, DNIS_CD, PRODUCT_CUST_ID
    FROM CRS_TT_BILAT_EXCL_MERGE1
)  MRG
    ON
    (
        SUBSTR(CR.DNIS_CD,1,3) = MRG.DNIS_CD
        AND CR.PRODUCT_CUST_ID = MRG.PRODUCT_CUST_ID
        AND ROWNUM = 1  
    )
WHEN MATCHED THEN UPDATE SET
    CR.ISUPDATED = 'Y',
    CR.AMOUNT = CASE WHEN MRG.AMOUNT_USD=0 THEN CR.AMOUNT ELSE MRG.AMOUNT_USD END

---

Update

ROWNUM in the ON clause works on the updated table, not the data in the USING clause. The example below starts with two identical rows and only one gets updated:

create table test1(a number, b number);
insert into test1 values(1, 1);
insert into test1 values(1, 1);

merge into test1
using
(
    select 1 a from dual
) test2
    on (test1.a = test2.a and rownum = 1)
when matched then update set b = 0;

select * from test1;

A  B
-  -
1  0
1  1

Answer 2

MERGE statement with selecting 1st row for each (AMOUNT_USD, DNIS_CD, PRODUCT_CUST_ID) - ROWNUM=1 condition from your query:

MERGE INTO CRS_T_CURRENT_RATES1 CR
USING (SELECT * FROM (
          SELECT AMOUNT_USD, 
                 DNIS_CD, 
                 PRODUCT_CUST_ID
                 ROW_NUMBER() OVER (PARTITION BY AMOUNT_USD, DNIS_CD, PRODUCT_CUST_ID ORDER BY 1) AS ORD_NO
          FROM CRS_TT_BILAT_EXCL_MERGE1
          ) WHERE ORD_NO = 1
      ) MGR
ON CR.PRODUCT_CUST_ID = MRG.PRODUCT_CUST_ID AND
   SUBSTR(CR.DNIS_CD,1,3)=MRG.DNIS_CD 
WHEN MATCHED THEN
   UPDATE SET CR.AMOUNT = (CASE
                              WHEN MRG.AMOUNT_USD=0 THEN CR.AMOUNT
                              ELSE MRG.AMOUNT_USD
                          END),
                          ISUPDATED = 'Y'
   WHERE ISUPDATED <> 'Y';