Skip to main content

Module 6: Open SQL Basics

Open SQL is SAP’s database-independent SQL interface used in ABAP programs to access database tables and views.
It ensures portability, security, and consistency across different database platforms.

1. What is Open SQL?​

Open SQL is a subset of SQL standardized by SAP.

Key Characteristics​

  • Database independent
  • Automatically optimized by SAP
  • Integrated with authorization checks
  • Works the same on all supported databases
Open SQL vs Native SQL

Open SQL should be used in 99% of cases.
Native SQL is database-specific and should be avoided unless absolutely necessary.

2. SELECT Statement – Basic Syntax​

Basic SELECT​

SELECT * FROM spfli
  INTO TABLE @DATA(lt_spfli).

Selecting Specific Fields​

SELECT carrid, connid
  FROM spfli
  INTO TABLE @DATA(lt_flights).
Best Practice

Always select only the required fields, never use SELECT * in productive code.

3. WHERE Clause​

The WHERE clause filters records at the database level.

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

Using Logical Operators:​

WHERE carrid = 'LH'
  AND connid > '0400'.
Performance Rule

Filtering in ABAP after selecting data is inefficient. Always filter using WHERE in the SELECT.

4. ORDER BY Clause​

Sorts the result set at the database level.

SELECT * FROM spfli
  INTO TABLE @DATA(lt_spfli)
  ORDER BY carrid, connid.
ORDER BY vs SORT

ORDER BY → Database level

SORT → Application server level

5. GROUP BY Clause​

Used with aggregate functions.

Aggregate Functions​

COUNT( )

SUM( )

MIN( )

MAX( )

AVG( )

SELECT carrid, COUNT(*) AS flight_count
  FROM spfli
  INTO TABLE @DATA(lt_count)
  GROUP BY carrid.
GROUP BY Rule

All non-aggregated fields in SELECT must appear in GROUP BY.

6. INTO TABLE vs INTO CORRESPONDING FIELDS​

INTO TABLE​

SELECT * FROM spfli
  INTO TABLE @DATA(lt_spfli).

INTO CORRESPONDING FIELDS OF TABLE​

SELECT carrid, connid
  FROM spfli
  INTO CORRESPONDING FIELDS OF TABLE @DATA(lt_flights).
When to Use CORRESPONDING

Use INTO CORRESPONDING when field names match but structures differ.

7. JOIN Basics​

7.1 INNER JOIN​

Returns records that exist in both tables.

SELECT a~carrid, a~connid, b~fldate
  FROM spfli AS a
  INNER JOIN sflight AS b
    ON a~carrid = b~carrid
   AND a~connid = b~connid
  INTO TABLE @DATA(lt_result).

7.2 LEFT OUTER JOIN​

Returns all records from left table, even if no match exists in right table.

SELECT a~carrid, a~connid, b~fldate
  FROM spfli AS a
  LEFT JOIN sflight AS b
    ON a~carrid = b~carrid
   AND a~connid = b~connid
  INTO TABLE @DATA(lt_result).
Join Best Practice

Prefer JOINs over nested SELECTs for better performance.

8. SELECT SINGLE vs SELECT UP TO 1 ROWS​

8.1 SELECT SINGLE​

  • Fetches one arbitrary matching record
  • Does not guarantee which record is returned unless full key is specified
SELECT SINGLE * FROM spfli
  INTO @DATA(ls_spfli)
  WHERE carrid = 'LH'.
Common Misunderstanding

SELECT SINGLE does not mean first record unless key is fully specified.

8.2 SELECT UP TO 1 ROWS​

  • Fetches one record
  • Can be combined with ORDER BY
SELECT * FROM spfli
  INTO @DATA(ls_spfli)
  WHERE carrid = 'LH'
  ORDER BY connid
  UP TO 1 ROWS.
ENDSELECT.
Correct Usage

Use UP TO 1 ROWS with ORDER BY when you need a specific record.

9. Performance Best Practices​

Follow These Rules

  • Avoid SELECT *

  • Use proper WHERE conditions

  • Prefer JOINs over nested SELECTs

  • Avoid SELECT inside loops

  • Use correct SELECT variant

10. Common Beginner Mistakes​

Avoid These

  • Using SELECT SINGLE without full key

  • Sorting large datasets in ABAP

  • Fetching unnecessary fields

  • Multiple SELECTs instead of JOIN

11. Summary​

  • Open SQL is database-independent

  • SELECT syntax is simple but powerful

  • WHERE, ORDER BY, GROUP BY optimize data retrieval

  • JOINs improve performance

  • SELECT variant choice is critical

12. Practice Exercises​

  • Select all flights for a specific airline.

  • Count flights per airline using GROUP BY.

  • Fetch latest flight using UP TO 1 ROWS.

  • Compare SELECT SINGLE vs UP TO 1 ROWS.

  • Replace nested SELECT with JOIN.

13. What’s Next?​

➡️ Module 7: Modularization Techniques

Learning Tip

Strong Open SQL knowledge is the foundation of HANA-optimized ABAP.