Skip to Content
1
Jul 14 at 10:14 AM

Does anyone use CTE / Common Table Expressions?

92 Views

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?