from (Select ipic.invoice_id,ipic.Condition_Type_Cd,ipic.Item_Amt,ipic.item_nr
from T2 ipic
WHERE ipic.Invoice_Id IN (SELECT invoice_id from T1
AND Sales_Date BETWEEN '2012-02-05' AND '2012-03-03'
Surcharge=(CASE WHEN IPI.Condition_Type_Cd IN ('NV','VT') THEN IPI.Item_Amt ELSE 0 END)
,Restocking_Fee=(CASE WHEN IPI.Condition_Type_Cd IN ('ZRSF') THEN IPI.Item_Amt ELSE 0 END)
AND T1.Invoice_Item_Nr = IPI.item_nr ;
This query gives the following error: *** Failure 7547 Target row updated by multiple source rows.
There might be more than one value trying to update the Surcharge/Restocking_Fee
AND T1.Invoice_Item_Nr = IPI.item_nr
is fetching more than one row.
Try giving sel * from, instead of update in the same query and check the records that have multiple values.
you can also check whether where condition is proper/ end_date is null conditione need to be applied if table is type 2.
Yes, invoice_id and Item_nr is not unique in IPI.
You can run the following to get the duplicates
This is the most common error for duplicate resords coming from lower stage or from source...if you dont have the choice to add another column to make it unique...try to apply the qualify function by differiantiating the corruct record using the row number() function...