Skip to Main Content
IBM Data and AI Ideas Portal for Customers


This portal is to open public enhancement requests against products and services offered by the IBM Data & AI organization. To view all of your ideas submitted to IBM, create and manage groups of Ideas, or create an idea explicitly set to be either visible by all (public) or visible only to you and IBM (private), use the IBM Unified Ideas Portal (https://ideas.ibm.com).


Shape the future of IBM!

We invite you to shape the future of IBM, including product roadmaps, by submitting ideas that matter to you the most. Here's how it works:


Search existing ideas

Start by searching and reviewing ideas and requests to enhance a product or service. Take a look at ideas others have posted, and add a comment, vote, or subscribe to updates on them if they matter to you. If you can't find what you are looking for,


Post your ideas

Post ideas and requests to enhance a product or service. Take a look at ideas others have posted and upvote them if they matter to you,

  1. Post an idea

  2. Upvote ideas that matter most to you

  3. Get feedback from the IBM team to refine your idea


Specific links you will want to bookmark for future use

Welcome to the IBM Ideas Portal (https://www.ibm.com/ideas) - Use this site to find out additional information and details about the IBM Ideas process and statuses.

IBM Unified Ideas Portal (https://ideas.ibm.com) - Use this site to view all of your ideas, create new ideas for any IBM product, or search for ideas across all of IBM.

ideasibm@us.ibm.com - Use this email to suggest enhancements to the Ideas process or request help from IBM for submitting your Ideas.

IBM Data & AI Roadmaps (http://ibm.biz/Data-and-AI-Roadmaps) - Use this site to view roadmaps for Data & AI products.

IBM Employees should enter Ideas at https://hybridcloudunit-internal.ideas.aha.io/


Status Not under consideration
Workspace Db2 for z/OS
Created by Guest
Created on Oct 23, 2019

extend MERGE syntax to allow Common Table Expressions (CTE)

The MERGE SQL statement is a new favourite SQL statement.

But I also like to use Common Table Expressions (CTE). 

CTE can be used in other type of SQL (mostly SELECT) but CTE is not allowed with  MERGE

 

What is my idea?  Allow MERGE to use  CTE.

 

I think CTE with MERGE will allow my application developers (and me) to write more clear SQL.

 

This is a low priority idea.  There are always workarounds!   But it would be nice... it seems logical

  • Guest
    Sep 28, 2021

    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.

  • Guest
    Dec 8, 2020

    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...

  • Admin
    Janet Figone
    Dec 3, 2020

    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

  • Guest
    Feb 13, 2020

    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

  • Admin
    Janet Figone
    Feb 13, 2020

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