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;
If you want to reference
W_PURCH_COST_F
(aliasTGT
) in the source query, you'll have to include it into theSRC
'sFROM
clause.It means that you'd have two
W_PURCH_COST_F
tables in thisMERGE
statement - one as the merge target (as you already have now), another as "source" used to join it with other table(s) inSRC
. Then it is a simple task to useNVL
,CASE
orDECODE
and do what you want.Though, I don't quite understand how you'll use both
FULFILLED_ON_DT
andPO_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
inSRC
:But, if used in
SRC
'sFROM
clause, it works: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
looks like a better choice to me.