Module 17: Advanced Open SQL (HANA Optimized)

Advanced Open SQL enables powerful, expressive, and HANA-optimized database access directly from ABAP, while still maintaining database portability.

This module focuses on writing SQL that pushes work to the database, not the application server.

---

1. New Open SQL Syntax (7.4+)

Modern Open SQL is expression-oriented, strict, and HANA-aware.

Key Improvements

• Mandatory host variables (@)
• SQL expressions
• Inline declarations
• Subqueries
• Case expressions
• Strict syntax checks

Why New Open SQL Matters

It enables code pushdown while remaining DB-independent.

---

2. Host Variables (@)

Host variables clearly separate ABAP variables from database fields.

---

Legacy (Implicit Host Variables)

SELECT * FROM spfli
  WHERE carrid = lv_carrid.

Modern (Explicit Host Variables)

SELECT * FROM spfli
  WHERE carrid = @lv_carrid
  INTO TABLE @DATA(lt_spfli).

Clean ABAP Rule

Always use @ for host variables — it is mandatory in new syntax.

3. SQL Expressions

SQL expressions allow calculations inside the SELECT statement.

Arithmetic Expression

SELECT carrid,
       price * seats AS revenue
  FROM zflight
  INTO TABLE @DATA(lt_result).

String Expression

SELECT carrid,
       CONCAT( carrid, connid ) AS flight_key
  FROM spfli
  INTO TABLE @DATA(lt_result).

Pushdown Principle

If the database can compute it, do not compute it in ABAP.

4. Subqueries

Subqueries allow nested SELECTs inside SQL statements.

IN Subquery

SELECT * FROM spfli
  WHERE carrid IN (
    SELECT carrid FROM scarr
    WHERE currcode = 'EUR'
  )
  INTO TABLE @DATA(lt_spfli).

EXISTS Subquery

SELECT * FROM scarr AS c
  WHERE EXISTS (
    SELECT * FROM spfli AS f
      WHERE f~carrid = c~carrid
  )
  INTO TABLE @DATA(lt_scarr).

When to Use Subqueries

• Complex filtering

• Avoid multiple SELECTs

• Improve readability

5. CASE Expressions

CASE expressions allow conditional logic inside SQL.

Simple CASE

SELECT carrid,
       CASE currcode
         WHEN 'EUR' THEN 'Europe'
         WHEN 'USD' THEN 'USA'
         ELSE 'Other'
       END AS region
  FROM scarr
  INTO TABLE @DATA(lt_result).

Searched CASE

SELECT carrid,
       CASE
         WHEN price > 1000 THEN 'High'
         WHEN price BETWEEN 500 AND 1000 THEN 'Medium'
         ELSE 'Low'
       END AS price_category
  FROM zflight
  INTO TABLE @DATA(lt_result).

Advantage

CASE expressions reduce post-processing loops in ABAP.

6. Database Portability

Open SQL ensures code runs on any SAP-supported database.

How Portability Is Preserved

• SAP abstracts database specifics

• Unsupported SQL features are blocked

• Same code runs on HANA and non-HANA DBs

Portability Trap

Using Native SQL breaks database portability.

Open SQL vs Native SQL

Aspect	Open SQL	Native SQL
Portability	✅	❌
Optimization	SAP-managed	Developer-managed
HANA Ready	✅	❌
Usage	Recommended	Rare cases only

Interview Rule

Always prefer Open SQL → CDS → AMDP in that order.

7. Performance Best Practices (HANA Optimized)

Follow These Rules

• Push calculations to DB

• Use JOINs instead of nested SELECTs

• Use CASE & SQL expressions

• Reduce data transfer volume

• Select only required fields

8. Common Mistakes

Avoid These

• Missing @ before host variables

• Using SELECT * unnecessarily

• Computing data in ABAP instead of SQL

• Using Native SQL without justification

• Writing HANA-specific SQL in Open SQL

9. Summary

• New Open SQL is explicit and strict

• Host variables improve clarity

• SQL expressions enable pushdown

• Subqueries reduce multiple SELECTs

• CASE expressions simplify logic

• Open SQL preserves DB portability

10. Practice Exercises

• Rewrite a SELECT using host variables.

• Move calculations from ABAP to SQL.

• Use CASE expression to categorize data.

• Replace nested SELECTs with subqueries.

• Validate portability of your SQL.

11. What’s Next?

➡️ Module 18: Code Pushdown Concept

Learning Tip

If you master Advanced Open SQL, CDS and AMDP will feel natural.