Group By

Group By clause categorizes and summarizes the lines of database table based on a single field. That single field is mentioned in the group by clause. We can use multiple fields in the group by clause also. Here the database table records will be summarized according to those fields.

The fields mentioned in the group by clause cannot be specified for the aggregate function. We have to use other fields which are not specified in group by clause to calculate the aggregate function. Now we have the a PO with the following quantity.

REPORT  zabap_gui.

DATA:
      ebeln  TYPE ekpo-ebeln,
      po_max TYPE p DECIMALS 2,
      po_min TYPE p DECIMALS 2,
      tq_max TYPE p DECIMALS 2,
      tq_min TYPE p DECIMALS 2.

WRITE:/    'PO No',
        15 'Max PO Quantity',
        35 'Min PO Quantity',
        55 'Max Target',
        70 'Min Target'.

SELECT ebeln
       MAX( menge ) MIN( menge )
       MAX( ktmng ) MIN( ktmng )
  FROM ekpo
  INTO (ebeln,
        po_max, po_min,
        tq_max, tq_min)
  GROUP BY ebeln.

  WRITE:/   ebeln,
         15 po_max,
         35 po_min,
         55 tq_max,
         70 tq_min.

ENDSELECT.

Here is the output:

Select Dynamic Column

We can select the columns dynamically in a select statement. The syntax is like this:

SELECT (local_internal_table)
  FROM database_table INTO TABLE internal_table.

Here the local internal table contains the field names dynamically. This table also has a line type which holds the data of field names like this.

DATA: line TYPE char100,
      itab TYPE TABLE OF line.

line = 'ebeln ebelp matnr werks lgort'.
APPEND line TO itab.

Now after appending the text to the itab it can be used dynamically in select statement. Here the WHERE clause is optional. If we don’t use it then the total rows/records of the fields will have been fetched by the system.

  

REPORT  zabap_gui.

TABLES: ekpo.

* Creating a custom structure of Item Table
TYPES:
      BEGIN OF ty_ekpo,
        ebeln TYPE ekpo-ebeln,
        ebelp TYPE ekpo-ebelp,
        matnr TYPE ekpo-matnr,
        werks TYPE ekpo-werks,
        lgort TYPE ekpo-lgort,
      END OF ty_ekpo.

* Creating a line type of predefined structure
DATA:
      wa_ekpo TYPE ty_ekpo,
      it_ekpo TYPE STANDARD TABLE OF ty_ekpo,

* Creating a line type and internal table
* to use as dynamic columns specification
      line TYPE char100,
      itab TYPE TABLE OF line.

line = 'ebeln ebelp matnr werks lgort'.
APPEND line TO itab.

SELECT (itab)
  FROM ekpo INTO TABLE it_ekpo
  WHERE ebeln = '3000000232'.

WRITE:/    'PO No.',
        15 'Item No',
        28 'Material',
        48 'Plant',
        55 'Storage'.
ULINE.
SKIP.

LOOP AT it_ekpo INTO wa_ekpo.
  WRITE:/    wa_ekpo-ebeln,
          15 wa_ekpo-ebelp,
          28 wa_ekpo-matnr,
          48 wa_ekpo-werks,
          55 wa_ekpo-lgort.
ENDLOOP.

Here is the output.

Bypassing Buffer in Select

One of an important feature of open SQL is that it fetches the data records from the buffer of SAP system. Now fetching records directly from database may take time. Hence performance will go down. That’s why SQL fetches data from buffer.

Now if the database table changes frequently (table like transaction table) then it will be a problem to select updated data which will not be present in buffer. To avoid this problem SAP system has introduced the BYPASSING BUFFER clause in the select statement after from clause. This statement ensures that the records are updated data records fetched from the database.

REPORT  zabap_gui.

TABLES: kna1.

* Local structure for local internal table
* and work area
TYPES:
       BEGIN OF ty_kna1,
         kunnr TYPE kna1-kunnr,
         land1 TYPE kna1-land1,
         name1 TYPE kna1-name1,
         ort01 TYPE kna1-ort01,
         pstlz TYPE kna1-pstlz,
         regio TYPE kna1-regio,
       END OF ty_kna1.

* Local internal table & work area
DATA:
      it_kna1 TYPE TABLE OF ty_kna1,
      wa_kna1 TYPE ty_kna1.

* Selection range by select option internal table
SELECT-OPTIONS: s_kunnr FOR kna1-kunnr.

START-OF-SELECTION.

* Selection of the specific fields
  SELECT kunnr land1 name1 ort01 pstlz regio
    INTO TABLE it_kna1 FROM kna1
    BYPASSING BUFFER "it ensures that the system fetches
                     "data directly from the database
                     "not from the buffer
    WHERE kunnr IN s_kunnr.

  IF sy-subrc = 0.
    WRITE:
           /5 'Customer No',
           14 'Country',
           24 'Name',
           60 'City',
          100 'Postal',
          112 'Region'.
    ULINE.
    SKIP.

    LOOP AT it_kna1 INTO wa_kna1.
      WRITE:
              /5 wa_kna1-kunnr,
              14 wa_kna1-land1,
              24 wa_kna1-name1,
              60 wa_kna1-ort01,
             100 wa_kna1-pstlz,
             112 wa_kna1-regio.
    ENDLOOP.
  ENDIF.

Here is the output.

Client Specified Select

Client specified clause switches off the automatic client handling by open SQL. If we select the MANDT (client) field then we have to use client specified clause like follows:

  SELECT mandt field1 field2 ... fieldn
    INTO TABLE internal_table FROM database_table
    CLIENT SPECIFIED  "MANDT has been selected
                      "hence client specified is must
    WHERE mandt = '800'
      AND field1 IN select_option.

This statement is always mentioned after the FROM clause. If the addition CLIENT SPECIFIED is specified, but the client ID in the WHERE condition is not mentioned, the SELECT statement circumvents the SAP buffering.

REPORT  zabap_gui.

TABLES: kna1.

* Local structure for local internal table
* and work area
TYPES:
       BEGIN OF ty_kna1,
         mandt TYPE kna1-mandt,
         kunnr TYPE kna1-kunnr,
         land1 TYPE kna1-land1,
         name1 TYPE kna1-name1,
         ort01 TYPE kna1-ort01,
         pstlz TYPE kna1-pstlz,
         regio TYPE kna1-regio,
       END OF ty_kna1.

* Local internal table & work area
DATA:
      it_kna1 TYPE TABLE OF ty_kna1,
      wa_kna1 TYPE ty_kna1.

* Selection range by select option internal table
SELECT-OPTIONS: s_kunnr FOR kna1-kunnr.

START-OF-SELECTION.
* Selection of the specific fields
  SELECT mandt kunnr land1 name1 ort01 pstlz regio
    INTO TABLE it_kna1 FROM kna1
    CLIENT SPECIFIED  "MANDT has been selected
                      "hence client specified is must
    WHERE mandt = '800'
      AND kunnr IN s_kunnr.

  IF sy-subrc = 0.
    WRITE:/   'Clnt',
            5 'Customer No',
           14 'Country',
           24 'Name',
           60 'City',
          100 'Postal',
          112 'Region'.
    ULINE.
    SKIP.

    LOOP AT it_kna1 INTO wa_kna1.
      WRITE:/    wa_kna1-mandt,
               5 wa_kna1-kunnr,
              14 wa_kna1-land1,
              24 wa_kna1-name1,
              60 wa_kna1-ort01,
             100 wa_kna1-pstlz,
             112 wa_kna1-regio.
    ENDLOOP.
  ENDIF.

Here is the output.

Average Sum Maximum Minimum by Select

We can calculate the average and sum of any quantity type field directly from select statement. Similarly we can extract the maximum value or minimum value from quantity type field. In the below example the system fetches the data of MENGE field from EKPO table and calculate its average and sum and then put into the packed type variable ‘average’ & ‘sum’ respectively. Similarly it fetches the maximum and minimum values from MENGE and put it packed type variable ‘maximum’ & ‘minimum’. Here the WHERE clause is optional. To avoid the entire field records we have used this clause.

In the database we find this MENGE field with PO number '3000000057'.

REPORT  zabap_gui.

DATA:
      average TYPE p DECIMALS 2,
      sum     TYPE p DECIMALS 2,
      maximum TYPE p DECIMALS 2,
      minimum TYPE p DECIMALS 2.

* Here the MENGE field of EKPO table has been used
* to calculate the average, sum, maximum & minimum
SELECT AVG( menge )
       SUM( menge )
       MAX( menge )
       MIN( menge )
  FROM ekpo
  INTO (average, sum, maximum, minimum)
  WHERE ebeln = '3000000057'.

WRITE: / 'Average = ', average,
       / 'Sum     = ', sum,
       / 'Maximum = ', maximum,
       / 'Minimum = ', minimum.

Here is the output.