ABAP 7.4 and beyond [12] : ABAP SQL Aggregate Queries and For All Entries

In this post you will learn a way to use aggregate functions in For All Entries scenarios.

SAP ABAP Developers have been using For All Entries for ages. Aggregate queries are somehow not preferred by all, but with HANA these are a must.

Sample Aggregate Query

Below query fetches summation of seats occupied on flights at summarization level carrier_id, connection_id.

SELECT carrier_id,
       connection_id,
       SUM( seats_occupied ) AS travellers
  FROM /dmo/flight
  GROUP BY carrier_id, connection_id
  ORDER BY carrier_id, connection_id
  INTO TABLE @DATA(lt_travels_by_connection).

The output of this on console will look like below.

Issues with Aggregate Queries

1. FOR ALL ENTRIES can not be used with aggregates.
2. You can not see the data that contributes to the aggregate and the aggregate together

In this post we will talk about how to handle the FOR ALL ENTRIES scenario.

Let us understand the issue first. What happens when you try to add both i.e. Aggregates and Form All Entries together in the query. You get a syntax error.

Solution: Use JOIN.

No, I am not talking about joining all tables together in select query. I am talking about a join on a internal table and database table.

Here is how –

Internal table will be addressed with @itab_name as we do while using it at any other position in SQL. Providing ALIAS to internal table is mandatory. Text for the code is given below for you to try out.

SELECT * FROM /dmo/carrier
    WHERE carrier_id IN @lr_carriers
    INTO TABLE @DATA(lt_carrier).
IF sy-subrc EQ 0.
  SELECT dtab~carrier_id,
         connection_id,
         SUM( seats_occupied ) AS travellers
    FROM /dmo/flight AS dtab
         INNER JOIN @lt_carrier AS itab
         ON dtab~carrier_id = itab~carrier_id
    GROUP BY dtab~carrier_id, connection_id
    ORDER BY dtab~carrier_id, connection_id
    INTO TABLE @DATA(lt_travels_by_connection).
ENDIF.

So this way we can achieve the purpose of FOR ALL ENTRIES i.e. fetching data from database table based on internal table and also use aggregate queries.

Solution to 2nd issue is WINDOWs within SQL which is covered in post ABAP 7.4 and beyond [13] : SQL Window Expression.

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

---

If you like the content, please subscribe…