Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Does anyone use CTE / Common Table Expressions?

pokrakam
Active Contributor

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?

4 REPLIES 4

BaerbelWinkler
Active Contributor

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

0 Kudos

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.

0 Kudos

Yeah, that's an option - at least if you don't have restrictions on using $tmp and a special setup for DDIC-changes! 🙂

Sandra_Rossi
Active Contributor

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