1 of 130

ABAP on HANA Workshop �

SAP Active Global Support

INTERNAL

  • 2011 SAP AG. All rights reserved.

1

RKT

2 of 130

ABAP Database Connectivity - ADBC

  • 2011 SAP AG. All rights reserved.

2

RKT

3 of 130

�Learning Objectives: ADBC

After completing this unit you will be able to:

  • Use ADBC to access database tables
  • 2011 SAP AG. All rights reserved.

3

RKT

4 of 130

Introduction of the ADBC

  • ADBC is an API (application programming interface) for the Native SQL interface of the AS ABAP that is based on ABAP Objects.
  • The methods of ADBC make it possible to :
      • Send database specific SQL commands to a database system and process the result.
      • Establish secondary database connections.
      • Access the views and tables in any database (including HANA) directly.
  • 2011 SAP AG. All rights reserved.

4

RKT

5 of 130

Why do we need ADBC in 7.40?

  • In SAP_BASIS 7.40 (Suite on HANA), all the views and procedures created in HANA can be exposed to ABAP DDIC and used directly (using OPEN SQL) in the ABAP code.
  • In case of side-by-side scenario (HANA accelerators), ADBC is used to access views, procedures and tables on HANA.
  • In Suite on HANA, we may still use ADBC to access analytical views with currency conversion functions* or generating DB procedure with an ABAP code.

Note – More details in DEMO.

  • 2011 SAP AG. All rights reserved.

5

RKT

6 of 130

Difference to Open SQL and ADBC ( Native SQL )

Advantage of Open SQL:

  • Simple and High Readability,
  • Syntax Check on SQL Statements,
  • Easily Performance Trace,
  • Database Independent.

Advantage of ADBC (Native SQL) :

  • Specific SQL Statements for HANA can be executed
  • Ability to Access All the Views and Tables in HANA Directly,
  • 2011 SAP AG. All rights reserved.

6

RKT

7 of 130

Classes in ADBC

The following four ADBC classes are the most important classes.

  • CL_SQL_STATEMENT – Execution of SQL Statements
  • CL_SQL_RESULT_SET – Result of the Execution
  • CL_SQL_CONNECTION – Administration of Database Connections
  • CL_SQL_EXCEPTION – Exception Handling

​

​

​

​

  • Note - We would take up first 2 classes in the Course. For the details on the others, please refer to appendix or http://help.sap.com/abapdocu_702/de/abenadbc.htm
  • 2011 SAP AG. All rights reserved.

7

RKT

8 of 130

CL_SQL_STATEMENT

  • CL_SQL_STATEMENT is used to set and execute SQL statements.
  • The instances of CL_SQL_STATEMENT can be created by ‘CREATE OBJECT’ statement.
  • The default database where the SQL statements execute is the central database of the AS ABAP. The methods to execute SQL statements on another (secondary) databases* are out of scope for this training.

​

​

​

​

*Refer to http://help.sap.com/abapdocu_702/de/abencl_sql_connection.htm

  • 2011 SAP AG. All rights reserved.

8

RKT

9 of 130

CL_SQL_STATEMENT

  • There are three methods to execute SQL statements. These methods have an obligatory input parameter STATEMENT of type string, to which syntactically correct SQL statement must be passed.
  • EXECUTE_QUERY – For Queries (SELECT statements)
  • EXECUTE_DDL – For DDL (CREATE, DROP, or ALTER)
  • EXECUTE_UPDATE – For DML (INSERT, UPDATE, or DELETE)
  • 2011 SAP AG. All rights reserved.

9

RKT

10 of 130

CL_SQL_STATEMENT

  • EXECUTE_QUERY – This method is used to execute queries (SELECT statements). It returns an instance of CL_SQL_RESULT_SET as the result of the query.

​

  • EXECUTE_DDL - This method is used to execute Data Definition Language statements such as CREATE, DROP, or ALTER. It doesn’t have a RETURNING parameter.

​

  • EXECUTE_UPDATE – This method is used to execute Data Manipulation Language ( DML ) statements such as INSERT, UPDATE, or DELETE and it returns the number of table rows processed in ROWS_PROCESSED.

​

  • 2011 SAP AG. All rights reserved.

10

RKT

11 of 130

Example Code�DDL & DML & Query Commands

DATA: lo_sql TYPE REF TO cl_sql_statement .

CREATE OBJECT lo_sql .

* Execute DDL statement

lo_sql->execute_ddl(

`CREATE TABLE dbtable` &&

`(val1 char(10) NOT NULL,` &&

` val2 char(10) NOT NULL,` &&

` PRIMARY KEY (val1) )`).

* Execute DML statement

lo_sql->execute_update(

`INSERT INTO dbtable VALUES('10', '10')`).

* Execute Query ( Describe in detail in following slides )

lo_result = lo_sql->execute_query(

'SELECT val1, val2 FROM dbtable' ).

​

  • 2011 SAP AG. All rights reserved.

11

RKT

12 of 130

CL_SQL_STATEMENT

  • There is another method SET_TABLE_NAME_FOR_TRACE in the CL_SQL_STATEMENT class. It can pass the views’ name to the SQL trace tools, such as SM50, STAD and ST05.
  • You should execute this method just before that you want to execute SQL statement. Then it will be directly shown in the trace result list and tell others which views are being operated now.
  • The method increases understandability by setting names where appropriate. This will ease troubleshooting and performance analysis. Please invoke this method every time you execute the SQL statement via ADBC.
  • 2011 SAP AG. All rights reserved.

12

RKT

13 of 130

Example Code�SET_TABLE_NAME_FOR_TRACE

DATA: lo_sql  TYPE REF TO cl_sql_statement,

lo_result TYPE REF TO cl_sql_result_set,� dref      TYPE REF TO data,�      val1 TYPE snwd_bpa-bp_id, val2 TYPE snwd_bpa-company_name,�      val3 type snwd_so_i-gross_amount, tname TYPE tabname.�CREATE OBJECT lo_sql .�tname = 'AN_OPEN_ITEMS'.

lo_sql->set_table_name_for_trace( tname ).�lo_result = lo_sql->execute_query( 

'SELECT bp_id, company_name, 

SUM( gross_amount_converted ) AS sum_amount 

FROM "_SYS_BIC"."test.oia.300.316/AN_OPEN_ITEMS" 

GROUP BY bp_id, company_name ').�GET REFERENCE OF val1 INTO dref. lo_result->set_param( dref ).�GET REFERENCE OF val2 INTO dref. lo_result->set_param( dref ).�GET REFERENCE OF val3 INTO dref. lo_result->set_param( dref ).�lo_result->next( ). lo_result->close( ).�WRITE: / val1, val2, val3.

  • 2011 SAP AG. All rights reserved.

13

RKT

14 of 130

Example Code�SET_TABLE_NAME_FOR_TRACE

  • SM50:

​

​

  • ST05:
  • 2011 SAP AG. All rights reserved.

14

RKT

15 of 130

Example Code�SET_TABLE_NAME_FOR_TRACE

  • STAD:

​

​

  • 2011 SAP AG. All rights reserved.

15

RKT

16 of 130

CL_SQL_RESULT_SET

  • After the execution of a query, an instance of CL_SQL_RESULT_SET is created and it stores the result data of the query.
  • There are three methods of class CL_SQL_RESULT_SET to assign the result to an object, a structure, or an internal table.

​

  • SET_PARAM , NEXT , and CLOSE - Assign compatible ABAP data objects to the columns of the result set

Like : ' SELECT SINGLE val1 val2 FROM dbtable INTO (wa1, wa2) ‘

​

  • SET_PARAM_STRUCT , NEXT , and CLOSE - Assign compatible ABAP structure to the row of the result set

Like : ' SELECT SINGLE val1 val2 FROM dbtable INTO ls_result '

​

  • 2011 SAP AG. All rights reserved.

16

RKT

17 of 130

CL_SQL_RESULT_SET

  • SET_PARAM_TABLE , NEXT_PACKAGE , and CLOSE - Assign compatibly structured internal table to the rows of the result set

Like : ' SELECT val1 val2 FROM dbtable INTO TABLE lt_result '

​

  • 2011 SAP AG. All rights reserved.

17

RKT

18 of 130

CL_SQL_RESULT_SET

SET_PARAM, NEXT, and CLOSE :

  • These methods are used to assign several compatible ABAP data objects to the columns of the result set from left to right.

lo_result = sql->execute_query('SELECT val1, val2 FROM dbtable').

GET REFERENCE OF wa1 INTO dref. lo_result->set_param( dref ).

GET REFERENCE OF wa2 INTO dref. lo_result->set_param( dref ).

lo_result->next( ).

wa1

wa2

val 1

val 2

XX

XX

XX

XX

……

……

val 0

val 1

val 2

val 3

​

XX

XX

XX

XX

……

XX

XX

XX

XX

……

……

……

……

……

……

SELECT val1, val2

FROM dbtable

Database Table

Result

Set

  • 2011 SAP AG. All rights reserved.

18

RKT

19 of 130

CL_SQL_RESULT_SET

SET_PARAM, NEXT, and CLOSE :

  • With the call of NEXT, one row in the result set of the query will be addressed and the value of the columns in that row will be assigned to those objects which are defined by SET_PARAM.

​

  • The method NEXT will return value 1 if the row can be addressed and 0 if it cannot be.

IF rc > 0.� ...       “Handling the result� ELSE.� WRITE 'No entry found.'.� ENDIF.

​

  • 2011 SAP AG. All rights reserved.

19

RKT

20 of 130

CL_SQL_RESULT_SET

SET_PARAM_STRUCT, NEXT, and CLOSE

  • These methods are used to assign a completely compatible ABAP structure to the rows of the result set.

lo_result = sql->execute_query('SELECT val1, val2 FROM dbtable').

GET REFERENCE OF ls_result INTO dref.

lo_result->set_param_struct( dref ).

lo_result->next( ).

​

wa1

wa2

ls_result

val 1

val 2

XX

XX

XX

XX

……

……

val 0

val 1

val 2

val 3

​

XX

XX

XX

XX

……

XX

XX

XX

XX

……

……

……

……

……

……

SELECT val1, val2

FROM dbtable

Database Table

Result

Set

  • 2011 SAP AG. All rights reserved.

20

RKT

21 of 130

CL_SQL_RESULT_SET

SET_PARAM_STRUCT, NEXT, and CLOSE

  • With the call of NEXT, one row of result of the query will be addressed and the value of that row will be assigned to the structure which is defined by SET_PARAM_STRUCT.

​

  • The method NEXT will return value 1 if the row can be addressed and 0 if it cannot be.

IF rc > 0.� ...       “Handling the result� ELSE.� WRITE 'No entry found.'.� ENDIF.

​

  • 2011 SAP AG. All rights reserved.

21

RKT

22 of 130

CL_SQL_RESULT_SET

SET_PARAM_TABLE, NEXT_PACKAGE, and CLOSE

  • These methods are used to assign a completely compatibly structured internal table to the rows of the result set.

lo_result = sql->execute_query('SELECT val1, val2 FROM dbtable').

GET REFERENCE OF lt_result INTO dref.

lo_result->set_param_table( dref ).

lo_result->next_package( ).

​

val 1

val 2

XX

XX

XX

XX

……

……

val 0

val 1

val 2

val 3

​

XX

XX

XX

XX

……

XX

XX

XX

XX

……

……

……

……

……

……

SELECT val1, val2

FROM dbtable

lt_result

……

……

XX

XX

XX

XX

Database Table

Result

Set

APPEND

  • 2011 SAP AG. All rights reserved.

22

RKT

23 of 130

CL_SQL_RESULT_SET

SET_PARAM_TABLE, NEXT_PACKAGE, and CLOSE

  • At each call of NEXT_PACKAGE, it reads out at most the number of rows that are passed to the input parameter UPTO and append them to the target internal table without deleting the previous contents. If no value is passed to UPTO, all the rows are read out.

​

    • In the method NEXT_PACKAGE, the number of rows read is returned in the return value ROWS_RET.

rc = lo_result->next_package( ). 

  • With all series of methods, reading out is completed using CLOSE.

lo_result->close( ).

​

  • 2011 SAP AG. All rights reserved.

23

RKT

24 of 130

CL_SQL_RESULT_SET

  • If you need to select one entry (row) and assign several values (columns) in it to local variables instead of one structure, you can use SET_PARAM to define those variables as target variables one by one.

​

  • If you only want to get a whole entry (row), you can assign the target structure to the row by SET_PARAM_STRUCT.

​

  • If you want to assign rows of the result to the internal table, you need SET_PARAM_TABLE.
  • 2011 SAP AG. All rights reserved.

24

RKT

25 of 130

DAY 2�Summary: ADBC

You should now be able to:

    • Use ADBC to access database tables

​

  • 2011 SAP AG. All rights reserved.

25

RKT

26 of 130

Demo – Introduction of the ADBC

DEMO:

    • Access database’s table via ADBC in ABAP
  • 2011 SAP AG. All rights reserved.

26

RKT

27 of 130

HANA Specific solutions : Code Pushdown

  • 2011 SAP AG. All rights reserved.

27

RKT

28 of 130

DAY 2 �Learning Objectives: HANA Specific solutions – Code pushdown

After completing this unit you will be able to:

  • Understand the basics of Modeling Views in HANA.
  • Attribute View, Analytical view, Calculation view.
  • Understand and use External views and DB procedure Proxies.
  • Understand and write simple DB procedures
  • Use ABAP to access views and DB procedures in HANA.

​

  • 2011 SAP AG. All rights reserved.

28

RKT

29 of 130

HANA Specific Solutions �

    • HANA specific solutions help us move the application logic to HANA. It is called as ‘code pushdown’ or ‘code to data’.
    • The data transferred between AS and DB is minimized
    • Number of round trips are reduced significantly.
  • 2011 SAP AG. All rights reserved.

29

RKT

30 of 130

Modeling Views in a Nutshell�Modeling with SAP HANA Studio

    • Attribute View

​

​

​

    • Analytic View

​

​

​

    • Calculation View

​

​

  • 2011 SAP AG. All rights reserved.

30

RKT

31 of 130

Modeling Views�

Analytic View

Attribute View

Column Tables

Calculation View

:can use

Legend:

Calculation Engine

Join Engine

OLAP Engine

  • 2011 SAP AG. All rights reserved.

31

RKT

32 of 130

Decide on where to Build Content?

Analyze Query

Logic

Information in existing tables

Only Joins & calculated expressions

no

Access Base Tables

yes

Use Attribute Views

Star Schema or Aggregation

no

yes

Use Analytic Views

yes

Calculation View

no

Use Graphical or Scripted Calc Views

yes

Use DB Procedures with SQLScript

no

  • 2011 SAP AG. All rights reserved.

32

RKT

33 of 130

Attribute View�

  • 2011 SAP AG. All rights reserved.

33

RKT

34 of 130

Modeling Views �Attribute View

    • Attribute View:
      • It is mostly processed in JOIN Engine
      • Master data modeling: used to join master data tables e.g. “Plant/Location” ⬄ “Material”, “Business Partner” ⬄ “Business Partner Address”
      • can be used to join two or more tables and also have calculated columns
  • 2011 SAP AG. All rights reserved.

34

RKT

35 of 130

Creating Attribute Views

    • Right-click your Package in HANA Studio and select New 🡪 Attribute View.
    • Enter name, description, select the View Type 🡪 Attribute View -> press Finish.
  • 2011 SAP AG. All rights reserved.

35

RKT

36 of 130

Creating Attribute Views �Select tables and attributes

    • Now drag & drop master data table(s) into the Data Foundation section.
    • Then at the Data Foundation section, right-click on the attributes -> Add to Output.
  • 2011 SAP AG. All rights reserved.

36

RKT

37 of 130

Creating Attribute Views �Key Attribute

    • At the Semantics section, define your Key Attribute(s).

​

​

​

​

      • The key attribute is synonymous to the primary keys of a table but it is not checked for uniqueness.

​

  • 2011 SAP AG. All rights reserved.

37

RKT

38 of 130

Creating Attribute Views �Join tables

    • If more than 1 table was chosen, define the joins in the Data Foundation section.

​

​

​

​

​

    • Then Save, validate and activate.
      • A runtime object will be created in the Catalog Schema _SYS_BIC.

​

  • 2011 SAP AG. All rights reserved.

38

RKT

39 of 130

Creating Attribute Views �Data Preview

    • After creating an Attribute View you can preview it’s data in table or graphical form.
  • 2011 SAP AG. All rights reserved.

39

RKT

40 of 130

Calculated Columns�

  • 2011 SAP AG. All rights reserved.

40

RKT

41 of 130

Calculated Columns

  • Calculated Columns can be created for Attribute Views, Analytic Views, Calculation Views and is calculated on-the-fly.
  • Calculated Column = Calculated Attribute or Calculated Measure
      • For a Calculated Column, on one or more existing attributes, measures, other Calculated Columns or constants can be used.
      • Calculations: string operations, If / Case / In / isnull operations, mathematical functions, date functions, conversion functions.
  • 2011 SAP AG. All rights reserved.

41

RKT

42 of 130

Calculated Columns�Risks

  • Calculated Columns are intermediate results that need to be written into buffer and take additional time.
      • Calculated Columns can impact the performance, especially when complex calculations need to be done.

​

  • When a Calculated Column is defined for an Analytic View :
      • the Analytic View is processed as Calculation View (🡪Calc Engine), not anymore as OLAP View (🡪 OLAP engine), see Explain Plan 🡪 Performance impact.

​

  • 2011 SAP AG. All rights reserved.

42

RKT

43 of 130

Calculated Columns�Risks

  • Avoid Calculated Columns in the Where-clause of the SQL statement reading data from the Attribute / Analytic / Calculation View
      • the Where-clause is not pushed down to the bottom view/table
      • Risk of high amount of data being processed 🡪 performance impact

​

  • Avoid Filters on Calculated Columns
      • The calculation first needs to be done for all selected records before the data is filtered.

​

      • Workaround:

Instead of a Calculated Column, it might be better (depending on the scenario) to add a new fix table column and do the calculation during the time of inserting a new data set into the table, e.g. via SLT.

Or do the calculation into a fix column via procedures scheduled periodically.

​

  • 2011 SAP AG. All rights reserved.

43

RKT

44 of 130

External Views

  • 2011 SAP AG. All rights reserved.

44

RKT

45 of 130

External Views

  • External views are needed to access the HANA views/models using OPEN SQL.
  • External views are the ABAP repository object corresponding to a HANA model/view.
  • External views are stored and managed in the ABAP Dictionary - similar to the regular Dictionary views .
  • The external view only represents the HANA view. HANA view is the leading object i.e. changes made to the fields in the HANA view imply changes for the external view (in ABAP Dictionary).

​

  • 2011 SAP AG. All rights reserved.

45

RKT

46 of 130

External Views

  • When an external view is created, the names and data types of the fields from the HANA view/model are mapped onto compatible DDIC names and DDIC data types (default mapping).

HANA Data Type

Description

ABAP Dictionary Type

SMALLINT

2-Byte integer

INT2

INTEGER

4-Byte integer

INT4

DECIMAL

Packed number

DEC

SMALLDECIMAL

Packed number

DEC

FLOAT

Binary floating point number

FLTP

VARCHAR

Character string

CHAR

  • 2011 SAP AG. All rights reserved.

46

RKT

47 of 130

External Views

HANA Data Type

Description

ABAP Dictionary Type

NVARCHAR

Unicode character string

CHAR

VARBINARY

Byte string

RAW

BLOB

Byte string

RAWSTRING

CLOB

Character string

STRING

NCLOB

Unicode character string

STRING

  • HANA names that are compatible with DDIC naming rules (for example, they do not contain more than 30 characters) are mapped onto DDIC fields with the same name. �
  • 2011 SAP AG. All rights reserved.

47

RKT

48 of 130

External Views

  • Since the creation of a external view, the implementation of the related HANA view may change. Thus it is occasionally necessary to perform sync function. This gets the ABAP Dictionary object in sync with the latest definitions in the corresponding HANA view.

​

​

​

  • For creating an External view:
    • Open the context menu of your package and choose New 🡪 Other ABAP Repository Object 🡪 Dictionary 🡪 Dictionary View .

​

  • 2011 SAP AG. All rights reserved.

48

RKT

49 of 130

External Views

    • Enter a Name* and a Description for the view to be created.

​

​

​

​

    • Select the option External View.
    • Enter the name of the appropriate HANA View that has already been created and activated in the HANA Repository. (use Ctrl + Space for F4 help)
    • This creates an inactive version of a Dictionary view for the corresponding HANA view. The view definition will include default mappings of field names and data types.
    • Activate the external view.

*NOTE: Maximum length for names of external views is limited to 16 characters.

​

  • 2011 SAP AG. All rights reserved.

49

RKT

50 of 130

Analytic View�

  • 2011 SAP AG. All rights reserved.

50

RKT

51 of 130

Modeling Views �Analytic View

    • Analytic View: Mostly processed in OLAP Engine
      • represents an OLAP view with a Star Schema including measures and attributes.
      • Optimized for mass data processing and aggregations. Used for “Currency Conversions” and “Unit conversions” functionalities
      • Attribute View(s) (master data) are joined to the Fact Table (Data foundation) as dimensions to give context to key figures (measures)
      • Data is not stored but read at run time from the joined database tables.
  • 2011 SAP AG. All rights reserved.

51

RKT

52 of 130

Analytic View �OLAP Star Schema - Example

Fact table

    • The Data Foundation can have multiple tables, but only one Fact table can contain measures.

N

1

⟖

Attribute View

​

​

Delivery Header

Delivery ID

Delivery Date

Ship-to Party

Sold-to Party

Sales Org

Material

Material ID

Material Class

Data Foundation

Delivery Item

Delivery ID

Item ID

Material

Measure

  • 2011 SAP AG. All rights reserved.

52

RKT

53 of 130

Creating Analytic Views

    • In HANA Studio, right-click on your Package and choose New -> Analytic View
    • Enter Name and Description and click on Finish.
  • 2011 SAP AG. All rights reserved.

53

RKT

54 of 130

Creating Analytic Views

    • Now drag & drop a Fact table into the Data Foundation section.
    • In the same way drag & drop Attribute Views into the Logical Join section.
  • 2011 SAP AG. All rights reserved.

54

RKT

55 of 130

Creating Analytic Views�Select output, define Joins

    • At the Data Foundation section, right-click on the desired Attributes or Measures and click on ‘Add to Output.

​

​

​

​

    • At the Logical Join section, build the Joins between Fact table / Data Foundation and Attribute Views. Specify the Join type and Cardinality.

​

  • 2011 SAP AG. All rights reserved.

55

RKT

56 of 130

Creating Analytic Views�Define attributes and measures

    • At the Semantics section, tab Local, define which output columns are attributes and which are measures.
    • For measures, define the aggregation type ( sum, max, min, count).

​

attribute

measure

aggregation type

    • Then Save, validate and activate.
      • A runtime object will be created in the Catalog Schema _SYS_BIC.

​

  • 2011 SAP AG. All rights reserved.

56

RKT

57 of 130

Creating Analytic Views�Data Preview

    • You can see the result by pressing Data Preview and by executing queries in SQL Editor.

​

    • Remember that in the SQL statement you have to write the full view name:

Select * from “_SYS_BIC”.“<package_name>/<view_name>”

  • 2011 SAP AG. All rights reserved.

57

RKT

58 of 130

Defining Join Types

    • Left (Right) Outer Join
      • Returns all rows from the left (right) table, even if there are no

matches in the right (left) table.

      • If records match, they are combined, otherwise the columns are empty.
      • The Join and constraints (filters) will be executed only if fields from the joined table are requested.
    • Inner Join
      • Returns rows when there is at least one match in both tables.
      • Combines records from the left and right table exactly when the specified criteria are met.
      • If the criteria are not met, no record is created in the result set.
      • Is normally slower than Left Outer Join, as the join operation is always executed, no matter which fields are requested. Should be used if constraints on the joined table have to be always applied.

​

left

right

left

right

  • 2011 SAP AG. All rights reserved.

58

RKT

59 of 130

Defining Join Types

    • Referential Join
      • Default Join Type.
      • Semantically an Inner Join that assumes that referential integrity

is given (e.g. ERP tables) and matching data exists in the joined tables.

      • Referential Join (Inner Join) is only executed if fields from both joined tables are selected.
      • If not, it works like a Left or Right Outer Join depending on the selected fields.

​

left

right

left

right

  • 2011 SAP AG. All rights reserved.

59

RKT

60 of 130

Results depending on Join Types�Example

SALE_ID

COST

ITEM_ID

sale 1

20000

hnd

sale 2

25000

tyo

sale 3

30000

bmw

sale 4

35000

mrd

sale 5

20000

bmw

ITEM_ID

ITEM_DESCR

hnd

Hyundai

tyo

Toyota

frd

Ford

mrd

Mercedes

Fact table – Data Foundation

Dimension table – Attribute View

JOIN on ITEM_ID

SALE_ID

COST

ITEM_ID

ITEM_DESCR

sale 1

20000

hnd

Hyundai

sale 2

25000

tyo

Toyota

sale 3

30000

bmw

?

sale 4

35000

mrd

Mercedes

sale 5

20000

bmw

?

    • Left Outer Join returns all the rows from left table and corresponding rows from right table if they exist.
    • Inner Join returns only rows with ITEM_IDs that exist in both tables.

SALE_ID

COST

ITEM_ID

ITEM_DESCR

sale 1

20000

hnd

Hyundai

sale 2

25000

tyo

Toyota

sale 4

35000

mrd

Mercedes

SALE_ID

COST

sale 1

20000

sale 2

25000

sale 3

30000

sale 4

35000

sale 5

20000

SALE_ID

COST

ITEM_ID

ITEM_DESCR

sale 1

20000

hnd

Hyundai

sale 2

25000

tyo

Toyota

sale 4

35000

mrd

Mercedes

BUT

    • Referential Join in that situation returns different amount of rows depending on the Select statement because referential integrity is broken here (‘bmw’ is absent in Items table).
  • 2011 SAP AG. All rights reserved.

60

RKT

61 of 130

Calculation View�

  • 2011 SAP AG. All rights reserved.

61

RKT

62 of 130

Modeling Views�Calculation View

    • Calculation View:
      • is mostly processed in Calculation Engine.
      • Use Calculation Views when the application logic can’t be modeled with an Analytic/Attribute View.
      • It is defined as graphical or scripted Calculation View (SQL Script).

​

​

  • 2011 SAP AG. All rights reserved.

62

RKT

63 of 130

Types of Calculation Views

    • Graphical Calculation View
    • Scripted Calculation View (=SQL Script based Calculation View)

Graphical Calculation View

SQL Script based Calculation View

(here with CE functions)

Union

Projection

Analytic View

Union

Projection

Analytic View

Column View structure

  • 2011 SAP AG. All rights reserved.

63

RKT

64 of 130

Graphical Calculation View�

  • 2011 SAP AG. All rights reserved.

64

RKT

65 of 130

Graphical Calculation View�

Graphical Calculation View

      • Only graphical elements are used, no SQL Script coding needed.
      • Union, Join, Projection and Aggregation nodes are available.
      • Analytic Views, Attribute Views, tables and other Calculation Views – all can be used in a Calculation view

Analytic View

Attribute View or Column Store table

Calculation View

  • 2011 SAP AG. All rights reserved.

65

RKT

66 of 130

Create Graphical Calculation View

    • Right-click your Package and select New 🡪 Calculation View
    • Choose ‘Graphical’ View Type.
    • Click Next to select needed Tables or Attribute/Analytic/Calculation Views.
  • 2011 SAP AG. All rights reserved.

66

RKT

67 of 130

Create Graphical Calculation View�Join, Union, Projection or Aggregation nodes

    • Connect tables and views as sources via Join, Union, Projection or Aggregation nodes with the Output node:
      • Join is used to connect fields of 2 different sources with Join. In the Join node, specify the joined fields.
      • Union is used to combine fields of multiple different sources. Combined fields need to have the same data type.
  • 2011 SAP AG. All rights reserved.

67

RKT

68 of 130

Create Graphical Calculation View�Join, Union, Projection or Aggregation nodes

    • Connect tables and views as sources via Join, Union, Projection or Aggregation nodes with the Output node:
      • Projection is used to select only the needed columns from the source,to add calculated columns.
      • Aggregation is used to aggregate data on specified fields, for example from raw tables. For each aggregated measure you can choose the aggregation type (sum, max, min).
  • 2011 SAP AG. All rights reserved.

68

RKT

69 of 130

Create Graphical Calculation View �Specify Attributes and Measures, Activate

    • As next step you have to specify Attributes and Measures for the Output node.

​

​

​

​

​

​

​

    • Finally you can save, validate and activate the Calculation View and do a Data Preview of the result.
  • 2011 SAP AG. All rights reserved.

69

RKT

70 of 130

Scripted Calculation View �

  • 2011 SAP AG. All rights reserved.

70

RKT

71 of 130

Scripted Calculation View �

    • Scripted Calculation Views are created using SQL Script:
      • SQL Script can query existing Attribute , Analytic Views , tables , Calculation views.
      • SQL Script can be used to create complex calculation functions.
    • Scripted Calculation views provide more flexibility in implementing business logic as compared to Graphical calculation view.

​

​

  • 2011 SAP AG. All rights reserved.

71

RKT

72 of 130

Scripted Calculation View �Example, SQL Script

​

    • Scripted Calculation Views are created using SQL Script or CE functions.
    • When activated, a Column View is created for the Output variable VAR_OUT
    • VAR_OUT is the only output variable (table type) of Scripted calculation view.

Column View

  • 2011 SAP AG. All rights reserved.

72

RKT

73 of 130

Scripted Calculation View�Example, CE functions

Union of both Analytic Views

Projection = Filtering or adding calculated columns

Analytic View 2

    • This Scripted Calculation View in this example uses CE functions.

Analytic View 1

  • 2011 SAP AG. All rights reserved.

73

RKT

74 of 130

Create Scripted Calculation View

    • Right-click your Package and select New 🡪 Calculation View.

​

    • To create a Scripted Calculation View choose ‘SQL Script’ as View Type.

​

  • 2011 SAP AG. All rights reserved.

74

RKT

75 of 130

Create Scripted Calculation View

    • Specify the fields for the Output Parameter var_out as result.
    • Enter the SQL Script.

​

​

​

​

​

    • Select Attributes and

Measures for the Output node.

    • Finally activate it.

​

  • 2011 SAP AG. All rights reserved.

75

RKT

76 of 130

�CE Functions

    • Inside the SQL Script, SQL language and also highly optimized Calculation Engine functions (CE Functions) can be used.
    • Execution of CE Functions can benefit from internal parallelization.
    • CE Functions should not be mixed with standard SQL statements because different optimizer engines are used.

​

    • But: Higher development

and maintenance efforts for

CE Functions, limited list

of available functions.

CE functions

SQL

  • 2011 SAP AG. All rights reserved.

76

RKT

77 of 130

CE Functions

    • CE_COLUMN_TABLE - get the contents of a table
    • CE_JOIN_VIEW - get attribute view contents
    • CE_OLAP_VIEW - procedure to get analytical view contents
    • CE_CALC_VIEW - get calculation view contents
    • CE_JOIN - inner join of two tables
    • CE_LEFT_OUTER_JOIN - left outer join
    • CE_RIGHT_OUTER_JOIN - right outer join
    • CE_UNION_ALL - union for two tables, identical tuples won’t be dropped
    • CE_PROJECTION - apply a filter, compute expressions, rename column.
    • CE_CALC - calculate an expression into a new column
    • CE_AGGREGATION - aggregation of the data to a group of attributes

Supported aggregation functions: count, sum, min, max, average = sum/count

    • CE_VERTICAL_UNION - concatenation of columns of two input tables,

no counterpart in SQL

    • CE_CONVERSION - unit conversion of input table, no counterpart in SQL
  • 2011 SAP AG. All rights reserved.

77

RKT

78 of 130

SQL vs. CE Functions�Examples 1

​

SQL

CE-Build In Function

SELECT on Column table

SELECT

A, B, C

from

"COLUMN_TABLE"

CE_COLUMN_TABLE("COLUMN_TABLE", [A, B, C])

SELECT on Attribute view

SELECT

A, B, C �from

"ATTRIBUTE_VIEW"

CE_JOIN_VIEW("ATTRIBUTE_VIEW", [A, B, C])

SELECT on Analytical view

SELECT

A, B, C, SUM(D)

from

"ANALYTIC_VIEW"

GROUP BY

A, B, C

CE_OLAP_VIEW("ANALYTIC_VIEW", [A, B, C, D]);

SELECT on Calculation View

SELECT

A, B, C, SUM(D)

from

“CALC_VIEW"

GROUP BY

A, B, C

CE_CALC_VIEW("ANALYTIC_VIEW", [A, B, C, D]);

​

  • 2011 SAP AG. All rights reserved.

78

RKT

79 of 130

SQL vs. CE Functions�Examples 2

​

SQL

CE-Build In Function

WHERE clause

SELECT

A, B, C, SUM(D)

from

"ANALYTIC_VIEW"

WHERE

B = 'value' AND C = 'value'

var_tab =

CE_COLUMN_TABLE("COLUMN_TABLE");

var_proj =

CE_PROJECTION(:var_tab, [A, B, C], ' "B" = ''value'' AND "C" = ''value'' ');

GROUP BY

SELECT

A, B, C, SUM(D)

FROM

"COLUMN_TABLE"

GROUP BY � A, B, C

var_tab =

CE_COLUMN_TABLE("COLUMN_TABLE");

var_agg =

CE_AGGREGATION(:var_tab, [SUM(D)], [A, B, C]);

INNER JOIN

SELECT A, B, Y, SUM(D)

from "COLTAB1" INNER JOIN "COLTAB2"

ON

"COLTAB1"."KEY1" = "COLTAB2"."KEY1" AND

"COLTAB1"."KEY2" = "COLTAB2"."KEY2"

CE_JOIN("COLTAB1","COLTAB2", [KEY1, KEY2], [A, B, Y, D])

LEFT OUTER JOIN

SELECT A, B, Y, SUM(D)

from "COLTAB1" LEFT OUTER JOIN "COLTAB2"

ON

"COLTAB1"."KEY1" = "COLTAB2"."KEY1" AND

"COLTAB1"."KEY2" = "COLTAB2"."KEY2"

CE_LEFT_OUTER_JOIN("COLTAB1","COLTAB2", [KEY1, KEY2], [A, B, Y, D])

  • 2011 SAP AG. All rights reserved.

79

RKT

80 of 130

SQL vs. CE Functions�Examples 3

​

SQL

CE-Build In Function

UNION ALL

var_tab1 = SELECT A, B, C, D FROM "COLUMN_TABLE1";

var_tab2 = SELECT A, B, C, D FROM "COLUMN_TABLE2";

​

SELECT * FROM :var_tab1 UNION ALL

SELECT * FROM :var_tab2;

var_tab1 =

CE_COLUMN_TABLE("COLUMN_TABLE1",[A,B,C,D]);

var_tab2 =

CE_COLUMN_TABLE("COLUMN_TABLE2",[A,B,C,D]);

var_out = CE_UNION_ALL(:var_tab1,:var_tab2);

UNION

var_tab1 = SELECT A, B, C, D FROM "COLUMN_TABLE1";

var_tab2 = SELECT A, B, C, D FROM "COLUMN_TABLE2";

​

SELECT * FROM :var_tab1 UNION

SELECT * FROM :var_tab2;

n/a

​

use CE_UNION_ALL

SQL Expressions

SELECT A, B, C, SUBSTRING(D,2,5)

FROM "COLUMN_TABLE"

var_tab =

CE_COLUMN_TABLE("COLUMN_TABLE");

var_proj =

CE_PROJECTION( :var_tab, ["A", "B", "C",

CE_CALC('midstr("D",2,5)', string) ]);

Right OUTER JOIN

SELECT A, B, Y, SUM(D)

from "COLTAB1" LEFT OUTER JOIN "COLTAB2"

ON

"COLTAB1"."KEY1" = "COLTAB2"."KEY1" AND

"COLTAB1"."KEY2" = "COLTAB2"."KEY2"

CE_RIGHT_OUTER_JOIN("COLTAB1","COLTAB2", [KEY1, KEY2], [A, B, Y, D])

  • 2011 SAP AG. All rights reserved.

80

RKT

81 of 130

HANA Views:

Which to use when�

  • 2011 SAP AG. All rights reserved.

81

RKT

82 of 130

Modeling Views�

Analytic View

Attribute View

Column Tables

Calculation View

:can use

Legend:

Calculation Engine

Join Engine

OLAP Engine

  • 2011 SAP AG. All rights reserved.

82

RKT

83 of 130

SQL Script for Procedures-

Introduction

  • 2011 SAP AG. All rights reserved.

83

RKT

84 of 130

SQL Script�Introduction

  • SQL Script is a collection of extensions to Structured Query Language (SQL) which allow developers to push data intensive logic into the HANA Database. It is the key for avoiding expensive copying of huge amount of data from DB to AS.
  • SQL Script allows to use control structures (like IF..ELSE, FOR, WHILE, CASE etc..) which is not possible in simple open SQL’s.
      • Compared to SQL, SQLScript is much more powerful in implementing business logics.

​

  • 2011 SAP AG. All rights reserved.

84

RKT

85 of 130

SQL Script vs SQL Queries�Introduction

​

SQL Script

SQL Queries

SQL Script can be used to implement complex business logic.

It therefore allows complex business logic to be pushed to the database level.

SQL Queries do not have features to implement business logic.

As a consequence a business logic cannot be pushed down into the database using SQL queries.

​

​

​

SQL script can return multiple result sets.

Therefore the computation of multiple result sets can be done in one access to the Database.

SQL query can only return one result at a time.

For the computation of multiple result sets multiple Database accesses are necessary.

Note: For more details on how to write SQL script, refer to Appendix

HANA database provides Database Procedures to push down business logic to database. The logics are programmed in SQL Script language.

  • 2011 SAP AG. All rights reserved.

85

RKT

86 of 130

HANA Database Procedures

  • 2011 SAP AG. All rights reserved.

86

RKT

87 of 130

Procedure�Introduction

  • HANA provides Procedures to implement very complex application logic.
      • Procedures provides much higher flexibility which HANA views might lack.
      • Only input parameters and final result sets (more than one possible) move between application and HANA when the procedure is executed.
  • 2011 SAP AG. All rights reserved.

87

RKT

88 of 130

Procedure�Creation

  • To create a Procedure in HANA, use HANA Studio and create a new Procedure for your package:
  • 2011 SAP AG. All rights reserved.

88

RKT

89 of 130

Procedure�Procedures with more than on result set

  • Currently ADBC cannot be used to access Procedures that return more than one result set.
  • Such Procedures have to be accessed by exposing them to ABAP DDIC *.

​

*More details during DEMO

  • 2011 SAP AG. All rights reserved.

89

RKT

90 of 130

Which HANA Artifact to use�

  • 2011 SAP AG. All rights reserved.

90

RKT

91 of 130

Decide on where to Build Content?

Analyze Query

Logic

Information in existing tables

Only Joins & calculated expressions

no

Access Base Tables

yes

Use Attribute Views

Star Schema or Aggregation

no

yes

Use Analytic Views

yes

Calculation View

no

Use Graphical or Scripted Calc Views

yes

Use DB Procedures with SQLScript

no

  • 2011 SAP AG. All rights reserved.

91

RKT

92 of 130

Pros and Cons of different HANA Artifacts�

  • 2011 SAP AG. All rights reserved.

92

RKT

93 of 130

Code Pushdown – Pros and Cons of different HANA Artifacts

​

1: Attribute View

2: Analytic View

3: Calculation View (Graphical)

4: Scripted Calculation View (SQL script/CE Functions)

5: Database Procedures

Usage

Used for simple applications involving joins and calculated expressions

Used for analytical purposes where read operations on mass data and aggregations are required.

Used for implementing business logics that can’t be modeled using only Attribute or Analytic Views.

​

Used for implementing business logics that can’t be modeled using Attribute or Analytic Views or graphical CalcView.

Used for implementing complex business logics that can’t be modeled using Attribute or Analytic Views or CalcView (Graphical and scripted) .

Pros

Easy to model as compared to other artifacts. Good performance when dealing with joins.

Very good performance when dealing with huge amount of data and aggregations.

User-friendly 🡪 No SQL, SQL Script or CE functions knowledge required. Supports UNION.

​

Gives high flexibility as compared to other views to implement complex business logic. Supports UNION

Gives highest flexibility for implementing very complex logics. Multiple result-sets can move from HANA to AS in one call to the database.

Cons

No support for complex calculation and logic e.g. currency conversion etc.

Limitations in regards to functionalities e.g. No UNION supported.

Limitations in regards to graphical functions. Only UNION, AGGREGATION, JOIN and PROJECTION supported

​

High efforts for development because have to write/code own logic. High in complexity if CE functions are used.

Performance depends on how the logic is written (if CE functions are used, if the queries are dependent on each other , if cursor is used for single line access). Performance is not always optimal as compared to other views.

  • 2011 SAP AG. All rights reserved.

93

RKT

94 of 130

Database Procedure Proxy

  • 2011 SAP AG. All rights reserved.

94

RKT

95 of 130

Database Procedure Proxy

  • Database procedure proxies are ABAP repository objects that make it possible to access HANA DB procedures ( HANA-based application/business logic) in ABAP codes.
  • Database procedure proxies are stored and managed in the ABAP Dictionary.
  • Database procedure proxies define mappings for the following:
    • Names of the input and output parameters from the database procedure and the parameter names to be used in ABAP
    • Types that are defined for the parameters in the database procedure and the ABAP data types.

​

  • 2011 SAP AG. All rights reserved.

95

RKT

96 of 130

Database Procedure Proxy

​

​

​

​

​

​

  • For each database procedure proxy, an ABAP interface is also generated, where the appropriate ABAP data types are defined.

​

ABAP Interface that contains type Definition

Importing Parameter

Exporting Parameter

Table type

Scalar type

  • 2011 SAP AG. All rights reserved.

96

RKT

97 of 130

Database Procedure Proxy

  • For each HANA database procedure only one database procedure proxy should be created. Even though several proxies can be created for one database procedure, this approach is not recommended.
  • For creating DB procedure proxy:
    • Open the context menu of your package and choose New 🡪 Other ABAP Repository Object 🡪 Dictionary 🡪 Database Procedure Proxy.
    • Enter the Name and Description for the proxy to be created. (use Ctrl + Space for F4 help)

​

​

​

  • 2011 SAP AG. All rights reserved.

97

RKT

98 of 130

Database Procedure Proxy

    • The wizard automatically proposes a name ZIF_<PROXY_NAME> for the ABAP interface to be created. We can change the proposal if wanted.
    • This creates an inactive version of a proxy for the corresponding HANA database procedure. The proxy definition will include default mappings of field names and data types.
    • Activate the proxy.

​

​

​

  • 2011 SAP AG. All rights reserved.

98

RKT

99 of 130

Database Procedure Proxy

  • When a database procedure proxy is created, the names and data types of input and output parameters from the HANA DB procedure are mapped onto compatible ABAP names and data types (default mapping).
  • Names of the components of table-type parameters can be changed . This is particular need and useful during MOVE- CORRESPONDING ABAP command.
  • One HANA data type can be mapped onto several compatible ABAP data types. All the options of compatible data types are available in the drop down and can be chosen.
  • With the DDIC Type Override function, we can use already existing suitable data elements or structures in the ABAP Dictionary for the data type mapping. ABAP data types are overridden then.

Note – With DDIC type override only data elements and flat structures from the ABAP Dictionary can be assigned to the proxy parameters as data types. Use of deep structure types is not supported

  • 2011 SAP AG. All rights reserved.

99

RKT

100 of 130

Database Procedure Proxy

  • Changing parameter names to be used in ABAP code:

​

​

​

​

  • Changing data types to be used in ABAP code

​

  • 2011 SAP AG. All rights reserved.

100

RKT

101 of 130

Database Procedure Proxy

  • Overriding data types with DDIC types :

​

​

​

  • 2011 SAP AG. All rights reserved.

101

RKT

102 of 130

Database Procedure Proxy

  • Checking the consistency of the Proxy checks the below:
    • The changed parameter names are valid ABAP names (whether they contain valid characters).
    • There is consistency between the HANA repository and the proxy definition in the ABAP repository.

​

​

​

  • 2011 SAP AG. All rights reserved.

102

RKT

103 of 130

Database Procedure Proxy

  • Since the creation of a proxy, the implementation of the related HANA DB procedure may change. Thus it is occasionally necessary to perform sync function. This gets the ABAP Dictionary object in sync with the latest definitions in the corresponding HANA DB procedure.

​

​

​

  • 2011 SAP AG. All rights reserved.

103

RKT

104 of 130

Database Procedure Proxy

  • Calling DB procedure proxy in ABAP:

​

​

​

  • 2011 SAP AG. All rights reserved.

104

RKT

105 of 130

DAY 2�Summary: HANA specific solution – Code pushdown

You should now be able to:

  • Understand HANA views, SQL Script and DB procedures in HANA.
  • Understand and create External views.
  • Understand and create DB procedure proxy

​

  • 2011 SAP AG. All rights reserved.

105

RKT

106 of 130

Demo + Exercises – HANA Specific solutions – Code pushdown

Lessons

Topics

Demos + Exercise

Modeling, ABAP: Creating a Attribute View to calculate the DAYS OPEN (difference between todays’ date and date on which SO was created) for each SO.

​

Accessing the attribute view with OPEN SQL.

​

​

Demos

Performance: Open days in ABAP v/s Open days in HANA

​

​

Demos + Exercise

Modeling , ABAP: Creating Analytical view to calculate gross amount per business partner in USD.

​

Accessing the analytical view through ADBC.

​

​

Demos

Performance: Currency conversion in ABAP v/s Currency conversion in HANA

​

​

Demos

Modeling: Creating Calculation views to mark specific Business Partners as SPECIAL based on predefined conditions (Scripted and Graphical)

​

​

  • 2011 SAP AG. All rights reserved.

106

RKT

107 of 130

Demo + Exercises – HANA Specific solutions – Code pushdown

Lessons

Topics

Demos + Exercise

Code Push down: Writing DB procedure to find the Business partners with highest 5 and bottom 5 Gross Revenue

​

​

Demos+ Exercise

Code Push down, ABAP: Creating DB procedure Proxy and calling DB procedures from ABAP code.

​

​

  • 2011 SAP AG. All rights reserved.

107

RKT

108 of 130

Relation between the tables used in Exercises and Demos

​

​

​

  • 2011 SAP AG. All rights reserved.

108

RKT

109 of 130

Appendix

ABAP Database Connectivity - ADBC

  • 2011 SAP AG. All rights reserved.

109

RKT

110 of 130

CL_SQL_CONNECTION

  • If you want to execute SQL statements on another database, an instance of CL_SQL_CONNECTION should be created first to represent one of the connections which registered in AS ABAP.
  • The GET_CONNECTION method of this class can be passed a connection name from the column DBCON (SAP T-code SM30). This method attempts to open the corresponding connection and, if successful, it creates an instance of CL_SQL_CONNECTION and returns the corresponding reference.

​

DATA lo_con_ref   TYPE REF TO cl_sql_connection. 

Connected to ABC:

lo_con_ref = cl_sql_connection=>get_connection( ‘ABC’ ).

.

  • 2011 SAP AG. All rights reserved.

110

RKT

111 of 130

CL_SQL_CONNECTION

  • Instances of CL_SQL_CONNECTION that are created with CREATE OBJECT represent the standard connection to the central database.

Connected to Central DB:

CREATE OBJECT lo_con_ref.

  • 2011 SAP AG. All rights reserved.

111

RKT

112 of 130

CL_SQL_CONNECTION

  • References to instances of CL_SQL_CONNECTION can be passed to the CON_REF parameter of the instance constructor of CL_SQL_STATEMENT. The instances created in this way execute their SQL statements on the database whose connection is represented by the instance of CL_SQL_CONNECTION.

CREATE OBJECT lo_sql EXPORTING con_ref = lo_con_ref.

​

  • The database connection is closed by the instance method CLOSE of CL_SQL_CONNECTION. This method has no effect in instances that represent the standard connection.

​

​

  • 2011 SAP AG. All rights reserved.

112

RKT

113 of 130

Example Code�A Simple Statement to Secondary Database Connection

DATA: lo_sql TYPE REF TO cl_sql_statement,

lo_result TYPE REF TO cl_sql_result_set,

lr_flight TYPE REF TO DATA,

lt_flight TYPE TABLE OF sflight.

CREATE OBJECT lo_sql

EXPORTING con_ref = cl_sql_connection=>get_connection( 'HDB' ).

lo_result = lo_sql->execute_query(

'SELECT * FROM SFLIGHT WHERE ...' ).

GET REFERENCE OF lt_flight INTO lr_flight.

lo_result->set_param_table( lr_flight ).

lo_result->next_package( ).

lo_result->close( ).

  • 2011 SAP AG. All rights reserved.

113

RKT

114 of 130

CX_SQL_EXCEPTION

  • The execution codes of SQL statements should be surrounded with TRY – CATCH control structures in case errors of SQL statement or database itself occur.
  • When any error occurs in the execution of the SQL statement, it will be caught and a instance of CL_SQL_EXCEPTION will be created. The error can then be handles appropritalely.

​

​

​

​

​

  • 2011 SAP AG. All rights reserved.

114

RKT

115 of 130

CX_SQL_EXCEPTION

TRY .

lo_result = lo_sql->execute_query('...').

...

CATCH cx_sql_exception

INTO lo_err.

MESSAGE lo_err 

TYPE 'I' 

DISPLAY LIKE 'E'.

ENDTRY.

​

Attribute

Meaning

DB_ERROR

"X", if an SQL statement was not executed by DBMS.

DBOBJECT_EXISTS

"X", if you want to create a database object that already exists.

DBOBJECT_NOT_EXISTS

"X", if you want to access a database object that does not exist.

DUPLICATE_KEY

"X", if a DML statement would violate a unique table key.

INTERNAL_ERROR

Internal error code from DBMS.

INVALID_CURSOR

"X", if you want to use an invalid or closed database cursor.

SQL_CODE

Database-specific error code, if DB_ERROR is "X".

SQL_MESSAGE

Database-specific error code, if DB_ERROR is "X".

  • 2011 SAP AG. All rights reserved.

115

RKT

116 of 130

Appendix

SQL Scripts in HANA

  • 2011 SAP AG. All rights reserved.

116

RKT

117 of 130

SQL Script�Data Types

  • SQL Script supports the below scalar data types.

Scalar Data types:

  • Integer: TINYINT, SMALLINT, INTEGER, BIGINT
  • Float: DECIMAL(p, s), REAL, FLOAT, DOUBLE
  • Character: VARCHAR, NVARCHAR, CLOB, NCLOB
  • Binary: VARBINARY, BLOB
  • Time: DATE, TIME, TIMESTAMP

​

  • But in addition, it also allows to use and define user-defined table types.

​

​

  • 2011 SAP AG. All rights reserved.

117

RKT

118 of 130

SQL Script�Data Types

Table Type Definition

  • SQL Script allows you to define and use user-defined table types.
  • These table types are used to define parameters for a procedure that represent tabular results.
  • They are also used to store the result set of SQL statement for further data manipulation within the procedure.

Syntax:

CREATE TYPE {schema.}name AS TABLE (attribute1 type1 , attribute2 type2,...)

  • 2011 SAP AG. All rights reserved.

118

RKT

119 of 130

SQL Script�Table Types

  • The table type is specified using a list of attribute names and primitive data types. For each table type, attributes must have unique names.
  • In order to create a table type in a different schema than the current default schema, the schema has to be provided as a prefix.
  • Table types do not have an instance that is created when the table type is created. No DML is supported on the table types.

​

tt_publishers

publisher

INTEGER

name

VARCHAR(50)

price

DECIMAL

cnt

INTEGER

  • 2011 SAP AG. All rights reserved.

119

RKT

120 of 130

SQL Script�Table Types

  • A table type can be dropped using the DROP TYPE statement.
  • DROP TYPE {schema.}name {CASCADE};
  • By default, the drop statement invalidates dependent objects. For example, if a procedure uses the table type in its parameters, it will be invalidated.

​

  • 2011 SAP AG. All rights reserved.

120

RKT

121 of 130

SQL Script�Writing simple procedure

  • Procedures describe a sequence of data transformations on data passed as input and database tables.
  • Procedures can have multiple input and output parameters which can be scalar types or table types.
  • It is a callable statement, and so it can be called using a CALL statement.

CREATE PROCEDURE testproc( IN cnt INTEGER, OUT result tt_result)

LANGUAGE SQLSCRIPT READS SQL DATA AS

BEGIN

RESULT = SELECT …… FROM dbtab WHERE count = :cnt;

End;

  • 2011 SAP AG. All rights reserved.

121

RKT

122 of 130

SQL Script�Writing simple procedure

CREATE PROCEDURE testproc( IN cnt INTEGER, OUT result tt_result)

LANGUAGE SQLSCRIPT

READS SQL DATA AS

​

BEGIN

RESULT = SELECT …… FROM dbtab WHERE count = :cnt;

END;

  • This SQLScript defines a read-only procedure which has 1 scalar input parameters and 1 output parameters of type table.
  • As different languages are supported in the body of the procedure, the implementation language is defined to be SQLScript.
  • The implementation language is by default SQLSCRIPT. It is good practice to define the language in all procedure definitions.

​

  • 2011 SAP AG. All rights reserved.

122

RKT

123 of 130

SQL Script�Writing simple procedure

CREATE PROCEDURE testproc( IN cnt INTEGER, OUT result tt_result)

LANGUAGE SQLSCRIPT

READS SQL DATA AS

​

BEGIN

RESULT = SELECT …… FROM dbtab WHERE count = :cnt;

END;

  • Procedure is tagged as read only procedure using READS SQL DATA.
  • Notice that it is a read-only procedure. It can only be called by other read-only procedures.
  • One factor to be considered is that neither DDL (create, alter, drop) nor DML (insert, update, delete) statements are allowed in its body.
  • The advantage to this definition is that certain optimizations are only available for read-only procedures.

​

  • 2011 SAP AG. All rights reserved.

123

RKT

124 of 130

SQL Script�Writing Simple procedures

  • The below command Drops a procedure created by CREATE PROCEDURE from the database catalog.

​

DROP PROCEDURE {schema.}name {CASCADE}

​

  • If a cascading drop is defined, dependent objects will also be dropped. If a cascading drop is not defined, dependent objects will be invalidated.
  • 2011 SAP AG. All rights reserved.

124

RKT

125 of 130

SQL Script�Writing Simple procedures

  • The below command is used to CALL a procedure

CALL {schema.}name (param1 {, ...})

E.g. call testproc (10,lt_result);

​

  • 2011 SAP AG. All rights reserved.

125

RKT

126 of 130

SQL Script�Example of a Procedure

CREATE PROCEDURE getOutput( IN cnt INTEGER, IN currency VARCHAR(3), OUT output_pubs tt_publishers, OUT output_year tt_years) LANGUAGE SQLSCRIPT READS SQL DATA AS

BEGIN

big_pub_ids = SELECT publisher AS pid FROM books -- Query Q1 GROUP BY publisher HAVING COUNT(isbn) > :cnt;

big_pub_books = SELECT title, name, publisher, -- Query Q2 year, price FROM :big_pub_ids, publishers, books WHERE pub_id = pid AND pub_id = publisher AND crcy = :currency;

output_pubs = SELECT publisher, name, -- Query Q3 SUM(price) AS price, COUNT(title) AS cnt FROM :big_pub_books GROUP BY publisher, name;

output_year = SELECT year, SUM(price) AS price, -- Query Q4 COUNT(title) AS cnt FROM :big_pub_books GROUP BY year;

END;

  • 2011 SAP AG. All rights reserved.

126

RKT

127 of 130

SQL Script�Writing simple procedures

  • In some cases, applications have to update the database content.
  • We can have scalar variables as defined as local variables in the procedure. Local variables can optionally be initialized with their declaration. NULL is the default value for local variables
  • Here is a simple example:;

lv_bp_id varchar(10):= ‘0100000001';

SELECT bp_id, company_name, phone_number

INTO lv_bp_id, lv_company_name, lv_phone_number

FROM SAPNVM.SNWD_BPA WHERE bp_id = :lv_bp_id;

‘:’ before variable means getting value of the variable.

‘:=’ is the assignment operator which assign the value on the left side to the variable on the right side.

​

​

  • 2011 SAP AG. All rights reserved.

127

RKT

128 of 130

SQL Script�Control Structures (IF structure)

  • IF <bool-expr1> THEN {then-stmts1} {ELSEIF <bool-expr2> THEN {then-stmts2}} {ELSE {else-stmts3}} END IF
  • The IF statement consists of a boolean expression – bool-expr1. If this expression evaluates to true then the statements – then-stmts1 – in the mandatory THEN block are executed. The IF statement ends with END IF. The remaining parts are optional.
  • 2011 SAP AG. All rights reserved.

128

RKT

129 of 130

SQL Script�Control Structures (WHILE and FOR)

  • While loop structure:

WHILE <bool-stmt> DO

{statements}

END WHILE;

  • For loop structure:

FOR <loop-var> IN {REVERSE} <start> .. <end> DO

{statements}

END FOR;

  • Break command ( BREAK;) and Continue command ( CONTINUE;) - You can use break to immediately leave the loop and continue to immediately resume with the next iteration.
  • 2011 SAP AG. All rights reserved.

129

RKT

130 of 130

SQL Script�Interesting Example

  • There are many useful expression available in SQL in HANA. Here is a interesting one.

​

​

​

​

​

​

​

Note: Please find a complete reference of SAP HANA database SQL in SAP HANA Database - SQL Reference Manual.

​

​

  • 2011 SAP AG. All rights reserved.

130

RKT