IBM Data and AI Ideas Portal for Clients

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:

Post your ideas

If you have not registered on this portal please register at http://ibm.biz/IBM-Data-and-AI-Portal-Register . To complete registration you will need to open the email you will receive from Aha to confirm your identity.

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

Help IBM prioritize your ideas and requests

The IBM team may need your help to refine the ideas so they may ask for more information or feedback. The offering manager team will then decide if they can begin working on your idea. If they can start during the next development cycle, they will put the idea on the priority list. Each team at IBM works on a different schedule, where some ideas can be implemented right away, others may be placed on a different schedule.

Receive notification on the decision

Some ideas can be implemented at IBM, while others may not fit within the development plans for the product. In either case, the team will let you know as soon as possible. In some cases, we may be able to find alternatives for ideas which cannot be implemented in a reasonable time.

Additional Information

To view our roadmaps: http://ibm.biz/Data-and-AI-Roadmaps

Reminder: This is not the place to submit defects or support needs, please use normal support channel for these cases

IBM Employees:

The correct URL for entering your ideas is: https://hybridcloudunit-internal.ideas.aha.io

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

  • Avatar32.5fb70cce7410889e661286fd7f1897de Guest
  • Oct 23 2019
  • Under Review
Who would benefit from this IDEA? application developers (and DBAS) who want to use MERGE.
How should it work?

allow CTE to be used with MERGE.

 

It should behave like CTE with SELECT.  I think it is obvious...

 

Below is ONE simple example.  

I think more complex examples are obvious once you start to think about it

-------------------------------------------------------------------
-- example of MERGE with derived table
-- >> the sql might be subjectively more clear if it could used CTE
-- this sql merge example is part of a script that I run
-- this MERGE will not run on it's own...
--- but I think it is a decent example

-- in this MERGE SQL... I must derive table "a" to find
-- db&ts&dsnum from syscopy .. and then I use the accompanying
-- image copy dsname to update my dgtt

MERGE into
session.dgtt_last_fic d
using (
select dbname, tsname, dsnum, ictype, shrlevel, icbackup
, timestamp as ts
, dsname, JOBNAME, AUTHID
from sysibm.syscopy
where 1=1
AND DBNAME =
(SELECT DISTINCT SRC_DBNAME FROM SESSION.DGTT_TS_TB)
AND TSNAME IN (SELECT TSNAME FROM SESSION.DGTT_TS_TB)
AND ICTYPE = 'F' AND ICBACKUP = ' '
and timestamp > (current timestamp - 7 days)
) a
on (d.SRC_dbname=a.dbname and d.tsname = a.tsname and d.dsnum=a.dsnum
and d.ictype = a.ictype and d.shrlevel = a.shrlevel
and d.icbackup = a.icbackup
and d.ts = a.ts)
when matched then
update
set (d.FIC_DSNAME, d.FIC_JOBNAME, d.authid)
= (a.dsname, a.jobname, a.authid)
;

-------------------------------------------------------------------
-- now here is essentially the same MERGE but attempting to
-- use a CTE.. (again called "a")
-- >> as someone who creates SQL... I find the following "easier"
-- to read... it seems logical to me..
with a as (
select dbname, tsname, dsnum, ictype, shrlevel, icbackup
, timestamp as ts
, dsname, JOBNAME, AUTHID
from sysibm.syscopy
where 1=1
AND DBNAME =
(SELECT DISTINCT SRC_DBNAME FROM SESSION.DGTT_TS_TB)
AND TSNAME IN (SELECT TSNAME FROM SESSION.DGTT_TS_TB)
AND ICTYPE = 'F' AND ICBACKUP = ' '
and timestamp > (current timestamp - 7 days)
)
MERGE into
session.dgtt_last_fic d
using a
on (d.SRC_dbname=a.dbname and d.tsname = a.tsname and d.dsnum=a.dsnum
and d.ictype = a.ictype and d.shrlevel = a.shrlevel
and d.icbackup = a.icbackup
and d.ts = a.ts)
when matched then
update
set (d.FIC_DSNAME, d.FIC_JOBNAME, d.authid)
= (a.dsname, a.jobname, a.authid)
;
-------------------------------------------------------------------

Idea Priority Low
Client Name Brian Laube
  • Attach files
  • Avatar40.8f183f721a2c86cd98fddbbe6dc46ec9
    Guest commented
    13 Feb 02:05pm

    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 commented
    13 Feb 12:36am

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


IBM's success depends on gathering feedback from customers like yourself. Aha Ideas Portal is the third party tool through which IBM Offering Managers gather feedback from customers such as yourself.
IBM is a global organization with business processes, management structures, technical systems and service provider networks that cross borders. As such, the information collected through Aha Ideas Portal (Customer Name, Customer Email Address) will be stored by them in the United States, and handled only as per IBM's instructions and policies. Your data (Name and Email Address) will NOT be shared with other IBM customers.
In order to safeguard your information in Aha, do not leave your workstation unattended while using this application, log off after using it, and print only if necessary. If you need to make a hardcopy, remember to pick up the print-out immediately, keep it under lock, and destroy it immediately when no longer needed.
NOTICE TO EU RESIDENTS: per EU Data Protection Policy, if you wish to remove your personal information from the IBM ideas portal, please login to the ideas portal using your previously registered information then change your email to "anonymous@euprivacy.out" and first name to "anonymous" and last name to "anonymous". This will ensure that IBM will not send any emails to you about all idea submissions