I got into ABAP in 2007, due to some random skill assignment that worked its magic on the batch of 30 freshers to split us between SAP ABAP, SAP Security, and Mainframe.
Since then I have been writing SELECT queries in a certain way, avoiding JOINs, missing the initial check before FOR ALL ENTRIES, and adding it after debugging when the program would take forever. Well, I can go deep in memory lane, but that is not the point.
The point is that the SELECTs were not changed or upgraded for a long time. And one fine day, SAP enhances the Open SQL and adds many new features.
One such change is the use of the escape character ‘@’ for the host/program variables (variable/structure/table ). Earlier, we could use program variables in the SQL query as if SQL is part of ABAP itself. in ABAP 7.40 ‘@’ was introduced to explicitly mention that anything that follows @ is a program variable. This made it possible for the Open SQL parser to distinguish clearly between operands that are evaluated by the database and ABAP variables whose contents have to be passed to the database.
So with that, let us look at how a simple query should be written and how things can get really interesting. The syntax mentioned in this post refers to version 7.52 and a few things might not work in the earlier version.
Host Variable (@), comma-separated list, and inline declaration
The select field list is now separated by a comma, all variables are preceded by @ and a table is declared inline.
"Before 7.40 TYPES: BEGIN OF flight_type, carrid TYPE sbook-carrid, connid TYPE sbook-connid, fldate TYPE sbook-fldate, END OF flight_type. DATA result TYPE STANDARD TABLE OF flight_type. SELECT carrid connid fldate FROM sflight INTO TABLE result WHERE carrid IN s_carrid. "From 7.40 SELECT carrid, connid, fldate FROM sflight WHERE carrid IN @s_carrid INTO TABLE @DATA(result).
INTO clause is moved to the end of the statement as this is the new home for INTO clause. Most of the queries will still work but some of the queries containing arithmetic operations will ask you to move the clause to the end.
(If you are new to the inline declaration – check out the post ABAP 7.4 and beyond : Inline Data Declarations.
Change column names
It is also possible to use an alias for column names. The below query will create the table flights_tab with fields carrier, connection, and flight_date.
SELECT carrid AS carrier, connid AS connection, fldate AS flight_date FROM sflight WHERE carrid IN @s_carrid INTO TABLE @DATA(result).
Literals as additional columns
SELECT carrid, connid, fldate, 'Extra Column' AS new_column FROM sflight WHERE carrid IN @s_carrid INTO TABLE @DATA(result).
SELECT carrid, connid, fltime + 120 AS total_time DIVISION( fltime, 60, 2 ) AS fltime FROM spfli WHERE carrid IN @s_carrid INTO TABLE @DATA(result).
SELECT SINGLE CONCAT( char1, char2 ) AS concat, LENGTH( char1 ) AS length, LEFT( char1, 3 ) AS left, LOWER( char2 ) AS lower, UPPER( char2 ) AS upper, RIGHT( char1, 3 ) AS right, SUBSTRING( char1, 3, 3 ) AS substring, REPLACE( char1, ',' , '.' ) AS replace, CONCAT_WITH_SPACE( char1, char2, 1 ) AS concat_with_space FROM demo_expressions INTO @DATA(result).
SELECT SINGLE dats_is_valid( dats1 ) AS valid, dats_days_between( dats1, dats2 ) AS days_between, dats_add_days( dats1, 100 ) AS add_days, dats_add_months( dats1, -1 ) AS add_month FROM demo_expressions INTO @DATA(result).
Case Statements – Simple case
"Simple Case SELECT num1, CASE num1 WHEN 50 THEN 'Fifty' WHEN 20 THEN 'Twenty' ELSE 'Something Else' END AS group FROM demo_expressions INTO @DATA(result).
Case Statements – Complex Case
"Complex Case / Searched case SELECT num1, num2, CASE WHEN num1 < 50 AND num2 < 50 THEN 'Both less than 50' WHEN num1 >= 50 AND num2 >= 50 THEN 'Both more than 50' ELSE 'Others' END AS group FROM demo_expressions INTO @DATA(result).
An essential shift with HANA is the use of aggregate queries. Aggregate queries went from not recommended to recommended with HANA’s code pushdown approach.
A simple aggregate query is as below.
SELECT carrid, SUM( price ) AS total_price, FROM sflight GROUP BY carrid INTO TABLE @DATA(result).
Along with this, it is also possible to use the expressions we use as part of the field list in the GROUP BY… HAVING clause.
SELECT num1 + num2 AS sum, COUNT( * ) AS count FROM demo_expressions GROUP BY num1 + num2 HAVING COUNT( * ) > 10 ORDER BY sum INTO TABLE @DATA(result).
SELECT * FROM sflight USING CLIENT '100' WHERE carrid IN @s_carrid INTO TABLE @DATA(result).
To sum up, there is a lot that we could do in a select query and we should try to use the queries effectively to reduce the overhead from looping and merging the data.
ABAP 7.4 And Beyond  : Open SQL Enhancements Part 2 covers the changes in Joins and Select from Internal Table.
Read about more such ABAP expressions and exciting new syntaxes: ABAP Expressions (7.4+)
If you like the content, please subscribe…