How to use Function inside of MERGE USING clause in Oracle?

I have a query like this that is merging data into a view via a private temp table.

MERGE INTO MY_VIEW V
USING
(
     SELECT DISTINCT ID,
          Column1
          Column2
          Column3
          FUNCTION.returnNumberValue(R.Column1, R.Column2, R.Column3) AS Value
     FROM MY_TEMP_TABLE R
) T
ON (T.ID = V.ID)
WHEN MATCHED THEN UPDATE SET
VALUE = T.Value

The function itself is pretty simple, just doing basic algebra and returning a number result. It looks something like this.

FUNCTION returnNumberValue(PARAM1 IN NUMBER, PARAM2 IN NUMBER, PARAM3 IN NUMBER) RETURN NUMBER IS
     value NUMBER;
     BEGIN
          IF PARAM1 > 0 THEN
               PARAM2 + PARAM3;
          ELSE
               value := 0;
          ENDIF;
          RETURN value;
     END returnNumberValue;

However, I keep getting an error:

ORA-30926 unable to get a stable set of rows in the source tables

I've read that this is due to some duplicates being found in the source, but as you can see I'm already using SELECT DISTINCT which should address this issue. I'm wondering how I can accomplish something like this.

I've also tried doing a group by instead of using distinct, as well as having this value already exist in my temp table but I still get the same error. I also don't think the temp table is the issue as it works fine if I just assign a number value there, like using '999', but use the other values that I need. Is there anyway to resolve this?

Solution

Do a GROUP BY on the column you will be joining on in the ON clause, to ensure it is unique (DISTINCT won't do it). That will require an aggregation on the other columns returned, of which you apparently only need the function result.

MERGE INTO MY_VIEW V
USING
(
     SELECT ID,
          MAX(FUNCTION.returnNumberValue(R.Column1, R.Column2, R.Column3)) AS Value
     FROM MY_TEMP_TABLE R
    GROUP BY ID
) T
ON (T.ID = V.ID)
WHEN MATCHED THEN UPDATE SET
VALUE = T.Value

If you want other logic than simply the max value, you can use analytical function like ROW_NUMBER using any logic you want to prioritize the rows having the same ID, then feed only that row into the function. E.g.:

MERGE INTO MY_VIEW V
USING
(
     SELECT ID,
            FUNCTION.returnNumberValue(Column1, Column2, Column3) AS Value
       FROM (SELECT ID,
                    R.Column1, R.Column2, R.Column3,
                    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ...[prioritizing logic here]...) seq
               FROM MY_TEMP_TABLE R)
    WHERE seq = 1
) T
ON (T.ID = V.ID)
WHEN MATCHED THEN UPDATE SET
VALUE = T.Value

But however you do it, you must end up with only one row per ID.