This post is a continuation of ABAP 7.4 And Beyond [6] : Open SQL Enhancements Part 1. In this post, you will learn about open SQL enhancements introduced after ABAP 7.40 release.

Select with Joins

JOINs have always been there and we have been avoiding them and preferred the ‘FOR ALL ENTRIES’. Not anymore, we can now use joins effectively with SAP HANA. We now have RIGHT OUTER JOIN as well.

Inner Join

Join the table with the ON condition. Inner join means, the data is selected only if data is found in both tables.

DATA(lv_city_from) = 'BERLIN'.

SELECT c~carrname, p~connid
  FROM scarr AS c INNER JOIN spfli AS p
    ON p~carrid   = c~carrid AND
       p~cityfrom = @lv_city_from
  INTO TABLE @DATA(lt_flights).

Left Outer Join

Here, scarr is the left-table and SPFLI is the right-table. The data is selected from the left-table and if a matching record is found in the right-table, fields from the right-table are populated, else they have an initial value.

DATA(lv_city_from) = 'BERLIN'.

SELECT c~carrname, p~connid
 FROM scarr AS c LEFT OUTER JOIN spfli AS p
 ON p~carrid   = c~carrid AND
    p~cityfrom = @lv_city_from
 INTO TABLE @DATA(lt_flights).

Coalesce

For outer joins, if you need to add a specific value for the field where the entry in the outer table does not exist – Coalesce can be used.

DATA(lv_city_from) = 'BERLIN'.

SELECT c~carrname, coalesce( p~connid, '9999' ) as connid
 FROM scarr AS c LEFT OUTER JOIN spfli AS p
 ON p~carrid   = c~carrid AND
    p~cityfrom = @lv_city_from
 INTO TABLE @DATA(lt_flights).

Right Outer Join

Here as well, scarr is the left-table and SPFLI is the right-table. The data is selected from the right table and if a matching record is found in the left-table, fields from the left-table are populated, else they have an initial value.

DATA(lv_city_from) = 'BERLIN'.

SELECT c~carrname, p~connid
  FROM scarr AS c RIGHT OUTER JOIN spfli AS p
    ON p~carrid = c~carrid AND
       p~cityfrom = @lv_city_from
  INTO TABLE @DATA(lt_flights).

Select all fields from a table in Join

Now, you can specify fields to be selected using the keyword FIELD and also use <tablename>-* to indicate that all fields from that table should be selected.

SELECT 
  FROM sflight INNER JOIN spfli
  ON sflight~carrid = spfli~carrid AND 
     sflight~connid = spfli~connid
  FIELDS spfli~*, sflight~fldate
  INTO TABLE @DATA(result).

UNION in SELECT

The addition of UNION creates the union of the results sets of two SELECT statements. The rows of the results set of the SELECT statement on the right of UNION are inserted into the results set of the SELECT statement on the left of UNION. The columns of the results set keep the names defined in the SELECT statement on the left of UNION.

OPEN SQL offers Union from 7.50.

"Before 7.50
SELECT schedule~carrid, schedule~connid, schedule~fldate, 
       flight~cityfrom, flight~cityto
  FROM sflight AS schedule INNER JOIN spfli AS flight
    ON schedule~carrid = flight~carrid AND 
       schedule~connid = flight~connid
  WHERE flight~cityfrom = 'FRANKFURT'
  INTO TABLE @DATA(lt_flights).

SELECT schedule~carrid, schedule~connid, schedule~fldate,
       flight~cityfrom, flight~cityto
 FROM sflight AS schedule INNER JOIN spfli AS flight
   ON schedule~carrid = flight~carrid AND 
      schedule~connid = flight~connid
  WHERE flight~cityfrom = 'SAN FRANCISCO'
  APPENDING TABLE @lt_flights.

"From 7.50
SELECT schedule~carrid, schedule~connid, schedule~fldate,
       flight~cityfrom, flight~cityto
  FROM sflight AS schedule INNER JOIN spfli AS flight
    ON schedule~carrid = flight~carrid AND 
       schedule~connid = flight~connid
    WHERE flight~cityfrom = 'FRANKFURT'
 
UNION ALL

SELECT schedule~carrid, schedule~connid, schedule~fldate,
       flight~cityfrom, flight~cityto
 FROM sflight AS schedule INNER JOIN spfli AS flight
   ON schedule~carrid = flight~carrid AND 
      schedule~connid = flight~connid
 WHERE flight~cityfrom = 'SAN FRANCISCO'
 
INTO TABLE @DATA(lt_flights).

Notice that the INTO clause moves to the end. So it would be a good habit if we move the clause to the end for all the queries. Also, if we want only the unique records, we can use UNION DISTINCT instead of UNION ALL. ( If nothing is specified after UNION, the default mode used is DISTINCT ). 

Important:

  1. The Union can also work on selecting data from different tables as long as the same fields are selected.
  2. UNION queries can not use FOR ALL ENTRIES
  3. AGGREGATEs can be used in UNION queries

SELECT FROM INTERNAL_TABLE

It’s all CAPS, so am I yelling. Yes, I am. With 7.52 you can query an Internal Table.  Here is an example.

"Select from Database Tables
SELECT schedule~carrid AS carrier_id,
       schedule~connid AS connection_id,
       schedule~fldate AS flight_date,
       flight~cityfrom AS city_from,
       flight~cityto   AS city_to
  FROM sflight AS schedule INNER JOIN spfli AS flight
    ON schedule~carrid = flight~carrid AND
       schedule~connid = flight~connid
  INTO TABLE @DATA(lt_flights).
"Select from Internal Table
SELECT carrier_id, 
       connection_id, 
       flight_date
  FROM @lt_flights AS flights
  WHERE city_from = 'FRANKFURT'
  INTO TABLE @DATA(lt_new_flights).

As we are selecting from the internal table we need to use the escape character @ for the host variable and mention the alias using AS.

FIELD Keyword

The field list in the query can be also specified with the keyword FIELD. This allows you to specify fields after the FROM clause which is similar to how we write CDS.

SELECT SINGLE
  FROM @lt_flights AS flights
  FIELDS carrier_id, connection_id
  WHERE flight_date = @sy-datum AND
        city_from = 'FRANKFURT'
  INTO @DATA(todays_flight).

Temporary table using the clause WITH (7.51+)

Using the keyword ‘WITH’, a dataset is created which can be further used in the query. We can have multiple such named datasets and they all need to start with +.

WITH
  +cities AS ( SELECT cityfrom AS city FROM spfli WHERE carrid = 'LH'
               UNION DISTINCT
               SELECT cityto   AS city FROM spfli WHERE carrid = 'LH' )
  SELECT * FROM sgeocity
    WHERE city IN ( SELECT city FROM +cities )
    INTO TABLE @DATA(result).

With that I will end this post and leave you with this thought – OPEN SQL has evolved and now we have to evolve too. Don’t use FOR ALL ENTRIES every time you want to build a report. Think Aggregates, think WITH, think JOINS and all the enhancements that are available.

Read the next post of this series – ABAP 7.4 And Beyond [8] : Open SQL Enhancements Part 3

Read about more such ABAP expressions and exciting new syntaxes: ABAP Expressions (7.4+)


If you like the content, please subscribe…

Join 2,647 other followers