TAW10 - ABAP OPEN SQL

WHERE CONDITION: POSSIBLE OPTIONS

This slide depicts a table listing operators that Open SQL accepts in WHERE conditions.

OPERATORS IN WHERE CONDITIONS

You select the country, using the list of values in the WHERE condition. An AND operator is used to determine the range for discount as well as the postal code with five places and number 5 in position 4.
The selection of name, which contains a substring ra in any position, is also mentioned with the AND operator.
The AND operators are evaluated before the OR operators when the conditions are linked.
The “_” and “%” placeholders correspond to the SQL standard in delimitation with LIKE.


COMPARING TABLE FIELDS

You can compare two database fields with each other by mentioning the fields from the same database tables after the comparison operators (=, EQ, <>, NE, <, LT, and so on), in the WHERE conditions of the Open SQL statements.
Alternatively, you can also use an alias for the table name in the FROM clause with the AS addition after the table name.

AGGREGATE FUNCTIONS

An aggregate determines values from multiple lines in a column of a database table and the calculation is performed in the database system. An aggregate expression uses an aggregate function to specify a column in the SELECT statement.
The data object in the INTO clause must provide a structure component or table column with the suitable type (see table) for each aggregate expression after SELECT.

SELECT STATEMENT WITH AGGREGATE EXPRESSION

The result of the field list in a SELECT statement, which contains only aggregate expressions, is a single data set. Just as in the SELECT SINGLE statement, you can specify a structure as the target.


THE GROUP BY ADDITION

A group contains all the records that have the same contents in the columns, which are specified after GROUP BY.
With the GROUP BY addition, the database first sorts the records into groups, instead of applying the aggregate functions to all the records found. The aggregate functions are evaluated separately for each group. Each group is represented as one line in the selection result.



THE HAVING ADDITION

While using GROUP BY, you can also specify a logical expression after the HAVING addition, to restrict the result set further. The logical expression after HAVING can contain aggregate functions, as well as the fields that are specified after GROUP BY.



THE ORDER BY ADDITION - VARIANTS

In Open SQL, you can use the ORDER BY addition for the SELECT statement to sort the data by specific criteria directly.
To sort the table by its complete primary key in ascending order, you use the ORDER BY PRIMARY KEY, subject to certain conditions:
  The FROM clause must only contain a single database table, with no views or joins.

  The field list after SELECT must contain all the key fields of the table, including the client.

You can sort the data by any existing columns by using the syntax ORDER BY col1 col2... The priority of sorting is determined by the sequence of the specified column.


THE ORDER BY ADDITION

You can define the direction of sorting by using the optional DESCENDING and ASCENDING additions after a given field. The result set is sorted in ascending order by default.


NESTED SELECTS

Nested SELECT statements are used to access the secondary tables in a loop over the records from the primary table.
This results in a high load on the database and the network.
This is due to at least two reasons:
  Many partially filled data packages are transferred.

  Identical accesses can take place for the same data several times.

EXAMPLE: INNER/OUTER JOIN

When using an INNER JOIN, the result set consists of records from the outer table, for which suitable data records exist in the inner table.
A LEFT OUTER JOIN results in a set that contains all the records from the outer table, regardless of whether or not suitable records exist in the inner table.
The fields of the inner table are set to ZERO values in the result set, if no suitable records exist in the inner table.

EXAMPLE: ABAP INNER JOIN

This slide depicts the syntax you can use while implementing an INNER JOIN. This enables you to read data, distributed across multiple functionally dependent tables.


READ ON DEMAND AND BUFFERING

This example shows how matching data from STRAVELAG (travel agency master data) within a loop for the bookings from table SBOOK can be read. You should encapsulate the reading of STRAVELAG records in a read routine, as using a SELECT SINGLE will lead to identical SQL statements. The table contents read from STRAVELAG are buffered in a static internal table. Before each database access, the system checks whether the corresponding table entry is already read.


SELECT WITH FOR ALL ENTRIES ADDITION

SELECT … FOR ALL ENTRIES is for additional data from the database, to augment the already available data in an internal table.
The content of the internal table is used as a restriction for the database access. The database interface takes a certain number of entries and sends one native SQL for each group. The results of the individual native SQL statements are combined to form the result of the Open SQL statement. The duplicate entries are removed automatically.