Register Login

Difference between ABAP Open SQL Statements and SAP HANA

Updated May 18, 2018

ABAP Open SQL Statements vs. HANA

The ABAP Open SQL statements are transferred to the database via the Database Interface (DBI). In many cases, the statement is modified in the DBI before being sent to the database.

Typical adjustments are:

  • If a column with an empty variable is compared in the WHERE section, the DBI omits this condition.
  • When you use FOR ALL ENTRIES, the program splits a value list depending on the DBI parameters described in SAP 1987132 (particularly rsdb/max_blocking_factor, rsdb/max_in_blocking_factor) into statements with short IN lists or OR linkages.
  • If the FOR ALL ENTRIES list is empty, all data is generally selected from the current client ("SELECT ... FROM WHERE MANDT = ?"). All conditions in the WHERE part are ignored.

    Starting with kernel 7.42 a DISTINCT is generated in any of the following situations:

    • Fast data access is used (rsdb/prefer_join_with_fda = 1) and no LOB columns are selected
    • The whole FOR ALL ENTRIES list can be satisfied with a single database selection and no LOB columns are selected
    • Accesses to SAP pool and cluster tables are converted into accesses on the related table pools or table clusters. In many cases these pool and cluster tables are already transparent tables with SAP HANA.
    • If tables are completely or generically buffered on SAP side, the buffers are reloaded, if necessary, with special DBI statements (for example, "SELECT * FROM
    WHERE MANDT = ? ORDER BY " for completely buffered tables) that may be completely different to the statement from the ABAP source code.

    Some operations (for example, kernel procedures, bulk operations, generations) can generate SQL statements although there is no regular SQL statement in the ABAP source code. Also when you use conversion exits, there can be significant differences between ABAP source code and database-side statements (such as additional conditions that are not explicitly present in the ABAP source code).

    IN conditions from the ABAP source code may be converted into any number of possible conditions on the database level, depending on your selection criteria:

    "=", "IN", "LIKE", "BETWEEN", ">", "<", ">=", "<="

    Columns that appear in both the selection list and the WHERE condition are removed from the selection list if it is clear from the WHERE condition what the column's value must be.

    If an expression ends with a space followed by a placeholder, the system generates an OR concatenation as follows:

    SQL statement: ... WHERE LIKE ' %'

    Statement after DBI transformation: ... WHERE ( LIKE ' %' OR LIKE '')


Comments

  • 12 Oct 2017 12:17 pm Guest

    I'm currently trying to work out how exactly OpenSQL code is transformed into native HANA SQL. This article did a great job in getting an idea.

    Where did you get your information from? Is there a complete list of transformations or a SAP Note explaining it further?

    Best
    Patrick


×