Native SQL syntax

After completing this lesson, you will be able to:

• Understand difference between syntax of Open SQL and Native SQL
• write correct Native SQL statements

Native SQL in a nutshell:

• Loosely integrated into ABAP, but allows access to all functions contained in the programming interface of the respective database system
• Syntax of native SQL is not checked, statements are directly sent to database system
• All tables in all schemas can be accessed using native SQL
• There is no automatic client handling in native SQL, no table buffering also.

Common points between native HANA SQL and Open SQL:

• key words are case sensitive
• table and column names  – HANA converts them to upper case unless in double quotes

Important syntax differences:

• Column lists are comma separated
• Table and column name qualifiers are separated using “.”
• There is no “FOR ALL ENTRIES IN” or “INTO CORREPONDING FIELDS OF” – these are only known in Open SQL

Example 1

in Open SQL:

SELECT carrid connid cityfrom cityto

FROM spfli

INTO …..

WHERE carrid = ‘LH’

AND connid = ‘0400’

ORDER BY carrid.

Native SQL:

SELECT carrid, connid, cityfrom, cityto       -> comma separated field list

FROM sflight.spfli                                 -> schema has to be specified (if schema is other than default 

INTO ….                                                          schema of user)

WHERE carrid = ‘LH’

AND connid = ‘0400’

AND mandt = ‘800’                            -> no automatic client handling, client is one of the key fields

ORDER BY mandt, carrid

Note: If ADBC is used, there will not be any INTO clause

Example 2

In Open SQL:

SELECT b~carrid a~carrname b~connid b~cityfrom b~cityto

FROM scarr AS a INNER JOIN spfli AS b

ON a~carrid =b~carrid

INTO ….

WHERE b~carrid = ‘LH’

AND b~connid = ‘0400’

ORDER BY b~carrid.

Native SQL:

SELECT b.carrid, b.connid, a.carrname, b.cityfrom, b.cityto

FROM sflight.scarr AS a INNER JOIN sflight.spfli AS b

ON a.mandt = b.mandt

AND a.carrid = b.carrid

INTO…..

WHERE b.carrid = ‘LH’

AND b.connid = ‘0400’

AND b.mandt = ‘800’

ORDER BY mandt, carrid