Developer ToolsFree referencePDF, Excel, Word exportSAP NetWeaver / BasisAll SAP HANA and Basis tools

ABAP New Open SQL Syntax Reference by Release

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.

Download:

Feature	Classic syntax	New syntax example	Minimum release (SAP_BASIS)	Notes
Comma-separated lists	SELECT matnr mtart FROM mara INTO TABLE lt_mara.	SELECT matnr, mtart FROM mara INTO TABLE @lt_mara.	7.40 SP05	Commas switch the statement to strict mode. Blank-separated lists are obsolete.
@ escape for host variables	WHERE matnr = lv_matnr	WHERE matnr = @lv_matnr	7.40 SP05	Once one host variable carries @, the whole statement is checked in strict mode and every host variable needs @.
USING CLIENT	CLIENT SPECIFIED WHERE mandt = lv_client	SELECT ... FROM mara USING CLIENT @lv_client ...	7.40 SP05	Switches automatic client handling to another client instead of turning it off.
Inline declaration, internal table	DATA lt_mara TYPE TABLE OF mara. SELECT * FROM mara INTO TABLE lt_mara.	SELECT matnr, mtart FROM mara WHERE mtart = 'FERT' INTO TABLE @DATA(lt_mara).	7.40 SP08	The news page lists inline declarations after INTO under SP08, not SP05. The row type is derived from the SELECT list.
Inline declaration, single fields	INTO (lv_matnr, lv_mtart)	INTO (@DATA(lv_matnr), @DATA(lv_mtart))	7.40 SP08	Each target needs its own @DATA( ).
INTO clause as last clause	SELECT ... INTO TABLE lt WHERE ... ORDER BY ...	SELECT ... WHERE ... ORDER BY ... INTO TABLE @lt UP TO 10 ROWS.	7.40 SP08	When INTO is last, UP TO and OFFSET must follow INTO.
Top n rows (no TOP keyword)	SELECT * FROM vbak UP TO 10 ROWS INTO TABLE lt ORDER BY erdat DESCENDING.	SELECT vbeln, erdat FROM vbak ORDER BY erdat DESCENDING INTO TABLE @DATA(lt_top) UP TO 10 ROWS.	UP TO: all releases. Position after INTO: 7.40 SP08	ABAP SQL has no SELECT TOP. Without ORDER BY the n rows returned are arbitrary.
SELECT SINGLE vs UP TO 1 ROWS	SELECT SINGLE * FROM mara WHERE matnr = lv_matnr.	SELECT vbeln FROM vbak WHERE kunnr = @lv_kunnr ORDER BY erdat DESCENDING INTO @DATA(lv_last) UP TO 1 ROWS. ENDSELECT.	All releases	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.
OFFSET (skip rows)	Not available: read and DELETE lines in ABAP	SELECT vbeln FROM vbak ORDER BY vbeln INTO TABLE @DATA(lt_page) UP TO 50 ROWS OFFSET 100.	7.51	Use with ORDER BY so pages are stable. ORDER BY with UP TO and OFFSET in subqueries came in 7.52.
Simple CASE	Read, then CASE in a LOOP	CASE vbtyp WHEN 'C' THEN 'ORDER' ELSE 'OTHER' END AS doc_kind	7.40 SP05 (SQL expressions in SELECT list)	SQL expressions in the SELECT list need comma-separated lists.
Searched CASE	IF in a LOOP	CASE WHEN netwr > 10000 THEN 'HIGH' ELSE 'LOW' END AS band	7.40 SP08	SP08 added complex (searched) case and SQL expressions after GROUP BY.
Arithmetic expressions	Compute in a LOOP after the read	SELECT ebeln, ebelp, menge - wemng AS open_qty FROM eket INTO TABLE @DATA(lt_open).	7.40 SP05	Integer, decimal and floating point expressions follow different type rules. Use DIVISION for decimals.
CAST	MOVE to a typed field in ABAP	CAST( menge AS FLTP ) AS menge_f	In 7.40 docs with FLTP target only; cast matrix extended in 7.53, 7.54 and 7.56	Check the cast matrix of your release before casting to CHAR, DEC or DATS.
COALESCE	IF field IS INITIAL after an outer join	coalesce( p~posnr, '000000' ) AS posnr	7.40 (two arguments); up to 255 arguments from 7.50	Returns the first argument that is not the null value. Useful after LEFT OUTER JOIN.
String functions (first set)	CONCATENATE, SHIFT, strlen( ) in ABAP	CONCAT( vbeln, posnr ) AS item_key, SUBSTRING( matnr, 1, 10 ) AS short_matnr, LENGTH( maktx ) AS len	7.50	7.50 added CONCAT, LPAD, LENGTH, LTRIM, REPLACE, RIGHT, RTRIM, SUBSTRING and ROUND.
String functions (second set)	TRANSLATE ... TO UPPER CASE in ABAP	UPPER( maktx ) AS maktx_uc, LEFT( matnr, 4 ) AS prefix	7.51	7.51 added UPPER, LOWER, LEFT, CONCAT_WITH_SPACE, INSTR, RPAD and DIVISION.
Date functions	Date arithmetic in ABAP	DATS_DAYS_BETWEEN( erdat, @sy-datum ) AS age_days	7.51	Also DATS_IS_VALID, DATS_ADD_DAYS, DATS_ADD_MONTHS. More date and time functions came in 7.53 and 7.55.
Aggregates with GROUP BY and HAVING	SELECT kunnr SUM( netwr ) FROM vbak INTO TABLE lt GROUP BY kunnr.	SELECT kunnr, SUM( netwr ) AS total FROM vbak GROUP BY kunnr HAVING SUM( netwr ) > 1000 INTO TABLE @DATA(lt_sum).	Aggregates: all releases. SQL expressions as aggregate arguments: 7.40 SP08	Columns in HAVING outside an aggregate must also be in GROUP BY. GROUPING SETS from 7.53, STRING_AGG from 7.54.
UNION / UNION ALL	Two SELECTs plus APPEND LINES	SELECT matnr FROM mara WHERE mtart = 'FERT' UNION ALL SELECT matnr FROM mara WHERE mtart = 'HALB' INTO TABLE @DATA(lt_all).	7.50	INTO goes after the last SELECT. UNION without ALL removes duplicates.
INTERSECT / EXCEPT	Two reads and a compare in ABAP	SELECT kunnr FROM vbak INTERSECT SELECT kunnr FROM vbrk INTO TABLE @DATA(lt_both).	7.56	Listed as new set operators in the 7.56 news. Not available on SAP_BASIS 7.50 to 7.55.
Common table expression WITH	Nested reads into internal tables	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).	7.51	CTE names start with +. Only the main query has an INTO clause.
CROSS JOIN	Nested LOOPs	SELECT t~werks, m~matnr FROM t001w AS t CROSS JOIN mara AS m INTO TABLE @DATA(lt_x) UP TO 100 ROWS.	7.51	Result size is rows(left) x rows(right). Restrict it.
Multiple LEFT OUTER JOINs	Inner joins only, or several reads	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).	7.40 SP05	SP05 lifted join restrictions, including WHERE on right-hand table fields of a LEFT OUTER JOIN.
FIELDS clause (FROM first)	SELECT f1 f2 FROM tab	SELECT FROM mara FIELDS matnr, mtart WHERE mtart = 'FERT' INTO TABLE @DATA(lt_mara).	7.50	Puts FROM first so code completion knows the data source.
Host expressions @( )	Compute into a variable first	WHERE erdat >= @( sy-datum - 30 )	7.50	Allowed in SQL expression arguments, after UP TO, and on right sides of WHERE, ON and HAVING except LIKE and IN.
Range tables with IN	WHERE matnr IN s_matnr	WHERE matnr IN @s_matnr	IN: all releases. @ form: 7.40 SP05	An empty range selects all rows. Very large ranges can exceed database statement limits.
FOR ALL ENTRIES with new syntax	SELECT vbeln posnr FROM vbap INTO TABLE lt_vbap FOR ALL ENTRIES IN lt_vbak WHERE vbeln = lt_vbak-vbeln.	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.	FAE: all releases. @ form: 7.40 SP05	An empty driver table makes the WHERE condition ignored and returns every row. Duplicate result rows are removed.
INTO CORRESPONDING FIELDS OF TABLE	INTO CORRESPONDING FIELDS OF TABLE lt	SELECT * FROM mara WHERE mtart = 'FERT' INTO CORRESPONDING FIELDS OF TABLE @lt_short.	Behaviour change in 7.40 SP05	From SP05 the SELECT list sent to the database is reduced to matching names when they are known statically.
All columns of one source in a join	List every column	SELECT a~*, b~posnr FROM vbak AS a INNER JOIN vbap AS b ON b~vbeln = a~vbeln INTO TABLE @DATA(lt_ab).	7.40 SP08	data_source~* cannot be combined with aggregate expressions.
Internal table as data source	LOOP and READ TABLE	SELECT matnr, menge FROM @lt_items AS i WHERE menge > 0 INTO TABLE @DATA(lt_pos).	7.52	Needs an alias. Joins of several internal tables without database tables run in the ABAP SQL engine from 7.58.
IS [NOT] INITIAL in WHERE	WHERE lifnr = space	WHERE lifnr IS INITIAL	7.53	Compares with the type-dependent initial value. Triggers the 7.53 strict mode.
Path expressions on CDS associations	Explicit join	SELECT FROM zi_salesorder FIELDS salesorder, \_customer-customername INTO TABLE @DATA(lt_so).	7.50	Example uses custom CDS names. The association must be exposed in the view. Filters and parameters in paths came in 7.52.

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.

Check a statement against your release

Send us an ABAP SQL statement and your SAP_BASIS level and we will tell you what compiles, what triggers strict mode and the fallback syntax.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

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.

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

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.

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

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

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.

Frequently Asked Questions

How do I write SELECT TOP 1 in ABAP?

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?

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?

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?

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.

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.

Talk to an Expert Explore More Free Tools