07-14-2021 11:14 AM
I've been reading about a new feature, using WITH to create Common Table Expressions (CTEs), and am really scratching my head why I'd use them. As far as I can tell they don't provide much that I can't do today, at the expense of additional syntax complexity.
Using the example from the ABAP help:
WITH
+cities AS (
SELECT cityfrom AS city
FROM spfli
WHERE carrid = @carrid
UNION DISTINCT
SELECT cityto AS city
FROM spfli
WHERE carrid = @carrid )
SELECT *
FROM sgeocity
WHERE city IN ( SELECT city
FROM +cities )
INTO TABLE @DATA(result).
I can write this using good ol' subqueries to get the same result:
SELECT *
FROM sgeocity
WHERE city IN (
SELECT cityfrom AS city
FROM spfli
WHERE carrid = @carrid
UNION DISTINCT
SELECT cityto AS city
FROM spfli
WHERE carrid = @carrid )
INTO TABLE @DATA(result).
The more complex examples I can find go to a level of complexity that I would use CDS Views for because then the subqueries are independently testable.
Does anyone have a real world use case?
07-14-2021 11:45 AM
mike.pokraka
Hi Mike,
unfortunately, I don't have a real world use case as I haven't yet given them a try. However, I remember being rather mystified by the concept of CTEs when I joined a hands-on session at TechEd 2019 about them. That is, until I asked about their relationship with CDS-views. And - IIRC - CTEs might for example come in handy to prototype a CDS-view within code whenever you don't want to physically create an actual CDS view in the DDIC just yet. Not really sure if they have other real life use cases though.
Cheers
Bärbel
07-14-2021 12:49 PM
Thanks for the input. Even your example fits my argument that there are perfectly useable tried and tested alternatives. You can prototype in code using regular SQL or it takes less than a minute to create a CDS view as a local $tmp object.
Like you, I remain mystified.
07-14-2021 12:54 PM
Yeah, that's an option - at least if you don't have restrictions on using $tmp and a special setup for DDIC-changes! 🙂
07-14-2021 1:30 PM
sql - When to use Common Table Expression (CTE) - Stack Overflow
How things happen: you learn a concept, you don't know what it's for, and one day you have a complex thing to do that you can't accomplish using the classic way, and you remember of that concept, you try it and you see that it works...
Try to do that in another Open SQL (example from ABAP doc):
WITH
+conns AS (
SELECT carrname, connid, cityfrom, cityto
FROM spfli
JOIN scarr ON spfli~carrid = scarr~carrid
WHERE spfli~carrid = @carrid ),
+cnts AS (
SELECT COUNT(*) AS cnt
FROM +conns )
SELECT *
FROM +cnts
CROSS JOIN +conns
ORDER BY carrname, connid
INTO CORRESPONDING FIELDS of TABLE @itab.
Or this one:
WITH +texts AS (
SELECT trkorr, langu
FROM e07t
WHERE langu = @sy-langu
UNION
SELECT trkorr, MIN( langu )
FROM e07t
WHERE NOT exists ( SELECT * FROM e07t as b where trkorr = e07t~trkorr and langu = @sy-langu )
GROUP BY trkorr )
SELECT e070~trkorr, e070~as4date, e07t~as4text
FROM e070
INNER JOIN e07t
ON e07t~trkorr = e070~trkorr
INNER JOIN +texts
ON e07t~trkorr = +texts~trkorr
AND e07t~langu = +texts~langu
WHERE e070~trkorr NOT LIKE 'SAP%'
AND e070~strkorr = ''
INTO TABLE @DATA(requests).