extend MERGE syntax to allow Common Table Expressions (CTE)

I have to agree with "Guest" about that RFE. Lately I wished several times that CTEs were supported in MERGE statements.

The benefits are obvious and the same as CTEs themselves : simplify and make statements more readable.

Without CTEs you have to write SELECTs inside FROM statements inside other FROM statements...

Below a recent example where I struggled to write the statement without a CTE :

merge into AMFLIBE.MOMAST MO

using (

select * from (

select M.REFNO, T.ORDNO, M.FITEM, M.FDESC, T.LT, round(T.LT, 0), M.ODUDT, M.ITRV, M.SSTDT

, (select CALDT from (select CALDT, row_number() over (order by CALDT) RN from AMFLIBE.CALNDR) as T2

where RN = (select RN from (select CALDT, row_number() over (order by CALDT) RN from AMFLIBE.CALNDR) as T3 where CALDT = M.ODUDT) - round(T.LT, 0) ) NEW_SSTDT

from AMFLIBE.MOMAST M

join (

select M.ORDNO, sum(W.STDQT) LT

from AMFLIBE.MOMAST M

join AMFLIBE.MOROUT R on M.ORDNO = R.ORDNO

join AMFLIBE.WRKCTR W on R.WKCTR = W.WKCTR

join PCFLIBE.PFCOMSL3 C on C.CUSPO = M.REFNO

where ...

group by M.ORDNO

) as T on M.ORDNO = T.ORDNO

) as T2

where SSTDT <> NEW_SSTDT

) as TMP on MO.ORDNO = TMP.ORDNO

when matched then update set MO.SSTDT = TMP.NEW_SSTDT

else ignore

and the same one with a CTE :

merge into AMFLIBE.MOMAST MO

using (

with TMPCAL as (

-- set row numbers for each calendar entry

select CALDT, row_number() over (order by CALDT) as RN from AMFLIBE.CALNDR

),

TMPORD as (

select M.ORDNO, sum(W.STDQT) LT

from AMFLIBE.MOMAST M

join AMFLIBE.MOROUT R on M.ORDNO = R.ORDNO

join AMFLIBE.WRKCTR W on R.WKCTR = W.WKCTR

join PCFLIBE.PFCOMSL3 C on C.CUSPO = M.REFNO

where ...

group by M.ORDNO

)

select M.REFNO, T.ORDNO, M.FITEM, M.FDESC, T.LT, round(T.LT, 0), M.ODUDT, M.ITRV, M.SSTDT

, (select CALDT from TMPCAL as T2

where RN = (select RN from TMPCAL where CALDT = M.ODUDT) - round(T.LT, 0) ) NEW_SSTDT

from AMFLIBE.MOMAST M

join TMPORD T on M.ORDNO = T.ORDNO

where SSTDT <> NEW_SSTDT

) as TMP on MO.ORDNO = TMP.ORDNO

when matched then update

set MO.SSTDT = TMP.NEW_SSTDT

else ignore

I'm actually using Db2 for i but I guess I'll get the fruits of seeing it added to Db2 for z sooner or later.

thanks for the status update and feedback. I suppose it is fine to reject this particular idea....

I might disagree with your reason for rejection... but whatever... (there is no method for me to have discussion with you ... to really explain the merits of an idea)

a last comment...... I would have appreciated more feedback during these last 10 months when this RFE was in "future consideration" status. I was wondering what you were thinking all this time...

Thank you for submitting this enhancement request. The Db2 for z/OS development team reviewed it and determined is not consistent with our product strategy, therefore, unfortunately, we are declining this request.

Please do continue to submit your enhancement ideas to us for consideration.

Sincerely,

The Db2 for z/OS Development team

I have updated the "how should it work" section with one example.

(unfortunately, the formatting gets lost when I paste into this tool... a pity)

> the example above is something that came up in my real life/work .. I was using MERGE and I had to use a "derived table" called "a".. but I think a CTE would have been easier to read ....

Do you want more examples?  I will think on another example

Hello Brian, Can you please provide examples of where you think the CTE would be useful in the MERGE syntax. Thanks.