# ABAP New Open SQL Syntax Reference by Release

> Source: https://netray.co/tools/abap-new-open-sql-syntax-by-release-reference
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Look up each ABAP SQL feature with the classic form, the new syntax and the SAP_BASIS release that first allows it, from 7.40 SP05 to 7.58.

## Category

developer

## Erp

SAP NetWeaver / Basis

## Audience

- ABAP developer
- SAP technical lead
- Code reviewer

## Tool

**Kind:** worksheet

**Mode:** reference

**Columns:**

#### Feature

**Key:** feature

**Type:** text

#### Classic syntax

**Key:** classic

**Type:** text

#### New syntax example

**Key:** modern

**Type:** text

#### Minimum release (SAP_BASIS)

**Key:** release

**Type:** text

#### Notes

**Key:** notes

**Type:** text

**Rows:**

**Feature:** Comma-separated lists

**Classic:** SELECT matnr mtart FROM mara INTO TABLE lt_mara.

**Modern:** SELECT matnr, mtart FROM mara INTO TABLE @lt_mara.

**Release:** 7.40 SP05

**Notes:** Commas switch the statement to strict mode. Blank-separated lists are obsolete.

**Feature:** @ escape for host variables

**Classic:** WHERE matnr = lv_matnr

**Modern:** WHERE matnr = @lv_matnr

**Release:** 7.40 SP05

**Notes:** Once one host variable carries @, the whole statement is checked in strict mode and every host variable needs @.

**Feature:** USING CLIENT

**Classic:** CLIENT SPECIFIED WHERE mandt = lv_client

**Modern:** SELECT ... FROM mara USING CLIENT @lv_client ...

**Release:** 7.40 SP05

**Notes:** Switches automatic client handling to another client instead of turning it off.

**Feature:** Inline declaration, internal table

**Classic:** DATA lt_mara TYPE TABLE OF mara. SELECT * FROM mara INTO TABLE lt_mara.

**Modern:** SELECT matnr, mtart FROM mara WHERE mtart = 'FERT' INTO TABLE @DATA(lt_mara).

**Release:** 7.40 SP08

**Notes:** The news page lists inline declarations after INTO under SP08, not SP05. The row type is derived from the SELECT list.

**Feature:** Inline declaration, single fields

**Classic:** INTO (lv_matnr, lv_mtart)

**Modern:** INTO (@DATA(lv_matnr), @DATA(lv_mtart))

**Release:** 7.40 SP08

**Notes:** Each target needs its own @DATA( ).

**Feature:** INTO clause as last clause

**Classic:** SELECT ... INTO TABLE lt WHERE ... ORDER BY ...

**Modern:** SELECT ... WHERE ... ORDER BY ... INTO TABLE @lt UP TO 10 ROWS.

**Release:** 7.40 SP08

**Notes:** When INTO is last, UP TO and OFFSET must follow INTO.

**Feature:** Top n rows (no TOP keyword)

**Classic:** SELECT * FROM vbak UP TO 10 ROWS INTO TABLE lt ORDER BY erdat DESCENDING.

**Modern:** SELECT vbeln, erdat FROM vbak ORDER BY erdat DESCENDING INTO TABLE @DATA(lt_top) UP TO 10 ROWS.

**Release:** UP TO: all releases. Position after INTO: 7.40 SP08

**Notes:** ABAP SQL has no SELECT TOP. Without ORDER BY the n rows returned are arbitrary.

**Feature:** SELECT SINGLE vs UP TO 1 ROWS

**Classic:** SELECT SINGLE * FROM mara WHERE matnr = lv_matnr.

**Modern:** SELECT vbeln FROM vbak WHERE kunnr = @lv_kunnr ORDER BY erdat DESCENDING INTO @DATA(lv_last) UP TO 1 ROWS. ENDSELECT.

**Release:** All releases

**Notes:** Use SINGLE with the full key. Use UP TO 1 ROWS with ORDER BY when you need the first row of a sort. A work area target with UP TO needs ENDSELECT.

**Feature:** OFFSET (skip rows)

**Classic:** Not available: read and DELETE lines in ABAP

**Modern:** SELECT vbeln FROM vbak ORDER BY vbeln INTO TABLE @DATA(lt_page) UP TO 50 ROWS OFFSET 100.

**Release:** 7.51

**Notes:** Use with ORDER BY so pages are stable. ORDER BY with UP TO and OFFSET in subqueries came in 7.52.

**Feature:** Simple CASE

**Classic:** Read, then CASE in a LOOP

**Modern:** CASE vbtyp WHEN 'C' THEN 'ORDER' ELSE 'OTHER' END AS doc_kind

**Release:** 7.40 SP05 (SQL expressions in SELECT list)

**Notes:** SQL expressions in the SELECT list need comma-separated lists.

**Feature:** Searched CASE

**Classic:** IF in a LOOP

**Modern:** CASE WHEN netwr > 10000 THEN 'HIGH' ELSE 'LOW' END AS band

**Release:** 7.40 SP08

**Notes:** SP08 added complex (searched) case and SQL expressions after GROUP BY.

**Feature:** Arithmetic expressions

**Classic:** Compute in a LOOP after the read

**Modern:** SELECT ebeln, ebelp, menge - wemng AS open_qty FROM eket INTO TABLE @DATA(lt_open).

**Release:** 7.40 SP05

**Notes:** Integer, decimal and floating point expressions follow different type rules. Use DIVISION for decimals.

**Feature:** CAST

**Classic:** MOVE to a typed field in ABAP

**Modern:** CAST( menge AS FLTP ) AS menge_f

**Release:** In 7.40 docs with FLTP target only; cast matrix extended in 7.53, 7.54 and 7.56

**Notes:** Check the cast matrix of your release before casting to CHAR, DEC or DATS.

**Feature:** COALESCE

**Classic:** IF field IS INITIAL after an outer join

**Modern:** coalesce( p~posnr, '000000' ) AS posnr

**Release:** 7.40 (two arguments); up to 255 arguments from 7.50

**Notes:** Returns the first argument that is not the null value. Useful after LEFT OUTER JOIN.

**Feature:** String functions (first set)

**Classic:** CONCATENATE, SHIFT, strlen( ) in ABAP

**Modern:** CONCAT( vbeln, posnr ) AS item_key, SUBSTRING( matnr, 1, 10 ) AS short_matnr, LENGTH( maktx ) AS len

**Release:** 7.50

**Notes:** 7.50 added CONCAT, LPAD, LENGTH, LTRIM, REPLACE, RIGHT, RTRIM, SUBSTRING and ROUND.

**Feature:** String functions (second set)

**Classic:** TRANSLATE ... TO UPPER CASE in ABAP

**Modern:** UPPER( maktx ) AS maktx_uc, LEFT( matnr, 4 ) AS prefix

**Release:** 7.51

**Notes:** 7.51 added UPPER, LOWER, LEFT, CONCAT_WITH_SPACE, INSTR, RPAD and DIVISION.

**Feature:** Date functions

**Classic:** Date arithmetic in ABAP

**Modern:** DATS_DAYS_BETWEEN( erdat, @sy-datum ) AS age_days

**Release:** 7.51

**Notes:** Also DATS_IS_VALID, DATS_ADD_DAYS, DATS_ADD_MONTHS. More date and time functions came in 7.53 and 7.55.

**Feature:** Aggregates with GROUP BY and HAVING

**Classic:** SELECT kunnr SUM( netwr ) FROM vbak INTO TABLE lt GROUP BY kunnr.

**Modern:** SELECT kunnr, SUM( netwr ) AS total FROM vbak GROUP BY kunnr HAVING SUM( netwr ) > 1000 INTO TABLE @DATA(lt_sum).

**Release:** Aggregates: all releases. SQL expressions as aggregate arguments: 7.40 SP08

**Notes:** Columns in HAVING outside an aggregate must also be in GROUP BY. GROUPING SETS from 7.53, STRING_AGG from 7.54.

**Feature:** UNION / UNION ALL

**Classic:** Two SELECTs plus APPEND LINES

**Modern:** SELECT matnr FROM mara WHERE mtart = 'FERT' UNION ALL SELECT matnr FROM mara WHERE mtart = 'HALB' INTO TABLE @DATA(lt_all).

**Release:** 7.50

**Notes:** INTO goes after the last SELECT. UNION without ALL removes duplicates.

**Feature:** INTERSECT / EXCEPT

**Classic:** Two reads and a compare in ABAP

**Modern:** SELECT kunnr FROM vbak INTERSECT SELECT kunnr FROM vbrk INTO TABLE @DATA(lt_both).

**Release:** 7.56

**Notes:** Listed as new set operators in the 7.56 news. Not available on SAP_BASIS 7.50 to 7.55.

**Feature:** Common table expression WITH

**Classic:** Nested reads into internal tables

**Modern:** WITH +big AS ( SELECT kunnr, SUM( netwr ) AS total FROM vbak GROUP BY kunnr ) SELECT kunnr, total FROM +big WHERE total > 100000 INTO TABLE @DATA(lt_big).

**Release:** 7.51

**Notes:** CTE names start with +. Only the main query has an INTO clause.

**Feature:** CROSS JOIN

**Classic:** Nested LOOPs

**Modern:** SELECT t~werks, m~matnr FROM t001w AS t CROSS JOIN mara AS m INTO TABLE @DATA(lt_x) UP TO 100 ROWS.

**Release:** 7.51

**Notes:** Result size is rows(left) x rows(right). Restrict it.

**Feature:** Multiple LEFT OUTER JOINs

**Classic:** Inner joins only, or several reads

**Modern:** SELECT a~vbeln, b~posnr, c~maktx FROM vbak AS a LEFT OUTER JOIN vbap AS b ON b~vbeln = a~vbeln LEFT OUTER JOIN makt AS c ON c~matnr = b~matnr AND c~spras = @sy-langu INTO TABLE @DATA(lt_j).

**Release:** 7.40 SP05

**Notes:** SP05 lifted join restrictions, including WHERE on right-hand table fields of a LEFT OUTER JOIN.

**Feature:** FIELDS clause (FROM first)

**Classic:** SELECT f1 f2 FROM tab

**Modern:** SELECT FROM mara FIELDS matnr, mtart WHERE mtart = 'FERT' INTO TABLE @DATA(lt_mara).

**Release:** 7.50

**Notes:** Puts FROM first so code completion knows the data source.

**Feature:** Host expressions @( )

**Classic:** Compute into a variable first

**Modern:** WHERE erdat >= @( sy-datum - 30 )

**Release:** 7.50

**Notes:** Allowed in SQL expression arguments, after UP TO, and on right sides of WHERE, ON and HAVING except LIKE and IN.

**Feature:** Range tables with IN

**Classic:** WHERE matnr IN s_matnr

**Modern:** WHERE matnr IN @s_matnr

**Release:** IN: all releases. @ form: 7.40 SP05

**Notes:** An empty range selects all rows. Very large ranges can exceed database statement limits.

**Feature:** FOR ALL ENTRIES with new syntax

**Classic:** SELECT vbeln posnr FROM vbap INTO TABLE lt_vbap FOR ALL ENTRIES IN lt_vbak WHERE vbeln = lt_vbak-vbeln.

**Modern:** IF lt_vbak IS NOT INITIAL. SELECT vbeln, posnr FROM vbap FOR ALL ENTRIES IN @lt_vbak WHERE vbeln = @lt_vbak-vbeln INTO TABLE @DATA(lt_vbap). ENDIF.

**Release:** FAE: all releases. @ form: 7.40 SP05

**Notes:** An empty driver table makes the WHERE condition ignored and returns every row. Duplicate result rows are removed.

**Feature:** INTO CORRESPONDING FIELDS OF TABLE

**Classic:** INTO CORRESPONDING FIELDS OF TABLE lt

**Modern:** SELECT * FROM mara WHERE mtart = 'FERT' INTO CORRESPONDING FIELDS OF TABLE @lt_short.

**Release:** Behaviour change in 7.40 SP05

**Notes:** From SP05 the SELECT list sent to the database is reduced to matching names when they are known statically.

**Feature:** All columns of one source in a join

**Classic:** List every column

**Modern:** SELECT a~*, b~posnr FROM vbak AS a INNER JOIN vbap AS b ON b~vbeln = a~vbeln INTO TABLE @DATA(lt_ab).

**Release:** 7.40 SP08

**Notes:** data_source~* cannot be combined with aggregate expressions.

**Feature:** Internal table as data source

**Classic:** LOOP and READ TABLE

**Modern:** SELECT matnr, menge FROM @lt_items AS i WHERE menge > 0 INTO TABLE @DATA(lt_pos).

**Release:** 7.52

**Notes:** Needs an alias. Joins of several internal tables without database tables run in the ABAP SQL engine from 7.58.

**Feature:** IS [NOT] INITIAL in WHERE

**Classic:** WHERE lifnr = space

**Modern:** WHERE lifnr IS INITIAL

**Release:** 7.53

**Notes:** Compares with the type-dependent initial value. Triggers the 7.53 strict mode.

**Feature:** Path expressions on CDS associations

**Classic:** Explicit join

**Modern:** SELECT FROM zi_salesorder FIELDS salesorder, \_customer-customername INTO TABLE @DATA(lt_so).

**Release:** 7.50

**Notes:** Example uses custom CDS names. The association must be exposed in the view. Filters and parameters in paths came in 7.52.

**Notes:** Release cells follow the ABAP Keyword Documentation news pages (Open SQL in Release 7.40 SP05, SP08, 7.50, 7.51, 7.52; ABAP SQL in 7.53 to 7.58). Check SAP_BASIS in System > Status. Each new feature switches the statement into the strict mode of its release.

## Content

**Intro:** This reference lines up classic and new ABAP SQL syntax for the features developers search for most, with the SAP_BASIS release that first allows each one. Use it to check whether a statement copied from a blog will compile on your 7.40, 7.50 or S/4HANA system.

**Sections:**

#### How to use it

**Body:** Search the table for the feature you need, such as OFFSET, CASE or FROM @itab, then compare the release cell with your system. Find your release in System > Status > Component information, row SAP_BASIS: 740 with its SP level, or 750 to 758. ECC 6.0 on EhP7 or EhP8 usually runs SAP_BASIS 740 or 750, while S/4HANA 1909 and later run 754 or higher. If your release is lower than the cell, the statement will not compile and you need the classic form.

**Bullets:**

- SAP_BASIS 740 SP05 is the floor for commas, @ and SQL expressions.
- Inline declarations after INTO need 740 SP08.
- UNION, FIELDS, host expressions and string functions need 750.

#### Version differences and gotchas

**Body:** Two release facts catch people out. First, inline declarations INTO TABLE @DATA( ) came with 7.40 SP08, not SP05, so a 740 SP05 system rejects them. Second, ABAP SQL has no SELECT TOP: you write ORDER BY and UP TO n ROWS, and from SP08 when INTO is the last clause UP TO goes after INTO. Every new feature also switches the statement into the strict mode of its release, so warnings in the rest of the statement become errors. Mixing a blank-separated list with an @ variable is the classic example.

**Bullets:**

- INTERSECT and EXCEPT only exist from 7.56.
- IS INITIAL inside WHERE needs 7.53.
- Joins of several internal tables without database tables run in the ABAP SQL engine from 7.58.

#### Worked example: top 10 orders, page 3

**Body:** To show the newest ten sales orders you write SELECT vbeln, erdat FROM vbak ORDER BY erdat DESCENDING INTO TABLE @DATA(lt_top) UP TO 10 ROWS. For the third page of 50 orders on 7.51 or later, add OFFSET 100 after UP TO 50 ROWS. On 7.50 or lower there is no OFFSET: read UP TO 150 ROWS and delete the first 100 lines in ABAP, or use a cursor with PACKAGE SIZE. Always sort on a unique key, or rows with the same date can move between pages.

#### How to check the result in the system

**Body:** Paste the statement into a test report in SE38 or ADT and run the syntax check. A message saying the addition is not supported points to the release limit, while strict mode errors usually come from a host variable without @. For performance, run the statement with ST05 SQL trace and read the statement the database actually received. With INTO CORRESPONDING FIELDS the trace shows the reduced field list. With FOR ALL ENTRIES it shows the split into several statements.

**Cta:** If you want developers to ask your SAP system questions in plain English and get checked ABAP SQL back, Netray can run that assistant on your own servers.

**Faq:**

#### How do I write SELECT TOP 1 in ABAP?

**Answer:** There is no TOP keyword. Use SELECT SINGLE when you have the full key. For the first row of a sort, use ORDER BY with UP TO 1 ROWS, for example SELECT vbeln FROM vbak WHERE kunnr = @lv_kunnr ORDER BY erdat DESCENDING INTO @DATA(lv_last) UP TO 1 ROWS. ENDSELECT. A work area target with UP TO needs ENDSELECT.

#### Can I use CASE in an ABAP SELECT?

**Answer:** Yes. Simple CASE in the SELECT list works from 7.40 SP05 and searched CASE (CASE WHEN condition THEN) from 7.40 SP08. The statement must use comma-separated lists and @ host variables. Give the result an alias with AS so an inline declaration has a component name.

#### How do I select from an internal table in ABAP SQL?

**Answer:** From SAP_BASIS 7.52 you write SELECT ... FROM @itab AS alias. The table needs an alias and must be a host variable with @. On some databases the internal table data has to be sent to the database, so use it for small tables or joins.

#### How do I skip rows in an ABAP SELECT?

**Answer:** Use OFFSET n after UP TO, available from 7.51, together with ORDER BY on a unique key so each page is stable. Below 7.51, read more rows and delete the leading lines in ABAP, or use OPEN CURSOR with FETCH NEXT CURSOR and PACKAGE SIZE.

## Search terms

abap select top 1, abap select case, abap select cast, abap select into table inline declaration, abap select from internal table, abap select top skip, abap new open sql syntax, abap sql release 7.50 features

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json