SQLScript for SAP HANA

Browse the Book

This sample chapter demonstrates the techniques for using SQLScript

procedures for ABAP programs. It starts with the most important concept,

AMDP, followed by the other development objects of the AMDP frame-

work. The end of this chapter briefly shows you an alternative to AMPD

and give some basic recommendations for using database procedures

in ABAP programs.

Jörg Brandeis

SQLScript for SAP HANA

387 pages, 2nd, updated and revised edition 2021, $79.95

ISBN978-1-4932-2139-4

www.sap-press.com/5336

First-hand knowledge.

“SQLScript in ABAP Programs”

Contents

Index

The Author

283

Chapter 8

SQLScript in ABAP Programs

To enable the use of SQLScript from within ABAP programs, various

options are available, for example, the ABAP-managed database proce-

dures (AMDP) framework or table functions. In this chapter, we’ll provide

you an overview of these techniques.

In previous chapters, you learned how to write effective SQLScript source

code. You can use various options to call this source code from within the

SAP NetWeaver ABAP platform. From the ABAP perspective, SQLScript basi-

cally is Native SQL, which is how all database-specific SQL dialects are

described. The opposite of Native SQL is Open SQL, which is part of the ABAP

programming language. Open SQL is database-independent and is trans-

lated into the corresponding Native SQL at runtime.

Consider Other Database Systems

If you use Native SQL in ABAP, the corresponding programs and classes are

restricted for use in a particular database system. To be compatible for

multiple database systems, you must develop along several tracks. Accord-

ingly, you should create different implementations for the various SQL

variants. In this chapter, we’ll focus on following a two-track approach in

our examples, using both techniques:

쐍 SQLScript for SAP HANA databases

쐍 Open SQL for all other databases

In the following section, you will learn the techniques for using SQLScript

procedures for ABAP programs. We’ll start with the most important con-

cept, AMDP, followed by the other development objects of the AMDP

framework. And at the end of this chapter, we will briefly show you an alter-

native to AMPD and give some basic recommendations for using database

procedures in ABAP programs.

8.1 AMDP Procedures

From the point of view of an ABAP developer, an AMDP procedure is a

method of an ABAP class, which is implemented in the SQLScript

284

8 SQLScript in ABAP Programs

programming language. Being a method solves several problems, such as

the following:

쐍 The SQL procedure is called as a method call of an ABAP class. As a result,

the procedure call is perfectly integrated in ABAP.

쐍 The transport of the database procedure is performed using the ABAP

class rather than requiring several transport mechanisms that must be

kept in sync.

쐍 At the time of development, the developer does not need direct access to

the SAP HANA database with the appropriate authorizations. Working

with the usual ABAP developer authorizations with the ABAP develop-

ment tools is sufficient.

In the following sections, the terms AMDP and AMDP procedure are used

interchangeably. If not explicitly mentioned, the information doesn’t refer

to AMDP functions.

Before we jump into using AMDP, however, we’ll first provide you with an

initial introduction to some of the concepts of AMDP.

8.1.1 Introduction to AMDP

AMDP The term ABAP-managed database procedure (AMDP) stands for the frame-

work recommended by SAP for using SQLScript code in ABAP programs.

This concept serves as the basis for creating, managing, and calling data-

base procedures and functions. The basic idea behind AMDP is that the

source code of SAP HANA database objects can be wrapped in an imple-

mentation of methods of an ABAP class. The corresponding SAP HANA

database object is then generated from this implementation when

required.

Three different objects can be created with the AMDP framework:

쐍 AMDP procedures can be called like methods of an ABAP class. These pro-

cedures are implemented in SQLScript. The caller of the method doesn’t

know that the procedure is actually a database procedure.

쐍 Core data services (CDS) table functions are AMDP functions that are

encapsulated by a CDS object and can therefore be called from ABAP or

Open SQL like a normal database view via a

SELECT query.

쐍 AMDP functions for AMDP methods can’t be called directly from ABAP or

Open SQL. However, these functions can be used when implementing

other AMDP objects in SQLScript source code.

Table 8.1 compares the most important properties of the three objects.

285

8.1 AMDP Procedures

AMDP Framework

With the release of SAP NetWeaver 7.4 Support Package Stack (SPS) 05,

AMDPs were introduced as a framework for managing and calling data-

base procedures in the SAP HANA database.

With the release of SAP NetWeaver 7.5, AMDP functions have been added

to this concept. Although these functions are technically not procedures,

but functions, the name AMDP is also used for them.

AMDP classes are classes that implement the interface

IF_AMDP_MARKER_

HDB

and that contain at least one AMDP method.

AMDP methods can be either AMDP procedures or AMDP functions. AMDP

functions either implement a CDS table function, or AMDP functions are

available internally for other AMDP methods.

ToolsThe AMDP framework provides ABAP developers with extensive options

for implementation according to the code-to-data paradigm. Direct access

to the SAP HANA database is not absolutely necessary since all develop-

AMDP Procedure CDS Table Function AMDP Function

Call from

ABAP

Like an ABAP

method

In a SELECT state-

ment

Not possible

Implementa-

tion

In a static or

instance method

In PUBLIC, static

method of a static

class

In a static or

instance method

Two-track

development

Inheritable Possible by case

distinction

Not relevant, since

only callable from

other AMDP meth-

ods

Type of data

access

Read and write Read-only Read-only

Where are the

parameters

defined?

When you define

the method

When you define

the CDS object

When you define

the method

Type of

parameters

Any IMPORTING,

EXPORT, and

CHANGE

Any scalar IMPORT-

ING

and exactly

one table-like

RETURNING param-

eter

Any scalar IMPORT-

ING

and exactly

one table-like

RETURNING param-

eter

Table 8.1 Comparison of the Three AMDP Objects

286

8 SQLScript in ABAP Programs

ment objects can be implemented via the ABAP development tools in the

Eclipse development environment. You can also use a debugger for the

SQLScript code in the AMDP methods. This AMDB debugger is described in

more detail in Chapter 11, Section 11.2.3.

If SAP HANA data models such as calculation views are modeled or if proce-

dures and functions are developed outside the AMDP framework, direct

database access with corresponding authorizations will be necessary. The

Eclipse plug-ins from SAP HANA Studio must also be installed.

Benefits of AMDP When does using SQLScript in an ABAP program make sense? No general

answer exists for this question. Basically, the use of AMDPs makes sense

above all when significant performance improvements can be achieved,

which is particularly the case in the following scenarios:

쐍 If using SQLScript prevents the transport of large amounts of data

between the database and SAP NetWeaver Application Server (AS).

쐍 If the SQLScript procedure is programmed declaratively and can there-

fore be easily parallelized and optimized by the SAP HANA database.

쐍 If transformations with routines are to be executed directly in the SAP

HANA database in SAP Business Warehouse (SAP BW). Data transfer pro-

cesses will be accelerated considerably, regardless of whether the previ-

ous ABAP code worked with database access or not.

Disadvantages But the use of AMDPs also has a few disadvantages. You must always con-

sider whether you want to accept these disadvantages for the expected per-

formance improvements. The following reasons, among others, speak

against the use of AMDPs:

쐍 The application developer must be proficient in several programming

languages. Sometimes, a well-implemented ABAP method is better than

a poorly implemented AMDP.

쐍 SAP GUI is not suitable for developing AMDPs.

쐍 The application logic is distributed between the database server and SAP

NetWeaver Application Server (AS).

쐍 Performance is not improved in all cases.

쐍 AMDP developments can’t be used in other database systems. For a suit-

able procedure for parallel development, still leveraging the advantages

of SAP HANA with AMDPs, Section 8.1.5.

Alternatives You should always evaluate the alternatives before using AMDPs, includ-

ing, for example, CDS views and the extended features of Open SQL of the

latest SAP NetWeaver releases. According to the SAP documentation (http://

s-prs.co/v533626), AMDPs should only be used if database-specific functions,

such as the SQL function for currency conversion, is required but not avail-

287

8.1 AMDP Procedures

able in Open SQL, or if the transport of large amounts of data between the

database server and SAP NetWeaver Application Server (AS) must be

avoided.

SPACE and the Empty Character String

In ABAP, a global constant SPACE, of length 1, contains exactly one blank

character. This constant is often used for selecting data, for example, in

the following

WHERE clause:

WHERE recordmode = SPACE

In fact, however, the database doesn’t store the space character, but an

empty string of length 0. ABAP therefore does not distinguish between an

empty string and a single space character.

However, SQLScript does distinguish between these two strings. For use in

an ABAP system, SAP has introduced the

ABAPVARCHAR mode. This mode

converts all string literals with exactly one blank character into an empty

string in SQLScript as well, as in the following query:

SELECT session_context( 'ABAPVARCHARMODE' ) FROM DUMMY

Furthermore, you can test whether this mode is switched on. Since this

mode is controlled by the session variable

ABAPVARCHAR, you can also turn

this mod on and off in the code, as shown in Listing 8.1.

DO BEGIN

SET 'ABAPVARCHARMODE' = 'true';

SELECT length(' '),

'->' || ' ' || '<-' FROM dummy;

SET 'ABAPVARCHARMODE' = 'false';

SELECT length(' '),

'->' || ' ' || '<-' FROM dummy;

END;

Listing 8.1 Testing the ABAP VARCHAR mode

The result depends on the mode set. If you really need exactly one space as

a string in SQLScript, use the expression

CHAR(32).

8.1.2 Creating AMDP Procedures

IF_AMDP_MARKER_

HDB

You can create an AMDP method only in a global class, which implements

the marker interface

IF_AMDP_MARKER_HDB. The interface itself has no meth-

ods; it serves exclusively to mark AMDP classes.

288

8 SQLScript in ABAP Programs

An AMDP class can include both normal ABAP methods and AMDP meth-

ods. Whether a method is implemented as AMDP is not specified in the

method declaration. Listing 8.2 shows an example.

CLASS zcl_amdp_demo DEFINITION

PUBLIC

CREATE PUBLIC .

PUBLIC SECTION.

INTERFACES if_amdp_marker_hdb.

TYPES gty_tt_countries TYPE TABLE OF t005t .

METHODS get_countries

IMPORTING

VALUE(iv_langu) TYPE langu

EXPORTING

VALUE(et_country) TYPE gty_tt_countries

CHANGING

VALUE(cv_subrc) TYPE sy-subrc.

ENDCLASS.

CLASS zcl_amdp_demo IMPLEMENTATION.

METHOD get_countries

BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT

USING t005t.

et_country = select *

from t005t

where spras = :iv_langu;

SELECT CASE

WHEN COUNT(*) > 0

THEN 0

ELSE 4

END AS subrc

INTO cv_subrc

FROM :et_country;

ENDMETHOD.

ENDCLASS.

Listing 8.2 Example of a Simple AMDP Method

Restrictions on the

method signature

An SQLScript procedure is generated at a later time from the source code of

an AMDP method. However, these database procedures are more restrictive

289

8.1 AMDP Procedures

with regard to parameters than ABAP methods. Accordingly, the following

restrictions must of course also apply to AMDP methods:

쐍 First, all parameters must be completely typed. Generic data types such

TYPE TABLE or REF TO DATA are not permitted.

쐍 Since SQLScript doesn’t know any structures, only scalar data types and

table types may be used as parameters. The table types can only use sca-

lar data types in their row structure.

쐍 All parameters must be transferred as value parameters (call by value);

you can’t use any reference parameters (call by reference). The reason for

this restriction is obvious when you consider that the application server

is running on a different system than the database. Accordingly, no

shared memory area exists that both systems reference.

쐍 You can only use IMPORTING, EXPORTING, and CHANGING parameters with

AMDP procedures. The use of

RETURNING parameters is not possible.

쐍 Only AMDP exception classes can be declared in the signature of the

method. These exception classes are the subclasses of

CX_AMDP_ERROR. If

these exception classes are declared, the caller of the AMDP method can

handle these exceptions. However, if not declared, these errors result in

a dump.

Implementing

an AMDP

AMDP procedures are developed in the SQLScript language, which is indi-

cated to the system by the addition

BY DATABASE PROCEDURE FOR HDB LANGUAGE

SQLSCRIPT.

If an AMDP implementation only reads data, you can optionally specify the

addition

OPTIONS READ-ONLY.

USINGThe optional addition USING enables you to tell an AMDP implementation

that you want to use the corresponding tables, views, AMDP functions, or

AMDP procedures from the default database schema of the SAP NetWeaver

system. Thus, you can use this name without having to explicitly specify

the relevant database schema in the SQLScript source code. The objects are

only separated by whitespaces and listed after the keyword

USING. Note that

this code is still ABAP code. If, for example, you specify a table generated by

SAP BW from the generation namespace,

/BIC/ or /BI0/, you do not need to

use any quotation marks, for example:

...USING /BI0/PPLANT.

If you access this table in SQLScript code, however, you must use special

notation, since the slash is not permitted in simple notation, as in the fol-

lowing:

SELECT ... FROM "/BI0/PPLANT".

290

8 SQLScript in ABAP Programs

USING for

AMDP objects

The use of AMDP procedures and functions is declared in the USING clause

via the name of the associated ABAP method using the notation

CLASS=

>METHOD. Listing 8.3 contains an example of the keywords in the METHOD

clause of the method implementation. Everything after the period in line 5

up to the keyword

ENDMETHOD is interpreted as SQLScript.

1 METHOD get_countries

2 BY DATABASE PROCEDURE FOR HDB

3 LANGUAGE SQLSCRIPT

4 OPTIONS READ-ONLY

5 USING t005t.

7 <SQLScript-Code>

8 ENDMETHOD.

Listing 8.3 Keywords for Implementing an AMDP Method

A General Concept for a Specific Database

Basically, all instructions and declarations in connection with AMDP are

designed in such a way that AMDPs can be implemented in different data-

base systems and in different languages. Accordingly, the implementation

of AMDP methods also includes the mandatory entries

FOR <databasesys-

tem> and LANGUAGE <querylanguage>.

However, to date (SAP NetWeaver 7.51), only implementations for the SAP

HANA database with the SQLScript language are possible. The language L

may only be used within SAP.

The other database systems under SAP NetWeaver each have their own

programming languages, such as Oracle’s PL/SQL language. However,

these languages can’t be used to implement AMDPs.

8.1.3 Generated Objects of an AMDP Method

An ABAP class serves as an envelope for the code of the AMDP procedure.

Some SAP HANA database objects are automatically generated from this

class. Figure 8.1 shows the objects generated by the procedure found in List-

ing 8.2.

Note that a table type, two procedures, a database table, and a view have

been generated for an AMDP method. Each object has its own special tasks,

which we’ll explain next.

291

8.1 AMDP Procedures

Figure 8.1 Generated Objects of an AMDP Procedure

The Generated AMDP Procedure

The procedure ZCL_AMDP_DEMO=>GET_COUNTRIES contains the actual SQLScript

source code. However, the code has been slightly modified, as shown in

Listing 8.4.

CREATE PROCEDURE

"ZCL_AMDP_DEMO=>GET_COUNTRIES"

(

IN "IV_LANGU" NVARCHAR (000001),

OUT "EV_SUBRC" INTEGER,

IN "CT_COUNTRY__IN__" "ZCL_AMDP_DEMO=>GET_COUNTRIES=>P00000#

ttyp",

OUT "CT_COUNTRY" "ZCL_AMDP_DEMO=>GET_COUNTRIES=>P00000#ttyp"

)

LANGUAGE sqlscript SQL SECURITY INVOKER

AS BEGIN

"CT_COUNTRY" = select * from :CT_COUNTRY__IN__ ;

BEGIN

lt_countries = SELECT land1 FROM :CT_COUNTRY;

et_country = SELECT t5.*

FROM "ZCL_AMDP_DEMO=>T005T#covw" AS t5

INNER JOIN :LT_COUNTRIES AS countries

ON t5.land1 = countries.land1

WHERE t5.spras = :IV_LANGU;

SELECT CASE

WHEN COUNT(*) > 0

292

8 SQLScript in ABAP Programs

THEN 0

ELSE 4

END AS subrc

INTO ev_subrc

FROM :CT_COUNTRY;

END;

Listing 8.4 Source Code of Procedure ZCL_AMDP_DEMO=>GET_COUNTRIES

Parameter list The relevant passages in the source code are highlighted in bold type. First,

you’ll see the parameter list. Notice that the

CHANGING parameter CT_COUNTRY

occurs twice. This repetition occurs because, in SQLScript, an INOUT parame-

ter must have a scalar type. Thus, in addition to the

OUT parameter CT_COUN-

TRY, the IN parameter CT_COUNTRY__IN__ is created. In the first line after AS

BEGIN, the parameter CT_COUNTRY is filled with the contents of CT_COUNTRY__

IN__

, which simulates the behavior of a CHANGING parameter.

Table Types for the Table Parameters

In the parameter interface of an SQLScript procedure, all parameters must

be typed. In the case of table parameters, corresponding table types are cre-

ated for AMDP procedures. In our example,

CT_COUNTRY is typed using the

generated table type

ZCL_AMDP_DEMO=>GET_COUNTRIES =>P00000#ttyp.

USING Views

Each access to a table declared via USING is encapsulated by a view. In our

example, this view is the database view

ZCL_AMDP_DEMO =>T005T#covw, which

replaces direct access to table

T005T. The task of the view is to compensate

for any field sequences that differ between the ABAP Dictionary and the

database object.

Stub Procedure

In addition to the actual AMDP procedure, a second procedure is generated,

which is named according to the name pattern

<AMDPprocedure>#stb2#

. Accordingly, in our example, the procedure name is ZCL_AMDP_

DEMO=>GET_COUNTRIES#stb2#20180301173139.

This procedure serves as a stub for the call from SAP NetWeaver. The time-

stamp (Section 8.1.4) is used for versioning purposes in case the object

changes.

293

8.1 AMDP Procedures

The source code shown in Listing 8.5 involves only two parameters: IV_

LANGU

and EV_SUBRC. The CHANGING table parameter CT_COUNTRIES is not part of

the parameter definition of the procedure.

Return of the tableThe return of the table CT_COUNTRIES is not carried out via an explicitly

defined table parameter but via the result set of the procedure. After the call

of the AMDP procedure, a

SELECT query is executed to the table CT_COUN-

TRIES.

CREATE PROCEDURE

"ZCL_AMDP_DEMO=>GET_COUNTRIES#stb2#20180301173139"

(

IN "IV_LANGU" NVARCHAR (000001),

OUT "EV_SUBRC" INTEGER

)

LANGUAGE sqlscript SQL SECURITY INVOKER AS BEGIN

"CT_COUNTRY__IN__" = SELECT * FROM "ZCL_AMDP_DEMO=>GET_COUNTRIES=

>P00000#tft#20180301173139" ;

CALL "ZCL_AMDP_DEMO=>GET_COUNTRIES" (

"CT_COUNTRY__IN__" => :CT_COUNTRY__IN__ ,

"IV_LANGU" => :IV_LANGU ,

"EV_SUBRC" => :EV_SUBRC ,

"CT_COUNTRY" => :CT_COUNTRY

);

SELECT * FROM :CT_COUNTRY;

END;

Listing 8.5 Source Code of the Stub Procedure

Input Tables as Global Temporary Tables

The values of table parameters are transferred into the stub procedure via

generated, global temporary tables. Before the actual AMDP procedure is

called, the table variables are initialized from the global temporary table. In

our example,

CT_COUNTRIES is filled from the global temporary table in the

following way:

"CT_COUNTRY__IN__" = SELECT * FROM "ZCL_AMDP_DEMO=>GET_COUNTRIES=>

P00000#tft#20180301173139"

Here again, a timestamp is part of the name.

294

8 SQLScript in ABAP Programs

8.1.4 Lifecycle of the Generated Objects

The lifecycle of the AMDP classes and methods is not identical to the lifecy-

cle of their corresponding SAP HANA objects. In the following sections,

we’ll describe the effects of various actions carried out in the SAP

NetWeaver Application Server (AS) on the corresponding SAP HANA

objects:

Object lifecycles 쐍 Creating and activating an AMDP class

Creating and activating an AMDP class with an AMDP method doesn’t

generate any database objects. Thus, you can transport AMDP classes to

SAP NetWeaver systems without an SAP HANA database. If an SAP HANA

database is available as the central database system, the database objects

are temporarily created and immediately deleted again for the syntax

check.

쐍 Executing an AMDP method

Database objects, introduced in the previous section, are generated

during the first execution of the AMDP method. The timestamp in the

object’s name refers to the activation time of the class.

Even if a class contains several AMDP methods, only the objects for the

executed methods are generated.

쐍 Repeated editing and activation of an AMDP class

The stub procedures for all AMDP procedures are deleted when the class

is reactivated. The same applies to all generated AMDP procedures that

have changed. The generated global temporary table will remain for the

time being.

The next time the program is executed, the objects are created again

with the new timestamp. Since global temporary tables are not deleted

immediately, some objects with different timestamps can still exist at

the same time.

쐍 Deleting an AMDP class

The generated objects on SAP HANA are deleted upon the deletion of the

AMDP class.

The late generation of SAP HANA objects means that transporting AMDP

classes in a mixed system landscape can run smoothly. Versioning of

changes is carried out using timestamps to ensure that the correct version

of a procedure is always called.

8.1.5 Two-Track Development

There are different reasons why you would want both an ABAP and an AMDP

implementation of a method. From a technical point of view, developing

295

8.1 AMDP Procedures

both is not a problem, since whether a method is written in SQLScript or

ABAP is only determined when the method is implemented and doesn’t

affect the definition of the method. In the following sections, we’ll look at a

few typical use cases where more than one implementation exists.

Clean Code: Separation of Concerns

The principle of the separation of concerns was formulated in 1974 by Eds-

ger W. Dijkstra. Under this principle, different tasks should also be per-

formed by different components in a system. In our case, separation of

concerns means that database access should not be mixed with other

application code. Instead, different classes should be created for each

aspect.

If you adhere to this principle, two-track development of AMDP and ABAP

implementations is also relatively simple. Classes for database access can

be easily exchanged.

Support of Different Database Systems

If the application needs to run on different database systems, you can have

one implementation in Open SQL and one in AMDP. Of course, you can also

create special implementations for each of the other databases.

Switching between

implementations

Switching between the various implementations should take place auto-

matically at runtime. For this purpose, you can use the factory design pat-

tern. As shown in Figure 8.2, we’ll use a static factory method,

GET_INSTANCE,

to generate the instances. Based on the

SY-DBSYS field, this method decides

which class implementation should be used.

Figure 8.2 UML Diagram for Two Implementations of Database Access with a

Static Factor Method

ZCL_READ_XYZ_OSQL

+ EXECUTE( ): Table of data

ZCL_READ_XYZ_AMDP

+ EXECUTE( ): Table of data

ZCL_READ_XYZ

+ EXECUTE( ): Table of data

+ GET_INSTANCE( ): ZCL_READ_XYZ

Extends Extends

296

8 SQLScript in ABAP Programs

Listing 8.6 shows a simple implementation of the method GET_INSTANCE. Of

course, other database systems could also be considered.

METHOD get_instance.

DATA lv_classname TYPE classname.

CASE sy-dbsys.

WHEN 'HDB'.

lv_classname = 'ZCL_READ_XYZ_AMDP'.

WHEN OTHERS.

lv_classname = 'ZCL_READ_XYZ_OSQL'.

ENDCASE.

CREATE OBJECT ro_instance TYPE (lv_classname).

ENDMETHOD.

Listing 8.6 Static Factory Method GET_INSTANCE in ABAP

Comparison between ABAP and AMDP

If you want to test the differences between the implementations in ABAP

and AMDP in runtime and result, inheritance is rather useful concept as in,

for example, the previous section where we leveraged inheritance from a

shared superclass. However, you’ll need a suitable switch that allows you

the flexibility to select the relevant implementation. You can create these

switches, for example, by using entries in a Customizing table or by defin-

ing user parameters. These switches can then be queried in a corresponding

factory method.

Retroactive Implementation as AMDP

Let’s say you implemented a method in ABAP and later discover perfor-

mance problems; you can implement the method as an AMDP. To preserve

the original implementation for performance and result comparisons, cre-

ating a subclass is a good idea. The corresponding method is then imple-

mented as an AMDP in this subclass.

Refactoring Due to restrictions on AMDPs regarding access to instance attributes and

parameters, this procedure must be taken into account when creating your

methods. Alternatively, you can carry out a refactoring, as shown in Figure

8.3, by following this approach:

1. Outsource the logic into a new method that meets the requirements of

an AMDP: no access to class and instance data, no

RETURNING parameters,

no parameters with structures.

2. Create a subclass.

3. Redefine the corresponding method as an AMDP.

297

8.1 AMDP Procedures

Figure 8.3 Outsourcing the Logic to an AMDP-Compatible Method and

Redefinition

To switch between the two versions of the class, you can use a factory

method again.

ABAP Unit Tests

A mock object is often used to insert data into a class to be tested in a unit

test. This object has the same external interface as a real object but provides

predefined data that is independent of the database state. Classes that con-

tain an AMDP can, for example, be replaced for unit tests by a local subclass

in which the AMDP method has been replaced by a corresponding ABAP

implementation. For such tests, you can use the dependency injection

design pattern.

Only in global

classes

Note that AMDP implementation is only possible in global classes. In local

classes, only an implementation in ABAP is allowed.

8.1.6 Using AMDP Procedures in Other AMDP Procedures

An AMDP method generates a corresponding database procedure the first

time it is executed (see also Section 8.1.4). This database procedure can of

course also be called directly in any SQLScript code, especially in other

AMDP procedures. Thus, a certain modularization of the programs is possi-

ble without direct development access to the database.

ExampleListing 8.7 shows a simple example of calling an AMDP procedure in

another AMDP method. Note that a

CHANGING parameter in the AMDP

method has the following two associated parameters in the corresponding

database table, as described in Section 8.1.3:

MY_CLASS_AMDP

# AMDP_SUITABLE_METHOD_X( )

MY_CLASS

+ METHOD_X( )

MY_CLASS

+ METHOD_X( )

# AMDP_SUITABLE_METHOD_X( )

Extends

Refactoring

298

8 SQLScript in ABAP Programs

쐍 CT_PRICE

쐍 CT_PRICE__IN__

In this case, you must assign these parameters yourself in order to call the

procedure correctly.

Avoid CHANGING Table Parameters

In the database procedure, the CHANGING table parameters are imple-

mented in two parameters. As shown in Listing 8.7, the code needed for a

direct call of this procedure can look confusing to untrained readers

because the

CT_PRICES__IN__ parameter was not defined anywhere in the

ABAP class. If you use these two parameters instead, the code will be more

readable:

쐍 IT_PRICES for entering the table

쐍 ET_PRICES for the output of the table

If the generated procedures are only called using ABAP methods, whether

or not you use

CHANGING table parameters doesn’t matter.

CLASS zcl_amdp_call DEFINITION PUBLIC.

PUBLIC SECTION.

TYPES: BEGIN OF ty_s_price,

item TYPE numc4,

net_price TYPE wertv9,

gross_price TYPE wertv9,

vat TYPE wertv9,

curr TYPE waers,

END OF ty_s_price.

TYPES ty_t_price TYPE STANDARD TABLE OF ty_s_price.

INTERFACES if_amdp_marker_hdb.

METHODS calculate_vat

IMPORTING

VALUE(iv_vat) TYPE int1

CHANGING

VALUE(ct_price) TYPE ty_t_price.

METHODS calculate_gross_price

IMPORTING

299

8.1 AMDP Procedures

VALUE(iv_vat) TYPE int1

CHANGING

VALUE(ct_price) TYPE ty_t_price.

ENDCLASS.

CLASS zcl_amdp_call IMPLEMENTATION.

METHOD calculate_gross_price BY DATABASE PROCEDURE

FOR HDB LANGUAGE SQLSCRIPT

USING zcl_amdp_call=>calculate_vat.

CALL "ZCL_AMDP_CALL=>CALCULATE_VAT"(

iv_vat => :iv_vat,

ct_price => :ct_price,

ct_price__in__ => :ct_price );

ct_price = SELECT item,

net_price,

net_price + vat as gross_price,

vat,

curr,

FROM :ct_price;

ENDMETHOD.

METHOD calculate_vat BY DATABASE PROCEDURE

FOR HDB LANGUAGE SQLSCRIPT.

ct_price = SELECT item,

net_price,

gross_price,

net_price * :iv_vat / 100 as vat,

curr,

FROM :ct_price;

ENDMETHOD.

ENDCLASS.

Listing 8.7 Calling an AMDP Procedure from Another AMDP Method

USINGAll AMDP procedures used must be declared in an AMDP method via USING,

which ensures that the necessary database objects are generated as

required. Note that the class constructor of the AMDP classes used is called

before the method is executed.

300

8 SQLScript in ABAP Programs

8.2 CDS Table Functions

A CDS table function is an AMDP function that provides table data and can

be consumed from ABAP in Open SQL, like a database view.

Implementation The implementation of a CDS table function is similar to an AMDP proce-

dure as a method of an ABAP class. A corresponding user-defined function

is then generated from this method in the SAP HANA database. Since user-

defined functions can’t be consumed directly in Open SQL, the AMDP func-

tions are encapsulated by a CDS object. An AMDP function can therefore

also be considered a programmed CDS view. The CDS object can then be

used normally, such as a database view, in Open SQL.

Core Data Services

The classic ABAP Dictionary manages database objects such as data types

(data elements, structures, table types); database tables; and views. The

definitions of these objects are stored and managed in a database-neutral

form in the associated database tables (

DD*) at the ABAP application server

level. Depending on the database system used, the appropriate database

objects are then generated from the stored definition. The classic ABAP

Dictionary, which can be called via Transaction SE11, only allows the limited

use of the SQL options, especially for views.

CDS are a common concept that exists both directly in the SAP HANA data-

base and on the SAP NetWeaver AS ABAP. With CDS, database objects such

as views or table functions can be defined with the help of their own lan-

guage, which is based on the data definition language (DDL) of SQL. These

data models can be enriched with semantic information using annota-

tions.

8.2.1 Creating a CDS Table Function

To create a CDS table function, two related objects must be created:

쐍 The CDS table function is defined in a DDL source with the parameters

and the table type of the return table. This step implicitly defines the sig-

nature of the associated AMDP method.

쐍 The AMDP function is implemented in a method of a static ABAP class.

This step contains the SQLScript source code for the table function.

Interaction Figure 8.4 shows the interaction between a CDS table function and an

AMDP function.

301

8.2 CDS Table Functions

Figure 8.4 Objects Involved in a CDS Table Function

CDS Table Function

CDS table functions

wizard

You can use a wizard to create CDS table functions from the ABAP develop-

ment tools of the Eclipse development environment. Start by clicking New 폷

Other ABAP Repository Object in the context menu of the development

package and then choose Core Data Services 폷 Data Definition in the follow-

ing dialog. Figure 8.5 shows the corresponding wizard. To create a CDS table

function, you must select the Define Table Function with Parameters tem-

plate.

The wizard then generates a basic structure of the corresponding source

code of the CDS table function, as shown in Listing 8.8.

@EndUserText.label: 'Example for a table function'

define table function Z_CDS_TF

with parameters parameter_name : parameter_type

returns {

client_element_name : abap.clnt;

element_name : element_type;

}

implemented by method class_name=>method_name;;

Listing 8.8 Basic Structure of the Definition of a CDS Table Function

SAP NetWeaver

Application Server for ABAP

SAP HANA

CDS Table Function AMDP Function

User-Dened Fields

Function

USING Views

IMPLEMENTED BY …

FOR TABLE FUNCTION …

Generate

302

8 SQLScript in ABAP Programs

Figure 8.5 Wizard for Creating CDS Objects

In this section, we’ll describe only a rudimentary syntax for defining CDS

objects, which are not the focus of the book. For more information on CDS

objects, we recommend the SAP document “SAP HANA Core Data Services

(CDS) Reference,” at http://s-prs.co/v533627.

Components The definition of the CDS table function consists of the following compo-

nents:

쐍 @EndUserText.label

This annotation contains the descriptive text for the CDS table function

previously specified in the wizard.

쐍 define table function <CDS_entity>

This component indicates the start of the definition of the CDS table

function. The name of the CDS entity is in the same namespace as the

data types of the ABAP Dictionary. Therefore, a CDS entity must not have

the same name as, for example, a database table.

쐍 with parameters <parameterlist>

If the CDS table function will have parameters, these parameters can be

defined in this component. The use of parameters is optional. The client

is often specified as a parameter, which will allow you to select data by

client in the function, as shown in Listing 8.10.

303

8.2 CDS Table Functions

쐍 returns { <fieldlist> }

The field list defines the structure of the return table. If the CDS table

function is client-dependent, the first field must have the ABAP Dictio-

nary type

CLNT. For example, you can use the data element MANDT.

쐍 implemented by method <classname>=><methodname>

This component tells the CDS table function by which AMDP method the

implementation is performed. The CDS entity can be activated if the

associated method doesn’t exist yet.

AMDP Function

The actual implementation of the CDS table function occurs an AMDP

method in a static, global ABAP class, which requires the implementation of

the marker interface

IF_AMDP_MARKER_HDB. Unlike AMDP procedures, AMDP

functions for CDS table functions are static methods. The parameters are

not defined individually in the method definition. Instead, you’ll use the

addition

FOR TABLE FUNCTION <CDS_table_function_name> to establish the ref-

erence to the associated CDS table function. The parameters defined in the

CDS entity are then available in the function implementation.

The method implementation must be structured as shown in Listing 8.9.

METHOD <method_name> BY DATABASE FUNCTION

FOR HDB LANGUAGE SQLSCRIPT

OPTIONS READ-ONLY

[USING <usages>].

<SQLScript-Code>

ENDMETHOD.

Listing 8.9 Structure of the Implementation of an AMDP Function for a CDS

Table Function

The additions in the METHOD statement are all mandatory except for the

USING specification. The use of USING in this context is similar as in AMDP

procedures.

Example of a CDS Table Function

For a functioning CDS table function, the definition of the CDS entity and

the implementation of the AMDP function belong together.

DefinitionListing 8.10 and Listing 8.11 contain some simple examples to show the

basics of CDS table functions using texts for countries read from table

T005T. In practice, your CDS table functions will be used for much more

complex scenarios that utilize multiple features of the SAP HANA database

and SQLScript.

304

8 SQLScript in ABAP Programs

@EndUserText.label: 'Country texts'

define table function z_country_text

with parameters

@Environment.systemField: #CLIENT mandt:mandt,

@Environment.systemField: #SYSTEM_LANGUAGE sy_langu:langu

returns {

mandt:mandt;

country:land1;

text:landx50;

}

implemented by method zjb_cl_country=>get_country_text;

Listing 8.10 Example of Defining a CDS Table Function

The client and language are taken from the system fields via the annotation

@Environment.systemField.

RETURN statement The implementation shown in Listing 8.11 consists of only a RETURN state-

ment, which returns the result of a

SELECT query.

CLASS zjb_cl_country DEFINITION PUBLIC.

PUBLIC SECTION.

INTERFACES if_amdp_marker_hdb.

CLASS-METHODS get_country_text

FOR TABLE FUNCTION z_country_text.

ENDCLASS.

CLASS zjb_cl_country IMPLEMENTATION.

METHOD get_country_text BY DATABASE FUNCTION

FOR HDB LANGUAGE SQLSCRIPT

OPTIONS READ-ONLY

USING t005t.

RETURN SELECT mandt,

land1 AS country,

landx50 AS text

FROM t005t

WHERE spras = :sy_langu

AND mandt = :mandt;

ENDMETHOD.

ENDCLASS.

Listing 8.11 Example of Implementing a CDS Table Function

Testing You can test the CDS table function using a simple ABAP program, as shown

in Listing 8.12.

305

8.2 CDS Table Functions

REPORT zjb_test .

SELECT *

FROM z_country_text

INTO TABLE @DATA(lt_country)

##db_feature_mode[amdp_table_function].

cl_demo_output=>display( lt_country ).

Listing 8.12 ABAP Program for Testing the CDS Table Function from the Example

Notice that the CDS table function can be read with a SELECT query, much

like a database table or a view.

Two-Track Implementation

You cannot create alternative implementations in ABAP or Open SQL for

CDS table functions. However, if your program needs to run on SAP HANA

as well as on other database systems, the caller of the CDS table function

must take care of an alternative implementation. To query at runtime

whether CDS table functions are available, you can use the

CL_ABAP_

DBFEATURES class. As shown in Listing 8.13, this class can be queried for the

existence of the used feature

AMDP_TABLE_FUNCTION.

IF cl_abap_dbfeatures=>use_features(

VALUE #( ( cl_abap_dbfeatures=>amdp_table_function ) ) ).

* Implementation with CDS table function

ELSE.

* Alternative implementation without SAP HANA features

ENDIF.

Listing 8.13 Checking the Availability of CDS Table Functions

The syntax check also indicates with a warning that database-dependent

functions are used when querying CDS table functions. You can use the

following pragma to disable the warning, as shown in Listing 8.12.

##db_feature_mode[amdp_table_function]

This pragma signals to the syntax check that the developer is aware that

this query will lead to errors in other database systems.

8.2.2 Generated Objects of a CDS Table Function

User-defined

function

As with AMDP procedures, database objects can also be generated in SAP

HANA for CDS table functions. First, a user-defined function is assigned the

306

8 SQLScript in ABAP Programs

same name as the associated static method of the AMDP function, as

shown in Listing 8.14.

create function

"ZJB_CL_COUNTRY=>GET_COUNTRY_TEXT"

(

"MANDT" NVARCHAR (000003),

"SY_LANGU" NVARCHAR (000001)

)

returns table

(

"MANDT" NVARCHAR (000003) ,

"COUNTRY" NVARCHAR (000003) ,

"TEXT" NVARCHAR (000050)

)

language sqlscript sql security invoker as begin

RETURN SELECT mandt,

land1 AS country,

landx50 AS text

FROM "ZJB_CL_COUNTRY=>T005T#covw"

WHERE spras = :SY_LANGU

AND mandt = :MANDT;

end;

Listing 8.14 Generated User-Defined Function for the AMDP Function

As with AMDP procedures, a view was also generated for the table specified

via

USING.

8.2.3 Implicit Client Handling of CDS Table Functions

By default, the implicit client handling of CDS table functions is enabled.

Thus, the first field in the field list must be of dictionary type

CLNT. Accord-

ingly, the developer must also ensure that this field is filled out correctly

with the source client of the data. In a

SELECT query to the CDS table func-

tion, all data with other clients are then implicitly filtered out.

To improve performance, you should filter out the data of other clients in

the AMDP function. To create a filter, you’ll define an input parameter for

the client, which is then automatically assigned the correct client via the

annotation @Environment.systemField: #CLIENT. The examples shown in

Listing 8.10 and Listing 8.11 illustrate the correct client handling.

307

8.3 AMDP Functions for AMDP Methods

Note the Client in the Join Condition

As an ABAP developer, you may not be accustomed to taking the client into

account since this task is usually undertaken by Open SQL. In SQLScript,

however, the client is only an ordinary database field. Be sure you make

the correct selection yourself, in particular for join conditions. If you don’t

select the client in this situation, the cartesian product of the data is

formed over all existing clients.

Client independentIf implicit client handling is not desired, you can disable it using the anno-

tation

@ClientDependent: false. Nevertheless, you can still have the client as

a field in the return structure of the CDS table function and select it as for

any other field.

Use Implicit Client Handling If Possible

Application and Customizing data are typically client specific. Thus, you

should always use the implicit the client handling feature of the CDS table

function.

8.3 AMDP Functions for AMDP Methods

If AMDP functions are only to be used in SQLScript source code by other

AMDP methods, defining an associated CDS table function is not necessary.

The definition and implementation of an AMDP function for AMDP meth-

ods differs in the following aspects:

쐍 The AMDP method can be a static method or an instance method. There-

fore, the associated AMDP classes don’t have to be static.

쐍 The method can also be declared in the private or protected visibility

area.

쐍 The parameters of the method are defined when the method is defined.

쐍 The addition FOR TABLE FUNCTION in the method definition is omitted.

8.3.1 AMDP Table Functions

AMDP table functions have been available in ABAP since SAP NetWeaver

7.40. They are defined with a fully typed, table-like

RETURNING parameter. In

the implementation, the result table must then be returned with the

RETURN

<table expression> statement.

308

8 SQLScript in ABAP Programs

AMDP functions

in different

AMDP methods

Listing 8.15 contains an example using an AMDP table function in a differ-

ent AMDP method. The direct call of the method

GET_COUNTRY_TEXT in an

ABAP method of the class

ZCL_AMDP_FUNC would not be possible.

CLASS zcl_amdp_func DEFINITION PUBLIC.

PUBLIC SECTION.

TYPES: BEGIN OF ty_s_country,

mandt TYPE mandt,

country TYPE land1,

text TYPE landx50,

END OF ty_s_country.

TYPES ty_t_country TYPE STANDARD TABLE OF ty_s_country

WITH DEFAULT KEY.

INTERFACES if_amdp_marker_hdb.

METHODS test_amdp_table_function

IMPORTING VALUE(iv_langu) TYPE langu

VALUE(iv_mandt) TYPE mandt

EXPORTING VALUE(et_country) TYPE ty_t_country.

PRIVATE SECTION.

METHODS get_country_text

IMPORTING VALUE(iv_langu) TYPE langu

VALUE(iv_mandt) TYPE mandt

RETURNING VALUE(rt_country) TYPE ty_t_country.

ENDCLASS.

CLASS zcl_amdp_func IMPLEMENTATION.

METHOD test_amdp_table_function BY DATABASE PROCEDURE

FOR HDB LANGUAGE SQLSCRIPT

OPTIONS READ-ONLY

USING zcl_amdp_func=>get_country_text.

et_country = select *

from "ZCL_AMDP_FUNC=>GET_COUNTRY_TEXT"

( iv_langu => :iv_langu,

iv_mandt => :iv_mandt);

ENDMETHOD.

METHOD get_country_text BY DATABASE FUNCTION

FOR HDB LANGUAGE SQLSCRIPT

OPTIONS READ-ONLY

USING t005t.

309

8.3 AMDP Functions for AMDP Methods

RETURN SELECT mandt,

land1 AS country,

landx50 AS text

FROM t005t

WHERE spras = :iv_langu

AND mandt = :iv_mandt;

ENDMETHOD.

ENDCLASS.

Listing 8.15 Example of Using an AMDP Table Function in a Different AMDP

Method

8.3.2 Scalar AMDP Functions

With SAP_BASIS component release 753, scalar functions can also be imple-

mented in the AMDP framework. In the definition, these functions differ

only in the scalar return type of the function. In the implementation, the

result is then not returned with the

RETURN statement, but the return

parameter is simply assigned.

We explored the structure of an AMDP function in connection with CDS

table functions earlier in Listing 8.9. For scalar AMDP functions, you can

also specify the

DETERMINISTIC option after OPTIONS if the same output is

always generated for the same input. This step allows the results of the sca-

lar function to be buffered from the database.

Listing 8.16 shows an example of a scalar function that cleans up values for

InfoObjects that are not allowed in an SAP BW system. Note that, although

this function finds and replaces most characters disallowed in SAP BW sys-

tems, other characters may cause problems.

CLASS zjb_bw_tools_amdp DEFINITION

PUBLIC

FINAL

CREATE PUBLIC .

PUBLIC SECTION.

INTERFACES if_amdp_marker_hdb.

METHODS replace_unallowed_characters

IMPORTING VALUE(iv_input) TYPE rschavl60

RETURNING VALUE(rv_output) TYPE rschavl60.

ENDCLASS.

CLASS zjb_bw_tools_amdp IMPLEMENTATION.

310

8 SQLScript in ABAP Programs

METHOD replace_unallowed_characters BY DATABASE FUNCTION

FOR HDB LANGUAGE SQLSCRIPT

OPTIONS READ-ONLY.

rv_output = CASE WHEN LEFT( :iv_input,1)='!'

THEN replace( iv_input ,'!' , '$')

WHEN :iv_input = '#'

THEN ''

ELSE :iv_input

END;

rv_output = replace_regexpr( '[[:cntrl:]]'

IN :rv_output WITH '' );

rv_output = replace( :rv_output, nchar( '0130'), '');

ENDMETHOD.

ENDCLASS.

Listing 8.16 Example of a Scalar AMDP Function

Performance of Scalar Functions

In general, scalar functions run the risk of having a negative impact on per-

formance. With the function shown in Listing 8.16, you can easily clean up

dozens of fields in a transformation routine in the SAP BW system. How-

ever, large amounts of data can quickly lead to performance problems.

Before using any scalar function, therefore, you should test them with

realistic data volumes to determine whether the scalar function is suffi-

ciently fast.

8.4 Alternatives to AMDP for Calling SQLScript Code from

ABAP Programs

With the AMDP framework, calling SQLScript can be elegantly integrated

into the ABAP language and Open SQL. A person reading an ABAP program

may not initially recognize that AMDP objects are actually hidden behind a

method call or a presumed table.

Alternative

techniques

However, with alternative techniques, you can execute SQLScript state-

ments from ABAP programs. These techniques may be necessary if second-

ary database connections will be used and can be helpful when executing

dynamic SQL code.

However, you can call SQLScript code from ABAP without using AMDPs in

several alternative ways:

311

8.5 Recommendations

쐍 ABAP Database Connectivity (ADBC)

ADBC is a class-based framework for running Native SQL. Individual SQL

statements, database connections, and result sets are represented as

ABAP objects. ADBC can be regarded as a successor to the static embed-

ding of Native SQL using the

EXEC SQL statement in ABAP code. In more

than name only, the ADBC framework is reminiscent of the similarly

structured Java Database Connectivity (JDBC) from the Java world.

쐍 EXEC SQL <Native SQL> ENDEXEC

This statement enables you to embed Native SQL statically into the ABAP

source code. However, SAP no longer recommends this approach.

Instead, ADBC is referenced for the use of Native SQL.

쐍 CALL DATABASE PROCEDURE

This ABAP statement allows you to call any database procedure on SAP

HANA via an associated proxy object. However, SAP recommends using

AMDPs instead, as long as the procedure will be called via the primary

database connection.

8.5 Recommendations

In this chapter, you learned some ways to directly access the objects of an

SAP HANA database in your programs. Even if trying out these new tech-

niques is tempting, let’s pause a moment to review some final consider-

ations:

쐍 If possible, you should continue to use Open SQL, as long as no signifi-

cant performance improvements are foreseeable. The ABAP language

has been extended in recent releases to include powerful functions, such

as the use of expressions and the option of combining multiple queries

via

UNION. More complex queries can also be mapped using CDS views.

쐍 However, if, for performance reasons or other considerations, executing

SQLScript code directly from ABAP programs still seems necessary, the

techniques of the AMDP framework on the SAP HANA database are the

weapon of choice for executing static SQLScript code.

쐍 If you need dynamic SQLScript code, ADBC is the right technique. How-

ever, dynamic SQL code is always problematic. On one hand, error analy-

sis is often more difficult, and on the other hand, you must guard against

security gaps and vulnerability to SQL injection attacks.

쐍 If you’re working with secondary database connections, you should con-

sider using a

CALL DATABASE PROCEDURE statement. Further, the autono-

mous transactions we discussed in Chapter 6, Section 6.6.2, can also

solve some classic use cases for secondary database connections more

elegantly.

312

8 SQLScript in ABAP Programs

Regardless of the technology used, the clean encapsulation of the database

access is always recommended in both AMDP and Open SQL. Only if you

bundle and encapsulate the database access in separate classes will you

have the necessary flexibility to carry out optimizations later without fear

of negative side effects.

8.6 Summary

Code execution on the SAP HANA database system can be extremely fast,

which you can effectively take advantage of with the concepts learned in

this chapter. With the AMDP framework, you have in your hands a tool that

allows you to easily create and call database procedures and functions. In

the next chapter, you’ll learn how this framework is used by SAP BW to

implement transformation routines in SQLScript.

Contents

Introduction ....................................................................................................................15

1 SAP HANA 21

1.1 What Is SAP HANA? ...................................................................................... 22

1.1.1 SAP HANA: A Fast SQL Database ............................................... 22

1.1.2 SAP HANA: An Application Server ............................................. 26

1.1.3 SAP HANA: A Collection of Tools ............................................... 27

1.2 System Architecture ..................................................................................... 29

1.2.1 SAP HANA Server Components .................................................. 29

1.2.2 Databases and Tenants ................................................................ 30

1.3 Organizing Database Objects .................................................................. 32

1.3.1 Database Schemas ......................................................................... 32

1.3.2 Database Catalogs ......................................................................... 34

1.3.3 Content and Repositories ............................................................ 35

1.4 Development Environments .................................................................... 36

1.4.1 SAP HANA Studio ............................................................................ 37

1.4.2 SAP HANA Database Explorer .................................................... 40

1.5 The SQL Console ............................................................................................. 44

1.6 Summary ........................................................................................................... 47

2 Getting Started with SQLScript 49

2.1 SQL versus SQLScript .................................................................................... 49

2.2 Basic Language Elements ........................................................................... 53

2.2.1 Statements ....................................................................................... 53

2.2.2 Whitespace ....................................................................................... 54

2.2.3 Comments ........................................................................................ 54

2.2.4 Literals ................................................................................................ 56

2.2.5 Identifiers .......................................................................................... 58

2.2.6 Access to Local Variables and Parameters ............................. 59

2.2.7 System Variables ............................................................................ 60

2.2.8 Reserved Words .............................................................................. 61

2.2.9 Operators .......................................................................................... 61

2.2.10 Expressions ....................................................................................... 63

Contents

2.2.11 Predicates .......................................................................................... 65

2.2.12 Data Types ......................................................................................... 66

2.2.13 The NULL Value ................................................................................ 67

2.2.14 The DUMMY Table .......................................................................... 69

2.3 Modularization and Logical Containers ............................................... 70

2.3.1 Blocks .................................................................................................. 72

2.3.2 Procedures ......................................................................................... 75

2.3.3 User-Defined Functions ................................................................ 83

2.3.4 User-Defined Libraries ................................................................... 87

2.4 Sample Program ............................................................................................. 89

2.4.1 Requirements ................................................................................... 89

2.4.2 Requirements Analysis .................................................................. 90

2.4.3 Implementation .............................................................................. 91

2.4.4 Testing the Implementation ....................................................... 97

2.5 Summary ............................................................................................................ 100

3 Declarative Programming in SQLScript 101

3.1 Table Variables ................................................................................................ 102

3.1.1 Declaring Table Variables ............................................................. 102

3.1.2 Using Table Variables .................................................................... 103

3.2 SELECT Statements ........................................................................................ 104

3.2.1 SELECT Clauses ................................................................................. 105

3.2.2 Field List of SELECT Clauses ......................................................... 105

3.2.3 FROM Clauses ................................................................................... 119

3.2.4 Joins ..................................................................................................... 122

3.2.5 WHERE Conditions ......................................................................... 130

3.2.6 WITH Clauses ................................................................................... 137

3.2.7 GROUP BY Clauses .......................................................................... 139

3.2.8 HAVING Clauses .............................................................................. 141

3.2.9 ORDER BY Clauses ........................................................................... 142

3.2.10 Set Theory .......................................................................................... 143

3.2.11 Subqueries ......................................................................................... 145

3.2.12 Alias Names ...................................................................................... 146

3.3 Other Operators .............................................................................................. 148

3.3.1 Calculation Engine Plan Operators ........................................... 148

3.3.2 MAP_MERGE Operator .................................................................. 149

3.3.3 MAP_REDUCE Operator ................................................................ 150

3.4 Summary ............................................................................................................ 151

Contents

4 Data Types and Their Processing 153

4.1 Character Strings ........................................................................................... 153

4.1.1 Data Types for Character Strings .............................................. 154

4.1.2 Conversions ...................................................................................... 157

4.1.3 Character String Functions ......................................................... 157

4.1.4 SQLSCRIPT_STRING Library ......................................................... 171

4.2 Date and Time ................................................................................................. 176

4.2.1 Date Information ............................................................................ 176

4.2.2 Time Information ........................................................................... 181

4.2.3 Combined Time and Date Information ................................... 182

4.2.4 Processing Time and Date Values ............................................. 182

4.2.5 Examples of Processing Time Values ....................................... 187

4.3 Numerical Data ............................................................................................... 189

4.3.1 Basic Arithmetic Operations ....................................................... 191

4.3.2 Square Roots and Exponents ...................................................... 191

4.3.3 Logarithms ........................................................................................ 192

4.3.4 Rounding or Trimming ................................................................. 192

4.3.5 Trigonometry ................................................................................... 194

4.3.6 Random Numbers .......................................................................... 194

4.3.7 Sign ...................................................................................................... 194

4.3.8 Quantities and Amounts ............................................................. 195

4.4 Binary Data Types .......................................................................................... 200

4.4.1 Conversion between Binary Data, Hexadecimal Data,

and Character Strings ................................................................... 201

4.4.2 Bits and Bytes .................................................................................. 202

4.5 Conversions between Data Types .......................................................... 204

4.6 Summary ........................................................................................................... 205

5 Write Access to the Database 207

5.1 INSERT ................................................................................................................. 208

5.1.1 Individual Data Records ............................................................... 208

5.1.2 Inserting Multiple Records Simultaneously .......................... 209

5.2 UPDATE .............................................................................................................. 211

5.2.1 Simple UPDATE Statement ......................................................... 211

5.2.2 UPDATE Statement with Reference to Other Tables .......... 212

Contents

5.3 UPSERT or REPLACE ........................................................................................ 213

5.3.1 Inserting or Updating Individual Data Records .................... 213

5.3.2 Inserting or Updating Multiple Data Records ....................... 214

5.4 MERGE INTO ..................................................................................................... 214

5.5 DELETE ................................................................................................................. 217

5.6 TRUNCATE TABLE ............................................................................................ 217

5.7 Summary ............................................................................................................ 217

6 Imperative Programming 219

6.1 Variables ............................................................................................................ 219

6.1.1 Local Scalar Variables .................................................................... 219

6.1.2 Local Table Variables ..................................................................... 224

6.1.3 Session Variables ............................................................................ 234

6.1.4 Temporary Tables ........................................................................... 235

6.2 Flow Control Using IF and ELSE ................................................................ 236

6.3 Loops .................................................................................................................... 239

6.3.1 FOR Loop ............................................................................................ 239

6.3.2 WHILE Loop ....................................................................................... 240

6.3.3 Controlling Loop Passes ................................................................ 241

6.3.4 Exercise: Greatest Common Divisor ......................................... 242

6.4 Cursors ................................................................................................................ 243

6.4.1 FOR Loop via a Cursor .................................................................... 243

6.4.2 Open, Read, and Close Explicitly ................................................ 244

6.4.3 Updateable Cursors ........................................................................ 246

6.5 Arrays ................................................................................................................... 246

6.5.1 Generating an Array ....................................................................... 247

6.5.2 Accessing the Array ........................................................................ 247

6.5.3 Arrays as Local Variables .............................................................. 248

6.5.4 Splitting and Concatenating Arrays ......................................... 249

6.5.5 Arrays and Table Columns ........................................................... 250

6.5.6 Bubble Sort Exercise ....................................................................... 251

6.6 Transaction Control ....................................................................................... 253

6.6.1 Transactions ..................................................................................... 253

6.6.2 Autonomous Transactions .......................................................... 254

6.7 Executing Dynamic SQL ............................................................................... 255

6.7.1 Parameters of Dynamic SQL ........................................................ 257

6.7.2 Input Parameters ............................................................................ 258

Contents

6.8 Error Handling ................................................................................................. 260

6.8.1 What Are Exceptions? ................................................................... 261

6.8.2 Triggering Exceptions ................................................................... 262

6.8.3 Catching Exceptions ...................................................................... 262

6.9 Summary ........................................................................................................... 266

7 Creating, Deleting, and Editing

Database Objects 267

7.1 Tables .................................................................................................................. 268

7.1.1 Creating Database Tables ............................................................ 268

7.1.2 Changing Database Tables ......................................................... 272

7.1.3 Deleting Database Tables ............................................................ 273

7.2 Table Types ....................................................................................................... 273

7.3 Views ................................................................................................................... 274

7.4 Sequences ......................................................................................................... 276

7.4.1 Increment .......................................................................................... 277

7.4.2 Limits .................................................................................................. 277

7.4.3 Behavior When Reaching the Limit .......................................... 277

7.4.4 Resetting the Sequence ................................................................ 278

7.4.5 Changing and Deleting a Sequence ......................................... 278

7.5 Triggers .............................................................................................................. 278

7.5.1 Parameters ....................................................................................... 280

7.5.2 Per Row or Per Statement ............................................................ 281

7.6 Summary ........................................................................................................... 281

8 SQLScript in ABAP Programs 283

8.1 AMDP Procedures .......................................................................................... 283

8.1.1 Introduction to AMDP ................................................................... 284

8.1.2 Creating AMDP Procedures ......................................................... 287

8.1.3 Generated Objects of an AMDP Method ................................ 290

8.1.4 Lifecycle of the Generated Objects ........................................... 294

8.1.5 Two-Track Development .............................................................. 294

8.1.6 Using AMDP Procedures in Other AMDP Procedures ......... 297

8.2 CDS Table Functions ..................................................................................... 300

8.2.1 Creating a CDS Table Function .................................................. 300

Contents

8.2.2 Generated Objects of a CDS Table Function .......................... 305

8.2.3 Implicit Client Handling of CDS Table Functions ................. 306

8.3 AMDP Functions for AMDP Methods .................................................... 307

8.3.1 AMDP Table Functions .................................................................. 307

8.3.2 Scalar AMDP Functions ................................................................. 309

8.4 Alternatives to AMDP for Calling SQLScript Code from

ABAP Programs

............................................................................................... 310

8.5 Recommendations ......................................................................................... 311

8.6 Summary ............................................................................................................ 312

9 SQLScript in SAP Business Warehouse 313

9.1 Executing the Data Transfer Process in ABAP vs. SAP HANA ..... 314

9.2 Transformation Routines as AMDP ........................................................ 318

9.2.1 Creating Transformation Routines in Eclipse ........................ 318

9.2.2 Creating Transformation Routines in SAP GUI ..................... 319

9.3 Successive Transformations and Mixed Execution ......................... 320

9.4 Generated AMDP Classes ........................................................................... 321

9.4.1 Signature of AMDP Methods ...................................................... 323

9.4.2 Assigning the Output Tables ...................................................... 325

9.4.3 Access to Data from Other Data Models ................................ 325

9.5 Individual Routines ....................................................................................... 328

9.5.1 Start Routines ................................................................................... 329

9.5.2 End Routines ..................................................................................... 329

9.5.3 Expert Routines ............................................................................... 330

9.5.4 Field Routines ................................................................................... 332

9.6 Error Processing and Error Stack .............................................................. 333

9.6.1 Processing Flow in the Data Transfer Process ....................... 335

9.6.2 Example: Recognizing Incorrect Data in Table OUTTAB .... 336

9.6.3 Example: Finding Invalid Field Contents with Regular

Expressions ....................................................................................... 337

9.7 Summary ............................................................................................................ 337

Contents

10 Clean SQLScript Code 339

10.1 Code Readability ............................................................................................ 339

10.1.1 Formatting the Code ..................................................................... 340

10.1.2 Mnemonic Names .......................................................................... 341

10.1.3 Granularity of Procedures and Functions ............................... 342

10.1.4 Comments ........................................................................................ 345

10.1.5 Decomposing Complex Queries ................................................ 347

10.1.6 Readable SQLScript Statements ................................................ 351

10.2 Performance Recommendations ............................................................ 352

10.2.1 Reducing Data Volumes ............................................................... 353

10.2.2 Avoid Switching between Row and Column Engines ........ 353

10.2.3 Declarative Queries ........................................................................ 353

10.2.4 Scalar Functions .............................................................................. 353

10.3 Summary ........................................................................................................... 354

11 Tests, Errors, and Performance Analysis 355

11.1 Testing SQLScript Code ............................................................................... 356

11.1.1 SQL Console ...................................................................................... 356

11.1.2 Testing ABAP-Managed Database Procedure Methods .... 358

11.1.3 SQLSCRIPT_LOGGING Library ..................................................... 359

11.1.4 End-User Test Framework in SQLScript ................................... 361

11.2 Debugger for SQLScript ............................................................................... 365

11.2.1 SQLScript Debugger in SAP HANA Studio ............................... 366

11.2.2 ABAP-Managed Database Procedure Debugger in

the ABAP Development Tools ..................................................... 369

11.2.3 Debugging in the SAP HANA Database Explorer ................. 372

11.3 Performance Analysis .................................................................................. 374

11.3.1 Runtime Measurement ................................................................ 374

11.3.2 Execution Plan ................................................................................. 375

11.3.3 Plan Visualizer ................................................................................. 377

11.3.4 SQL Analyzer of the SAP HANA Database Explorer ............. 384

11.3.5 SQLScript Code Analyzer .............................................................. 386

11.4 Summary ........................................................................................................... 390

Contents

Appendices 391

A Data Model for Task Management ........................................................ 393

B List of Abbreviations ..................................................................................... 397

CThe Author........................................................................................................ 399

Index ................................................................................................................................... 401

401

Index

ABAP ................................................................. 67

date format ............................................ 180

unit test .................................................... 297

ABAP Database Connectivity

(ABDC) ...................................................... 311

ABAP development tools ................... 36, 38

ABAP Dictionary ........................................ 300

ABAP_ALPHANUM() ................................ 157

ABAP_LOWER() .......................................... 159

ABAP_UPPER() ........................................... 159

ABAP-managed database procedure

(AMDP) ....................... 21, 55, 70, 284, 296

ABAP .......................................................... 296

classes ....................................................... 321

debugger ......................................... 369, 372

field routine ............................................ 332

framework .............................................. 285

function ................................. 284, 300, 307

implementing a procedure ............... 289

in ABDP procedure .............................. 297

method ............................................ 287, 297

objects ....................................................... 290

procedure ....................................... 283–284

PROCEDURE method .......................... 323

recommendations ............................... 311

retroactive implementation ............ 296

routines .................................................... 147

testing the method .............................. 358

tools ........................................................... 317

ABS() ............................................................... 194

ACOS() ............................................................ 194

ADD_*() ......................................................... 183

ADD_MONTHS_LAST() ........................... 184

ADD_MONTHS() ........................................ 184

ADD_WORKDAYS() .................................. 184

Advanced DataStore objects

(aDSOs) ............................................ 313, 326

Aggregate expression .............................. 111

Aggregate functions ....................... 111, 113

Alias ................................................................ 106

Alias name .......................................... 146, 212

Alignment .................................................... 341

Alpha conversion ...................................... 157

ALPHANUM ................................................ 155

ALTER TABLE ............................................... 272

Amount ......................................................... 195

Anonymous blocks ........................... 70, 102

ANSI SQL standard ....................................... 50

APPLY_FILTER ............................................. 259

Arithmetic operators .................................. 61

Array ............................................................... 246

access ........................................................ 247

as local variable .................................... 248

concatenate and split ......................... 249

generate ................................................... 247

table and .................................................. 250

ARRAY_AGG() ................................... 250–251

AS BEGIN ................................................... 80, 84

ASCII ...................................................... 154, 170

character set .............................................. 53

ASIN() ............................................................. 194

Assigning an output table ..................... 325

Asterisk .......................................................... 107

ATAN() ........................................................... 194

ATAN2() ......................................................... 194

Authorization control ................................ 33

Automatic number assignment ......... 270

Autonomous transaction ...................... 254

AVG ................................................................. 113

Basic arithmetic operations .................. 191

BEGIN

AUTONOMOUS TRANSACTION ...... 254

block ............................................................. 72

Best practices .............................................. 339

BETWEEN ...................................................... 132

Binary data ................................................... 202

Binary data type ......................................... 200

Binary floating-point number ............. 190

BINTOHEX() ................................................. 201

BINTONHEX() .............................................. 201

BINTOSTR() .................................................. 201

BITCOUNT() ................................................. 204

BITSET() ......................................................... 202

BITUNSET() ................................................... 202

Blank characters ........................................... 54

Blank line ...................................................... 340

BLOB ............................................................... 200

Blocks ................................................................ 72

anonymous ................................................ 74

comment ..................................................... 55

BREAK statement ...................................... 241

402

Index

Breakpoint ................................................... 370

Bring Your Own Language (BYOL) ......... 26

Bubble sort algorithm ............................. 251

Calculation engine .................................... 376

Calculation engine functions

(CE functions) ................................ 148, 387

Calendar week ............................................ 186

CALL ................................................................... 81

CALL DATABASE PROCEDURE ............. 311

CARDINALITY() ........................................... 246

CASCADE ....................................................... 273

CASE expressions ...................................... 108

searched ................................................... 109

simple ........................................................ 108

CAST() ............................................................. 204

CEIL() .............................................................. 193

CHANGING table parameters ............... 298

CHAR() ........................................................... 170

Character string ......................................... 153

data type .................................................. 154

function .................................................... 157

literals .......................................................... 57

search within .......................................... 165

Client capability ............................................ 31

Client concept ............................................. 197

Client handling .......................................... 306

CLOB ............................................................... 157

COALESCE function ..................................... 69

Code Analyzer ............................................. 386

Code Inspector ........................................... 389

Code optimization .................................... 354

Code pushdown ............................................ 52

Code-to-data paradigm .............................. 51

Colon ................................................................. 60

Column alias ............................................... 147

Column definition .................................... 268

Column engine .......................................... 376

Column list .................................................. 352

Column list UPSERT ................................. 213

Column name ............................................. 106

Column sequence ........................... 208–209

Column store ............................. 22, 270, 353

Column update .......................................... 215

Column-based storage ............................... 23

Commented out ........................................ 242

Comments ............................................ 54, 345

Commercial rounding ............................. 192

COMMIT ........................................................ 253

CompositeProvider .................................. 313

Compression .................................................. 23

CONCAT() ...................................................... 158

array .......................................................... 249

Concatenation ............................................ 158

Constants ........................................................ 88

Constraint ..................................................... 269

CONTAINS .................................................... 135

Content folder .............................................. 35

CONTINUE .................................................... 241

Control character ....................................... 337

Conversion

data type .................................................. 204

explicit ....................................................... 204

implicit ...................................................... 204

permissible .............................................. 204

CONVERT_CURRENCY() .......................... 198

CONVERT_UNIT() ...................................... 195

Core data services (CDS) ........................... 21

client .......................................................... 306

objects of a table function ................ 305

table function ............ 284, 300–301, 303

view ............................................................ 300

Correlation name ............................ 107, 352

COS() ............................................................... 194

COSH() ............................................................ 194

Cosine ............................................................. 194

COT() ............................................................... 194

COUNT ................................................. 112–113

CPU load .......................................................... 24

CREATE FUNCTION ..................................... 83

CREATE TABLE ............................................ 268

CREATE TABLE AS ...................................... 271

CREATE TABLE LIKE .................................. 271

Critical path ................................................. 380

Cross-join ...................................................... 123

Currency conversion ................................ 198

CURRENT_DATE ......................................... 183

CURRENT_LINE_NUMBER ....................... 61

CURRENT_OBJECT_NAME ....................... 60

CURRENT_OBJECT_SCHEMA .................. 60

CURRENT_TIME ......................................... 183

CURRENT_TIMESTAMP ........................... 183

CURRENT_UTCDATE ................................ 183

CURRENT_UTCTIME ................................. 183

CURRENT_UTCTIMESTAMP .................. 183

CYCLE parameter ....................................... 277

Data control language (DCL) ................... 50

Data definition language (DDL) .... 50, 267

Data flow graph .......................................... 101

Data manipulation language (DML) .... 50

403

Index

Data model

data ........................................................... 394

example ................................................... 393

installation ............................................. 395

table ........................................................... 393

task management ................................ 393

Data preview ............................................... 358

Data provisioning server .......................... 30

Data transfer intermediate storage ... 333

Data transfer process ..................... 315, 320

execute ..................................................... 314

processing flow ..................................... 335

Data types ............................................. 66, 153

composite ................................................... 66

convert ............................................ 157, 204

primitive ..................................................... 66

scalar ............................................................ 66

Data volume ............................................... 353

Database

catalog ............................................... 34, 273

different systems .................................. 295

objects ................................................ 32, 267

schema ........................................................ 32

search ........................................................ 135

status ........................................................ 253

write access ............................................ 207

DATE ............................................................... 176

Date formats ............................................... 177

DATS ............................................................... 180

DATS fields .................................................. 180

DAYNAME() ................................................. 187

DAYOFYEAR() .............................................. 187

Deadlock ....................................................... 255

Debug mode ................................................ 372

Debugging ...................... 356, 365–366, 373

data preview .......................................... 358

external .................................................... 368

in SAP HANA Studio ............................ 368

procedure ................................................ 367

Decimal floating-point number ......... 190

Declarative programming ..................... 101

DECLARE

CONDITION ............................................ 261

CURSOR .................................................... 243

Decomposition .......................................... 172

DEFAULT DECLARE .................................. 220

DEFAULT parameter ................................... 77

DEFAULT SCHEMA ...................................... 80

DELETE ................................................. 217, 229

Delta merge operation ............................... 25

Delta storage .................................................. 25

DETERMINISTIC ............................................ 84

Development environments ........... 36–37

Deviating fiscal year ................................. 187

Dictionary compression ............................ 23

DISTINCT ...................................................... 105

DO BEGIN ........................................................ 74

Don’t Repeat Yourself (DRY)

principle ...................................................... 71

DROP TABLE ................................................ 273

DUMMY table ................................................ 69

Dynamic SQL ..................................... 255, 387

Eclipse ............................................ 37, 318, 380

debugger .................................................. 366

installation ................................................. 38

Empty date ................................................... 177

EMPTY parameter ........................................ 78

Empty result ................................................ 146

END .................................................................... 80

anonymous block .................................... 74

block ............................................................. 72

functions ..................................................... 84

End routine .................................................. 329

End-of-line comment ................................. 54

End-user test framework ........................ 361

Engine ............................................................ 375

Equivalent join ........................................... 125

Error code ..................................................... 261

Error handling ................................... 260, 262

Error processing ........................................ 333

Error stack ........................................... 333, 335

ERRORTAB .................................................... 325

Error-tolerant search .................................. 28

Escape expression ..................................... 131

ESCAPE_DOUBLE_QUOTES ................... 259

ESCAPE_SINGLE_QUOTES ..................... 259

Euclidean algorithm ................................ 242

Evaluation sequence of operators ......... 62

EXCEPT .......................................................... 145

Exceptions ................................................... 261

forwarding .............................................. 265

in procedures .......................................... 264

trigger ....................................................... 262

EXEC ................................................................ 256

EXEC SQL ....................................................... 311

EXECUTE IMMEDIATE ............................. 257

Execution plan .............. 375, 378, 380, 386

EXISTS ............................................................ 133

EXISTS predicate ........................................ 236

Expert routine ............................................ 330

EXPLAIN PLAN ............................................ 376

Explanatory comments .......................... 346

Exponent ...................................................... 191

404

Index

Expressions .......................................... 63, 106

context ......................................................... 64

Extended store server ................................ 30

EXTRACT() .................................................... 185

FETCH INTO ................................................. 244

Field list ............................................... 105–106

Field routine ................................................ 332

First in, first out (FIFO) ............................ 143

Fiscal year ..................................................... 187

Fixed-point number ................................. 189

Floating-point literal ................................... 57

Floating-point number ........................... 190

FLOOR() ......................................................... 193

Flow control ................................................ 236

FOR loop ............................................... 239, 243

FORMAT function ..................................... 174

Formatting ................................................... 340

Formatting strings .................................... 173

FROM clause ................................................ 119

UPDATE .................................................... 212

Full outer join ............................................. 127

Function call in the field list ................. 111

Functions ......................................................... 70

Fuzzy search ................................................ 136

Generation namespaces ............................ 59

Generic programming ............................ 255

Geodata ............................................................ 27

Global temporary table (GTT) .............. 235

Granularity .................................................. 342

Graph engine ................................................. 28

Graph processing ......................................... 28

Greatest common divisor ...................... 242

GROUP BY clause .............................. 112, 139

Grouping sets ............................................. 140

Hamming distance ................................... 169

HAMMING_DISTANCE() ......................... 169

HAVING clause ........................................... 141

Header comments .................................... 345

Help procedures ........................................ 263

Hexadecimal representation ............... 202

HEXTOBIN() ................................................. 201

Hungarian notation ................................. 342

Identifiers ....................................................... 58

IF ....................................................................... 236

IF statement ................................................. 236

IF_AMDP_MARKER_HDB ...................... 287

Imperative programming ...................... 219

Imperative statements ............................ 219

Implicit client handling .......................... 306

Implicit date conversion ........................ 177

IN ...................................................................... 132

Inbound projection .................................. 347

Inclusive time ............................................. 380

Increment ..................................................... 277

Index server ................................................... 29

Index-based access ......................... 224–225

Infinite loop ................................................. 241

Initial value, variable ................................ 220

In-memory ..................................................... 23

Inner join ................................... 114, 124, 144

Input parameter, dynamic SQL ............ 258

Input parameters ....................................... 275

INSERT ........................................... 99, 208, 226

Insert-only approach ................................. 24

Integer ............................................................ 189

Integer expression .................................... 225

INTERSECT .................................................... 144

Intersection .................................................. 144

Interval .......................................................... 132

INTO clause .................................................. 220

INTO clause EXEC ...................................... 257

IS [NOT] NULL ............................................. 135

IS_EMPTY ...................................................... 231

IS_SQL_INJECTION_SAVE ...................... 259

ISCLOSED ...................................................... 245

ISOWEEK() ..................................................... 186

Join .................................................................. 122

condition .................................................. 124

type ............................................................. 123

Join engine ................................................... 376

LAG .................................................................. 119

LANGUAGE ..................................................... 79

LANGUAGE SQLSCRIPT ............................. 84

LAST_DAY(\u003cdate\>) ...................... 184

Lateral joins ................................................. 128

LCASE() ........................................................... 159

405

Index

LEAD ............................................................... 119

Left outer join ............................................. 126

LEFT() ............................................................. 160

LENGTH() ...................................................... 157

Lexical element ............................................ 49

LIKE ................................................................. 131

LIKE_REGEXPR ........................................... 170

LIMIT .............................................................. 105

Limit ............................................................... 277

Line break .............................................. 54, 340

Linear dimension ......................................... 90

Linguistic search ................................ 28, 135

List of statements ........................................ 74

Literals .................................................... 56, 352

LN() ................................................................. 192

LOB ................................................................. 157

Local table variable ................................... 224

Local temporary table ............................. 235

Local variable ................................................. 59

LOCALTOUTC() ........................................... 187

LOCATE_REGEXPR() ........................ 163, 165

LOCATE() ....................................................... 165

LOG() .............................................................. 192

Logarithm .................................................... 192

Logging ......................................................... 254

Logical container ....................................... 219

Logical operators .......................................... 62

Loop pass ...................................................... 241

Loops ..................................................... 233, 239

LOWER() ........................................................ 159

LPAD() ............................................................ 167

LTRIM() .......................................................... 168

Map merge ................................................... 149

MapReduce method ................................ 150

Material number ....................................... 168

MAX ................................................................ 113

MAXVALUE ................................................. 277

MEMBER OF ....................................... 134, 248

MEMBER_AT() ............................................ 248

MERGE INTO ............................................... 214

MIN ................................................................. 113

MINVALUE ................................................... 277

Mixed execution ....................................... 320

MOD() ............................................................ 191

MONTHNAME() ......................................... 187

Multicontainer database .......................... 31

Multitenant database containers

(MDCs) ......................................................... 31

Name server ................................................... 29

Naming .......................................................... 341

Naming conventions ............................... 342

Native SQL .................................................... 283

NCHAR() ........................................................ 170

NCLOB ............................................................ 157

NDIV0() .......................................................... 191

Nest expressions ....................................... 114

Nested CASE expressions .......................... 96

NEXT_DAY( \u003cdate\>) .................... 184

Node grouping ........................................... 379

Non-equivalent join ................................. 125

Non-permitted characters ..................... 337

NOT EXISTS .................................................. 145

NOT NULL ..................................................... 269

NOT NULL DECLARE ................................. 220

NOTFOUND ................................................. 245

NOW() ............................................................. 183

NULL value ........................... 67–68, 269, 328

in aggregate functions ....................... 114

NULLS FIRST ................................................ 142

NULLS LAST ................................................. 142

Number of rows ......................................... 231

Numerical data ........................................... 189

Numerical literal .......................................... 56

NVARCHAR .................................................. 154

OCCURRENCES_REGEXPR() ......... 163, 166

OData ................................................................ 27

OFFSET ........................................................... 105

OLAP engine ................................................ 376

ON .................................................................... 124

OPEN CURSOR ............................................ 244

Open SQL ............................................... 67, 283

OpenUI5 ........................................................... 27

Operator ........................................................... 61

Operator expressions .............................. 108

Operator list ................................................ 382

OPTIONS READ-ONLY ............................. 289

ORDER BY ............................................ 117, 142

ORDINALITY ................................................ 250

Orphaned AMDP Classes ........................ 319

Outsourcing complexity ........................ 343

OUTTAB ................................................ 325, 336

Overlapping ................................................. 223

406

Index

Parameterization ......................................... 90

Parameters ................................................... 120

dynamic SQL .......................................... 257

example ....................................................... 90

generic ......................................................... 78

named .......................................................... 81

trigger ....................................................... 280

view ............................................................ 275

Parentheses .......................................... 62, 351

PARTITION BY ............................................. 117

Performance ................................................ 352

Performance analysis ..................... 355, 374

Performance trace .................................... 383

Personal schema ........................................... 33

Phonetic code ............................................. 169

Placeholders ................................................ 131

Plan operator .............................................. 375

Plan Visualizer (PlanViz) ..... 350, 356, 377

call .............................................................. 377

views .......................................................... 380

POWER() ........................................................ 191

Pragmas ........................................................ 362

Predicates .............................................. 65, 236

Predictive analytics ..................................... 28

Pretty printer .............................................. 341

PRIMARY KEY .............................................. 269

Primary key .............................. 207, 214, 269

Procedures ............................................... 70, 75

call ................................................................. 80

create ............................................................ 75

parameter list ........................................... 76

properties .................................................... 79

Public schema ................................................ 33

Public synonym ............................................ 35

Quantity ........................................................ 195

Quantity conversion ....................... 195, 197

QUARTER() ................................................... 187

Queries .......................................................... 104

insert from ............................................... 209

Query decomposition ................... 347–348

Query result, dynamic SQL ................... 257

Question mark .............................................. 82

Quotation marks .......................................... 58

RAND_SECURE() ........................................ 194

RAND() ........................................................... 194

Random numbers ..................................... 194

Readability ................................ 339, 343, 351

READS SQL DATA ......................................... 80

RECORD ......................................................... 323

RECORD_COUNT ....................................... 231

Recursive calls ............................................... 82

Redundant comments ............................ 346

Regular expressions ....................... 161, 337

groups ....................................................... 163

Relational operators ................................... 62

REPLACE ........................................................ 213

REPLACE_REGEXPR() ..................... 163, 166

REPLACE() ..................................................... 166

Repository ...................................................... 35

Request .......................................................... 315

Requirements analysis .............................. 90

Reserved words ............................................ 61

RESIGNAL ...................................................... 265

RESTART ........................................................ 278

RESTRICT ....................................................... 273

Return values ................................................ 95

RETURNS ......................................................... 84

Reuse ................................................................ 71

Right outer join .......................................... 126

RIGHT() .......................................................... 160

ROLLBACK .......................................... 217, 253

ROUND() ........................................................ 192

Rounding ...................................................... 192

Row engine ................................................... 376

Row store ..................................... 23, 270, 353

ROWCOUNT .......................................... 60, 245

ROWS clause ................................................ 118

RPAD() ............................................................ 167

RTRIM() .......................................................... 168

Runtime measurement .......................... 374

Runtime platform ....................................... 26

Sample program .......................................... 89

SAP Adaptive Server Enterprise

(SAP ASE) .................................................... 50

SAP Business Application Studio ... 36, 42

SAP Business Technology Platform

(SAP BTP) .................................................... 42

SAP Business Warehouse (SAP BW) ..... 59,

313, 347

query .......................................................... 383

transformation ...................................... 314

transformation routine ...................... 371

SAP BW modeling tools ............ 36, 38, 319

SAP BW/4HANA ............................... 314, 321

transformation ...................................... 317

407

Index

SAP Fiori apps ................................................ 27

SAP HANA ....................................................... 21

database ..................................................... 22

deployment infrastructure .................. 41

introduction .............................................. 22

performance ........................................... 317

perspectives ............................................... 39

server components ................................. 29

system architecture ................................ 29

system schema ......................................... 33

SAP HANA automated predictive

library .......................................................... 28

SAP HANA database explorer ......... 36, 40,

384, 389

debugger .................................................. 372

user interface ............................................ 43

SAP HANA deployment infrastructure

(HDI)

container .................................................... 27

server ............................................................ 29

SAP HANA smart data integration ........ 30

SAP HANA Studio ................................. 36–37

database connection ............................. 39

debugger .................................................. 366

SAP HANA Web-Based Development

Workbench ................................................ 36

SAP HANA XS .......................................... 26, 29

SAP HANA XSA ....................................... 26, 29

SAP HANA, express edition ..................... 29

SAP HANA, streaming analytics

option .......................................................... 30

SAP Landscape Transformation

schema ........................................................ 34

SAP NetWeaver schema ............................ 33

SAP Web IDE ............................................ 36, 40

SAPUI5 .............................................................. 27

Savepoint ........................................................ 23

Scalar AMDP ................................................ 309

Scalar parameters ..................................... 324

Scalar queries .......................................... 64, 66

Scalar session variables .......................... 234

Scalar user-defined functions ................. 86

Search ............................................................... 28

SEARCH operator ...................................... 232

SEARCH table operator ........................... 230

SECONDDATE ............................................. 182

SECURITY MODE functions ..................... 84

SELECT clause ............................................. 105

SELECT INTO ............................................... 221

SELECT query .............................................. 104

SELECT statement ............................ 101, 104

UPSERT ..................................................... 214

Self-join ................................................ 116, 122

Semantic search ............................................ 28

Semicolon ....................................................... 53

Separation of concerns ........................... 295

Sequence .................................... 270, 276, 278

change ...................................................... 278

delete ......................................................... 278

reset ........................................................... 278

Sequence of operators ................................ 62

Sequences ..................................................... 267

SERIES_GENERATE_DATE ...................... 150

Session variables ....................................... 234

SET clause ..................................................... 211

Set operations ............................................. 143

SHINE demo ................................................... 59

SHORTTEXT ................................................. 156

Sign ................................................................. 194

SIGN() ............................................................. 194

SIGNAL ........................................................... 262

Simple notation ............................................ 58

SIN() ................................................................ 194

Single container database ......................... 31

SINH() ............................................................. 194

Sinus ............................................................... 194

Size category .................................................. 93

Sorted table variables .............................. 232

SOUNDEX() .................................................. 169

Source code ................................................. 389

Special characters ......................................... 58

Special notation ............................................ 58

Splitting strings ......................................... 171

SQL ..................................................................... 49

security ........................................................ 80

SQL Analyzer ............................................... 384

SQL console ................................... 43–44, 356

getting started .......................................... 46

user interface ............................................ 45

SQL Editor ................................................. 45, 47

SQL functions ............................................. 102

SQL injection ............................................... 259

SQL__PROCEDURE__SOURCE__

RECORD .................................................... 324

SQL_ERROR_CODE ............................ 60, 262

SQL_ERROR_MESSAGE .................... 60, 262

SQLSCRIPT_LOGGING library .............. 359

SQLSCRIPT_STRING library ................... 171

SQRT() ............................................................ 191

Square root .................................................. 191

Start routine ................................................ 329

Statement ........................................................ 53

Streaming cluster ......................................... 30

408

Index

String

operators .................................................... 62

padding .................................................... 167

replace variable ..................................... 166

similarity .................................................. 169

trimming .................................................. 167

STRING_AGG ............................................... 113

Structure comments ................................ 346

Stub procedure ........................................... 292

SUBARRAY() ................................................. 249

Subquery ................................... 116, 145, 236

correlated ................................................ 115

in field lists .............................................. 114

SUBSTR_AFTER() ........................................ 160

SUBSTR_BEFORE() ..................................... 160

SUBSTR_REGEXPR() ......................... 161, 163

SUBSTRING() ............................................... 160

Subtracting sets ......................................... 145

Successive transformations .................. 320

SUM ...................................................... 112–113

System database ........................................... 31

System variable ............................................. 60

Table ............................................................... 268

alias ............................................................ 147

change ...................................................... 272

convert to string ................................... 174

copy ............................................................ 271

create with SQL ..................................... 271

definition ................................................. 268

delete ......................................................... 273

expressions ............................................. 119

global temporary .................................. 293

local variable .......................................... 224

parameter ......................................... 66, 292

tables used .............................................. 383

temporary ............................................... 270

type ................................................... 269, 273

Table variables ........................... 66, 102, 210

declare ....................................................... 102

use .............................................................. 103

Tabs .................................................................... 54

TAN() ............................................................... 194

TANH() ........................................................... 194

Task management .................................... 393

Temporary table ........................................ 235

Tenant ............................................................... 30

Tenant database ............................................ 31

Test cases ............................................... 98, 361

Test data ........................................................... 98

Test library ................................................... 364

modularization ..................................... 363

Test procedures .......................................... 363

Test-driven development ....................... 356

Testing ................................... 94, 97, 355–356

TEXT ................................................................ 157

Text mining ................................................... 28

TIME ...................................................... 176, 181

Time information ...................................... 181

Time zone ..................................................... 187

Timeline ........................................................ 382

TIMESTAMP ................................................. 182

TO_DATE() function ................................. 178

TO_NVARCHAR ................................ 157, 178

TO_TIME() ..................................................... 181

TO_VARCHAR ............................................. 178

TO_VARCHAR() ................................ 157, 181

TOP .................................................................. 105

Transaction context ................................. 217

Transaction control .................................. 253

Transaction log ............................................. 23

Transaction RSA1 ...................................... 319

Transformation .......................................... 314

on SAP HANA ......................................... 316

routine ............................................. 318–319

Transport ...................................................... 322

Triggers ................................................ 267, 278

limit reproduction ................................ 280

per row ...................................................... 281

per statement ......................................... 281

Trigonometry .............................................. 194

TRIM_ARRAY() ............................................ 249

TRIM() ............................................................. 168

Troubleshooting ........................................ 355

TRUNCATE TABLE ...................................... 217

UCASE() .......................................................... 159

UDF_SORT_2 ............................................... 238

UDF_SORT_3 ............................................... 238

UMINUS() ...................................................... 194

Unicode ......................................................... 154

UNICODE() .................................................... 170

Uniform terms ............................................ 342

UNION ............................................................ 143

UNIQUE ......................................................... 269

UNNEST() ...................................................... 250

UPDATE ......................................................... 211

reference to other tables .................... 212

table operator ........................................ 228

UPPER() .......................................................... 159

UPSERT .......................................................... 213

409

Index

User-defined functions ............ 43, 83, 344

call and use ................................................ 85

create ........................................................... 83

implementation ....................................... 91

properties ................................................... 83

sample application ................................. 89

sample program flow ............................ 91

scalar ......................................................... 111

User-defined libraries ................................ 87

User-defined table types ........................ 273

USING ............................................................ 289

view ............................................................ 292

WADP routine ........................................ 325

UTC ................................................................. 187

UTCTOLOCAL() ........................................... 187

Value

constant ................................................... 111

VARBINARY ................................................. 200

VARCHAR ..................................................... 154

Variables ....................................................... 219

local scalar .............................................. 219

scalar ......................................................... 111

unnecessary ............................................ 387

visibility .................................................... 223

VERTICAL_UNION .................................... 332

View ................................................................ 274

Virtual tables ............................................... 274

Visibility ........................................................ 223

Watchpoint .................................................. 373

WEEK() ........................................................... 186

WEEKDAY() .................................................. 186

WHEN MATCHED ...................................... 216

WHERE clause ............................................. 127

DELETE ...................................................... 217

UPSERT ..................................................... 214

WHERE condition ...................................... 130

comparison ............................................. 130

with multiple values ............................ 130

WHILE Loop ................................................. 240

Whitespace ..................................................... 54

Window functions ................. 115–116, 118

WITH clause ....................................... 137–138

WITH ENCRYPTION ..................................... 80

WITH ORDINALITY ................................... 250

WITH PRIMARY KEY ................................. 213

First-hand knowledge.

We hope you have enjoyed this reading sample. You may recommend

or pass it on to others, but only in its entirety, including all pages. This

reading sample and all its parts are protected by copyright law. All usa-

ge and exploitation rights are reserved by the author and the publisher.

Jörg Brandeis is the managing director of Brandeis Consulting

GmbH in Mannheim, which offers training and consulting for

SAP BW/4HANA, SAP HANA and SQLScript. Until mid-2015, Mr.

Brandeis worked as head of development at zetVisions AG in

Heidelberg, where he was responsible for the development and

architecture of the SAP NetWeaver-based products zetVisions CIM and SPoT.

In this role, he worked extensively with agile development methods and clean

code.

Jörg Brandeis

SQLScript for SAP HANA

387 pages, 2nd, updated and revised edition 2021, $69.95

ISBN 978-1-4932-2139-4