6.4Open SQL Enhancements

In Chapter 5, you were already introduced to Open SQL enhancements that enable you to use ABAP CDS views in Open SQL statements. As of AS ABAP 7.4 SP5, there are many more extensions you can use to query and process your persistent data. With these extensions, Open SQL further approaches the full functional scope of the SQL 92 standard. This section provides a brief overview of the new Open SQL features.

One of the most significant extensions in Open SQL is the support of SQL expressions in the SELECT list and in the WHERE, GROUP BY, and HAVING clauses of SELECT statements. The operands of the expression can generally be fields of the data sources used, ABAP variables (host variables), or literals. In addition, new operators and SQL functions are supported, including the following:

• Arithmetic operators: +, -, *, /
  The / operator is used for float-based divisions and isn’t permitted in integer expressions. Overflows of integer expressions or floating point number expressions result in the CX_SY_OPEN_SQL_DB exception.

• String operator: &&
  The && operator is used in string expressions and concatenates two character-type operands. As of AS ABAP 7.5, the following string functions are also available: CONCAT, LPAD, LENGTH, LTRIM, REPLACE, RTRIM, RIGHT, and SUBSTRING.

• Unary negation operator: -
  The unary negation operator generates the negative value of a numeric operand.

• Arithmetic functions: ABS( expr ), CEIL( expr ), FLOOR( expr ), DIV( expr1, expr2 ), MOD( expr1, expr2 )
  The DIV function is used for integer-based divisions of the numeric expressions expr1 and expr2. If the operand of an arithmetic function has the NULL value, the function returns NULL.

• Additional functions: CAST( expr AS fltp ) and COALESCE( expr1, expr2 )
  In AS ABAP 7.4, operands in the CAST function can only be converted to FLTP. As of AS ABAP 7.5, additional DDIC types can be used.

• Case distinctions: simple and searched CASE statements
  Simple CASE statements behave like SWITCH statements, whereas searched CASE statements behave like IF ... ELSE ... IF statements.

Compared to ABAP CDS, SQL expressions can also be used as operands in the MAX, MIN, SUM, and COUNT( DISTINCT ) aggregation functions.

The definition of joins in SELECT statements also includes numerous extensions, for example:

• You can use RIGHT OUTER JOIN to link data sources.

• You can now use LIKE and IN operators in ON conditions.

• You can link individual comparisons in ON conditions with OR and negate them with NOT.

• You can also use literals as operands of an ON condition. By defining ON conditions, which always have the true truth value, for example, ON 1 = 1, you can also implement cross joins. Here, the involved data sources are linked to each other as a Cartesian product.

To facilitate the implementation of queries in SELECT Open SQL statements, you can now also use ABAP inline declarations for the target area of SELECT statements. For this purpose, use a declaration expression with the DATA() declaration operator in the INTO clause of the SELECT statement (see Listing 6.16). The abbreviated <data_source>~* form enables you to add all fields of a data source to the SELECT list.

If you use one of the previously mentioned extensions in Open SQL, the syntax of the Open SQL statement is automatically checked in a strict mode. This includes stricter checks to ensure the accuracy of the statement. In the strict mode, previous syntax warnings are now syntax errors. It also includes new checks; for example, fields that are included in the HAVING clause of a SELECT statement must be listed in the GROUP BY clause. Furthermore, you must adhere to the following rules in the strict mode:

• Field lists, for example, in the SELECT and GROUP BY list, must be separated by commas.

• ABAP variables (host variables) that are used in Open SQL statements must precede the @ escape character.

The strict mode is not only available for SELECT statements but also for all Open SQL statements. However, it only applies to the currently used statement and not automatically to all Open SQL statements of the compilation unit. If you want to benefit from the strict checks in your existing Open SQL statements, you can enable the strict mode by implementing minor changes to your statements. To do so, you only have to meet one of the rules just mentioned, for example, by adding the @ escape character in front of the used host variables.

[»]Strict Open SQL Mode

We recommend always activating the strict Open SQL mode. You can use the strict mode in Unicode programs in which the fixed point arithmetic program attribute is activated.

To demonstrate the usage of the new features, we check the total baggage weight of the individual flight classes of a flight through an Open SQL query (see Listing 6.16). The result includes all classes that exceed the maximum baggage weight. The flight that is to be checked and the upper limit for the baggage weight of a passenger of a class are specified in the lv_flight and lv_max_luggweight (economy class), lv_max_luggweight_b (business class), and lv_max_luggweight_f (first class) host variables. To make it easier to read the results, we transform the internal IDs of the flight classes into user-friendly constants using a CASE statement.

SELECT
flight~carrid,
flight~connid,
flight~fldate,
CASE booking~class
WHEN 'Y' THEN 'ECONOMY'
WHEN 'C' THEN 'BUSINESS'
ELSE 'FIRST'
END AS class,
SUM( booking~luggweight ) AS luggage_weight
FROM sflight AS flight
RIGHT OUTER JOIN sbook AS booking
ON flight~carrid = @lv_flight-carrid
AND flight~connid = @lv_flight-connid
AND flight~fldate = @lv_flight-fldate
AND booking~class IN ( 'C', 'Y', 'F' )
INTO TABLE @DATA(results)
WHERE booking~cancelled <> 'X'
GROUP BY
flight~carrid,
flight~connid,
flight~fldate,
booking~class
HAVING
( booking~class = 'Y' AND
SUM( booking~luggweight ) >
MAX( flight~seatsmax * @lv_max_luggweight ) )
OR ( booking~class = 'C' AND
SUM( booking~luggweight ) >
MAX( flight~seatsmax_b * @lv_max_luggweight_b ) )
OR ( booking~class = 'F' AND
SUM( booking~luggweight ) >
MAX( flight~seatsmax_f * @lv_max_luggweight_f ) ).

Listing 6.16New Open SQL Features

These Open SQL extensions provide you with numerous new options for accessing and manipulating your persistent data. In particular, the support of SQL expressions enables you to optimally leverage the strengths of your database for data processing.