ABAPIntermediate
Open SQL
Master Open SQL for fast, clean and HANA-friendly ABAP data access with joins, filters, aggregation, package processing and real project performance patterns.
Overview
Master Open SQL for fast, clean and HANA-friendly ABAP data access with joins, filters, aggregation, package processing and real project performance patterns.
Lessons in this topic
- Open SQL Part 1: How ABAP Reads the DatabaseUnderstand Open SQL (ABAP SQL): database-independent access to dictionary tables, client handling, the SELECT forms you will meet in classic code, and the classic vs new strict syntax.
- Open SQL Part 2: SELECT Basics — Field Lists, WHERE and INTOWrite correct, efficient SELECT statements: explicit field lists instead of SELECT *, WHERE conditions with ranges and patterns, ORDER BY, INTO TABLE vs INTO CORRESPONDING FIELDS, and UP TO n ROWS.
- Open SQL Part 3: FOR ALL ENTRIES — Correct UseUse FOR ALL ENTRIES safely: the empty-driver trap, duplicate removal, automatic duplicate elimination in the result, key fields in the field list, package splitting, and when a JOIN is better.
- Open SQL Part 4: SELECT SINGLE vs UP TO 1 ROWSRead exactly one row the right way: SELECT SINGLE with the full key, UP TO 1 ROWS with ORDER BY for "the first/latest" row, existence checks, buffering effects and the ATC warnings you will see.
- Open SQL Part 5: JOINs — When to Use and When to AvoidCombine tables in one statement: INNER JOIN vs LEFT OUTER JOIN, ON conditions with keys, aliases and the ~ selector, joins on buffered tables, and when JOIN, FOR ALL ENTRIES or a CDS view is the better choice.
- Open SQL Part 6: Aggregates, GROUP BY and HAVINGLet the database compute totals: COUNT, SUM, MIN, MAX, AVG, GROUP BY and HAVING, DISTINCT, and summing amounts correctly per currency — instead of loops and COLLECT in ABAP.
- Open SQL Part 7: PACKAGE SIZE and Large Data ProcessingProcess millions of rows without memory dumps or timeouts: SELECT … PACKAGE SIZE, OPEN CURSOR / FETCH, commit handling in long jobs, restartability and parallel processing basics.
- Open SQL Part 8: Performance Analysis with ST05, SAT and SQL MonitorFind slow SQL with evidence: record an SQL trace in ST05, read the statement summary and explain plan, detect identical selects and missing indexes, profile with SAT, and find system-wide hotspots with the SQL Monitor (SQLM).
- Open SQL Part 9: HANA-Friendly SQL and Code PushdownAdapt classic SQL habits for SAP HANA: fewer round trips, aggregation and calculation in the database, SQL expressions (CASE, CAST, string/date functions), subqueries, and when to move logic into CDS views.
- Open SQL Part 10: Open SQL in Real Reports and InterfacesPut it all together: a complete classic report and an interface extract with selection screen, authorization, efficient reads, joins, FOR ALL ENTRIES, totals, locking-aware updates and a review checklist.
Interview questions covered
- A SELECT statement into an internal table takes 30 seconds. After analyzing the code, you realize the WHERE clause doesn't use any indexed fields, and a full table scan is occurring. How would you refactor this, and what tools would you use to verify improvement?
- Design a robust error handling and monitoring strategy for a production Open SQL batch job that processes 10 million rows. Include recovery, logging, and performance tracking.
- Compare CDS Views vs. Open SQL for data retrieval in SAP S/4HANA. When would you recommend each approach?
- You are tasked with fetching 500,000 sales orders and their line items for a month-end batch. Describe your approach to avoid memory issues and database timeouts, including PACKAGE SIZE, batching, and commit strategy.
- You are debugging a SELECT query using ST05 (SQL Trace). Walk through the key metrics you would examine to diagnose performance issues and identify the root cause.
- Explain the GROUP BY and HAVING clauses in Open SQL. Provide an example that counts orders by customer and filters for customers with > 10 orders.
- Explain the relationship between index field order and WHERE-clause selectivity in Open SQL. Does the written order of conditions in the WHERE clause matter?
- Describe the ORDER BY clause in Open SQL. What is the default sort order, and how do you specify descending order for specific fields? What are the performance implications?
- You are designing a high-performance batch job to export 2 million sales orders to a data warehouse. Walk through your Open SQL strategy, including PACKAGE SIZE, indexing, and monitoring.
- Write an Open SQL SELECT INTO TABLE statement that fetches material master header data from MARA for a list of materials. Include proper field selection, WHERE clause, and no-data handling.
- Explain the difference between SELECT SINGLE and SELECT ... UP TO 1 ROWS in Open SQL. When would you use each, and what are the performance and determinism implications?
- A SELECT statement returns duplicate rows due to a JOIN with a table that has multiple matches. How would you identify this issue and resolve it?
- Explain aggregate functions in Open SQL (SUM, COUNT, AVG, MAX, MIN) and how they work with GROUP BY. Include how to handle NULL values.
- A SELECT query returns results, but you notice unexpected NULL values in certain fields. How would you handle NULL values in Open SQL, and what are the implications for business logic?
- A performance report queries sales data across 10 SAP tables. Explain your approach to optimize this using Open SQL, including when you would consider a CDS View or materialized aggregates.
- You notice that a SELECT statement with an IN operator and a large list (5000 values) is slow. Explain the performance issue and propose alternatives.
- Explain the concept of database pushdown in S/4HANA and how it influences your Open SQL design decisions.
- A report uses multiple SELECT statements inside a loop, fetching related data for each order. Redesign this using a single query with JOINs and explain the performance benefit.
- Your SELECT statement uses FOR ALL ENTRIES but the driver table is sometimes empty during execution. How does this affect the query result, and how would you code defensively?
- Describe how client handling (MANDT) works in Open SQL. When is client filtering automatic, and when must it be handled explicitly?