Please enable JavaScript.
Coggle requires JavaScript to display documents.
HA400-3 - Code-to-Data with SAP HANA as Secondary Database - Coggle Diagram
HA400-3 - Code-to-Data with SAP HANA as Secondary Database
Classical Open SQL
Code-to-Data Paradigm
Uniform syntax and semantics for all database systems supported by SAP
ABAP programs that only use Open SQL will work in any ABAP system, regardless of the database system in use
Open SQL only works with databse tables that are defined in the ABAP Dictionary
Using secondary database connections possible
Structured Query Language (SQL)
Data Manipulation Language (DML)
Statements for reading and changing data in database (SELECT, INSERT, UPDATE, DELETE)
Reflected in ABAP in Open SQL
Data Definition Language (DDL)
Statements for creating and administering database tables
Reflected in ABAP in ABAP Dictionary and Core Data Services
Data Control Language (DCL)
Statements for authorization and consistency checks
Not reflected in ABAP
Open SQL in ABAP < 7.4 SP05 does not support everything possible
Standard SQL DML
Fixed-value and computed columns
Sub-queries in the SELECT or FROM
UNIONs
CASE expression
Additional Join types
HANA SQL
Using HANA built-in functions (DAYS_BETWEEN( )...)
Accessing HANA Views
Use native SQL
ABAP Database Connectivity (ADBC)
ABAP API for Native SQL calls (recommended instead of older EXEC SQL statement)
Main classes
CL_SQL_CONNECTION
CL_SQL_STATEMENT
CL_SQL_RESULT_SET
Native SQL syntax
Loosely integrated into ABAP
Syntax of native SQL is not checked. Statements are directly sent to the database system
No automatic client handling, no table buffering
All tables, in all schemas, can be accessed
Allows access to all functions in the SQL interface of the database system
Common ground between native HANA SQL and Open SQL
Key words are case insentitive
Table & column names seem case-insensitive. But beware! HANA converts them to upper case unless quoted
Important syntax differences between native HANA SQL and Open SQL
Column lists are comma separated
Table and column name qualifiers are separated using "."
No FOR ALL ENTRIES IN or INTO CORRESPONDING FIELDS OF - these are only known to ABAP / Open SQL
Test native SQL statement in the SAP HANA Studio with SQL Console
SAP HANA Views
Attribute Views
Used to join tables (text tables to master data tables)
Used as a projection (subset of fields)
May contain filters
May have columns with simple calculations
May be used in analytic or calculation views
Analytic Views
Used to join tables
Joins fact tables (data foundation) with attribute views
Data is read from the joined database tables
Joins and calculated measures are evaluated at run time
May contain more sophisticated calculations (currency conversion)
May have input parameters
Calculation Views
Can be created graphically or based on a script (SQL Script, CE Functions)
Is designed for sophisticated analysis
Functions defined in the HANA-specific language 'SQL Script'
Functions that contain SQL commands
SELECT <FIELDS> FROM <TABLE, VIEW, COLUMN VIEW>
SQL in function must be 'read only' (no insert, update, delete, drop...)
Functions that call other functions
Modularize the logic within the calculation view
HANA offers pre-defined functions, e.g. for creating a join or union of tables
May have input parameters
Consuming SAP HANA Views using Native SQL
SELECT <FIELDS> FROM <SCHEMA.PACKAGE/VIEW> (PARAMETERS) WHERE (CLAUSE)
Working with Database Procedures (or Stored Procedures) in SAP HANA Studio
Based on the SAP HANA-specific language SQL script
Input parameters
Output parameters
Read and / or write access to the database
Calling SAP HANA Procedures in ABAP