What it is (and why it’s useful).
In ABAP Open SQL, a Common Table Expression (CTE) is a named, temporary “view-like” result defined with a WITH clause at the start of a query. Each CTE can be referenced by subsequent CTEs and by the main query, which makes complex logic easier to stage, read, and reuse without creating persistent DB objects.
SAP Help Portal+1
Version & feature notes.
CTEs arrived in Open SQL with ABAP 7.51 and have gained capabilities over time (e.g., associations, window functions under strict-mode checks in 7.54). ABAP SQL also supports WITH HIERARCHY for hierarchy handling via CTEs. Be sure your target system release supports what you plan to use.
SAP Community SAP Help Portal +1
A quick pattern (pseudo-example).
WITH cte_sales AS ( SELECT FROM vbak FIELDS vbeln, kunnr, erdat WHERE erdat >= @lv_from_date ), cte_items AS ( SELECT FROM vbap FIELDS vbeln, SUM( netwr ) AS amount, COUNT( * ) AS items GROUP BY vbeln ) SELECT FROM cte_sales AS s INNER JOIN cte_items AS i ON i.vbeln = s.vbeln FIELDS s.vbeln, s.kunnr, i.amount, i.items INTO TABLE @DATA(result).
to aggregate and retrieve sales order data from the VBAP table and aggregate values from VBAK based on kunnr (customer ID) and waerk (currency).
Great use cases.
- Stage complex logic: filter, aggregate, and join in steps instead of one monolithic SELECT. Easier to review and tune. SAP Help Portal
- Hierarchy queries: model parent-child rollups using WITH HIERARCHY rather than hand-crafting loops. SAP Help Portal
- Pushdown with app data: on newer platforms, ABAP SQL can join/CTE with internal tables as sources—handy for enriching DB data with in-memory selections. SAP Community
Do’s (best practices).
- Name clearly, scope tightly. Keep each CTE focused (filtering, aggregation, enrichment). Short, descriptive names help code reviews. (General guidance informed by ABAP docs.)
- Measure and compare. Use SQL Monitor/Performance tools to confirm CTE chains beat equivalent single-pass or CDS approaches for your data sizes. (General guidance.)
- Lean on newer features when available. If you’re on 7.54+, strict-mode features (e.g., CTE associations via WITH ASSOCIATIONS) can simplify joins while enforcing safer syntax.
SAP Help Portal - Parameterize with host variables. Keep CTEs reusable across business cases by feeding dates, org units, and flags via @ host variables. (General guidance.)
- Choose the right artifact. Prefer CDS views for reusable, cross-app semantics; prefer CTEs for query-local staging and readability. (General guidance aligned with SAP docs’ roles.)
Don’ts (common pitfalls).
- Don’t assume recursion. SAP HANA’s WITH does not support recursive query expressions; for hierarchies, use ABAP SQL hierarchy features instead. SAP Help Portal+1
- Don’t over-chain. Stacks of 8–10 CTEs can become slower or harder to reason about than a well-designed CDS or an indexed intermediate. Keep it pragmatic. (General guidance.)
- Don’t forget release parity. Features like WITH HIERARCHY, associations, or joins to internal tables require newer ABAP platform levels—guard with feature toggles or fallbacks.
Where CTEs shine in SAP scenarios.
- Sales & distribution analytics: pre-aggregate VBAP items, then join to header (VBAK) and business partner data for dashboard feeds. SAP Help Portal
- Material/BOM insights: build staged filters (e.g., active plants, valuation areas) and apply WITH HIERARCHY to roll up levels for summaries without temp tables. SAP Help Portal
- “Enrich then filter” patterns: use an internal table of current selections (e.g., top customers from a KPI app) as a CTE data source, then push the heavy join to the DB. SAP Community
Bottom line.
CTEs in ABAP Open SQL let you express multi-step logic cleanly, push computation to the database, and handle hierarchies with less boilerplate. Validate platform support, avoid recursive expectations, and pick CTEs or CDS based on reusability needs—and you’ll keep both performance and readability in the green.
SAP Help Portal
SAP Help Portal
