[SOLVED] Is there a way to include CASE WHEN or NVL in MERGE oracle sql?

Is there a way to include CASE WHEN or NVL in MERGE oracle sql?

I have a merge statement below and I want to add as a condition that if X_RECEIVED_ON_DT is null, it will use the date in FULFILLED_ON_DT and PO_CLOSED_DT, which are columns in the TGT (target table) to populate the TGT.X_GAAP_EXCH_RATE_WID column

 MERGE
/*+ PARALLEL(8) */
INTO W_PURCH_COST_F TGT USING
(SELECT
  /*+ PARALLEL(8) */
  cost.INTEGRATION_ID,
  cost.X_RECEIVED_ON_DT,
  cost.LOC_CURR_CODE,
  COALESCE(gaap.ROW_WID,0) X_GAAP_EXCH_RATE_WID
FROM W_Purch_Cost_F_3955 cost
JOIN W_DAY_D wday
ON TRUNC(cost.X_RECEIVED_ON_DT)=TRUNC(wday.CALENDAR_DATE)
LEFT OUTER JOIN WC_GAAP_EXCH_RATE_G gaap
ON gaap.PERIOD               =wday.PER_NAME_ENT_PERIOD
AND cost.LOC_CURR_CODE       =gaap.FROM_CURCY_CD
) SRC ON (TGT.INTEGRATION_ID = SRC.INTEGRATION_ID AND TGT.DATASOURCE_NUM_ID = 310)
WHEN MATCHED THEN
  UPDATE SET TGT.X_GAAP_EXCH_RATE_WID = SRC.X_GAAP_EXCH_RATE_WID;

Solution

If you want to reference W_PURCH_COST_F (alias TGT) in the source query, you'll have to include it into the SRC's FROM clause.

It means that you'd have two W_PURCH_COST_F tables in this MERGE statement - one as the merge target (as you already have now), another as "source" used to join it with other table(s) in SRC. Then it is a simple task to use NVL, CASE or DECODE and do what you want.

Though, I don't quite understand how you'll use both FULFILLED_ON_DT and PO_CLOSED_DT, but I hope you know.

Example based on Scott's schema (as I don't have your tables):

This won't work - you can't reference TGT in SRC:

SQL> merge into emp e
  2  using (select distinct d.deptno, d.dname, d.loc
  3         from dept d
  4         where d.deptno = e.deptno                --> not allowed
  5        ) x
  6  on (e.deptno = x.deptno)
  7  when matched then update set e.ename = x.loc;
       where d.deptno = e.deptno
                        *
ERROR at line 4:
ORA-00904: "E"."DEPTNO": invalid identifier

But, if used in SRC's FROM clause, it works:

SQL> merge into emp e
  2  using (select distinct d.deptno, d.dname, d.loc
  3         from dept d join emp a on a.deptno = d.deptno
  4        ) x
  5  on (e.deptno = x.deptno)
  6  when matched then update set e.ename = x.loc;

14 rows merged.

SQL>

Options you might want to consider are:

Nested NVL: nvl(x_received_on_dt, nvl(fulfilled_on_dt, po_closed_dt))

coalesce (returns first non-null value):

coalesce(x_received_on_dt, fulfilled_on_dt, po_closed_dt, sysdate)

coalesce looks like a better choice to me.