Why we use Native SQL?
The aim of this lesson is:
- Describe the use of native SQL in context of sap HANA
SQL is a standard language for accessing relational databases. It can be divided into three areas:
- Data Manipulation Language (DML)
- statements for reading and changing data in database tables.
- e.g. SELECT, INSERT, UPDATE, DELETE
- Data Definition Language (DFL)
- statements for creating and administering database tables
- e.g. CREATE, DROP
- Data Control Language (DCL)
- statements for authorization and consistency checks
- e.g. GRANT SELECT ON <..>, REVOKE SELECT
Open SQL covers DML aspects. The ABAP dictionary tools controls DDL aspects. ABAP authorization concept is used to manage DCL aspects.
Open SQL provides a uniform syntax for all database systems supported by SAP.
ABAP programs written in Open SQL will work in any SAP system, regardless of database in use.
Open SQL can only work with database tables created in ABAP dictionary.
Open SQL can also be used via secondary database connections.
Translation of Open SQL to Native SQL
Native SQL is for database level. Open SQL works at application server level.
Restrictions of Open SQL
Open SQL does not support everything possible with standard SQL DML or HANA SQL
Standard SQL DML:
- Fixed value and computed columns
- Sub queries in SELECT or FROM clauses
- UNIONs
HANA SQL:
- CASE construct in HANA SQL
- HANA built in functions like DAYS_BETWEEN()…
- Accessing HANA views
If you need above features, you need to use Native SQL