SQLScript for SAP HANA

Browse the Book

This chapter will teach you how to create database objects direc-

tly using SQL. The SQL statements needed for this topic use data

definition language (DDL), as will be described in this chapter. In

addition, it will introduce you to a few other useful database ob-

jects, such as sequences and triggers.

Jörg Brandeis

SQLScript for SAP HANA

435 Pages, 2019, $79.95

ISBN 978-1-4932-1822-6

www.sap-press.com/4862

First-hand knowledge.

“Creating, Deleting, and Editing

Database Objects”

Table of Contents

Index

The Author

283

Chapter 7

Creating, Deleting, and

Editing Database Objects

Not only can you write data to existing database tables and read this data

from them again with SQL, you can also create tables and other database

objects directly. In this chapter, we’ll show you how to manage database

objects like tables, table types, views, and more.

In most cases, you’ll use the modeling tools found in your development

environment to create database objects, such as tables or views. In this

chapter, you’ll learn how to create database objects directly using SQL. The

SQL statements needed for this topic are referred to as data definition lan-

guage (DDL). In addition, we’ll introduce you to a few other useful database

objects, such as sequences and triggers.

CREATE, ALTER,

and DROP

Back in Chapter 2, you learned how to create procedures and functions. In

general, you can create, delete, and change other database objects accord-

ing to the same schema:

쐍 CREATE

You always use

CREATE

to generate new database objects:

CREATE <objecttype>

<objectdefinition>;

쐍 ALTER

Changes to the object definition are implemented using the

ALTER

state-

ment:

ALTER <objecttype>

<objectdefinition_new>;

284

7 Creating, Deleting, and Editing Database Objects

쐍 DROP

The

DROP

statement allows you to delete an object:

DROP <objecttype>

<objectname>;

The listings shown in previous chapters contained examples that included

the

CREATE

and

DROP

statements when a table was created for only one listing

and then was deleted afterward.

Object definitions can involve many fine details, which we won’t describe

too deeply in this book. Instead, refer to the reference documentation “SAP

HANA SQL and System Views Reference” at http://bit.ly/1822_701, which

lists all available options for the various system statuses.

7.1 Tables

Let’s start with the most important object type in a database—the database

tables. The current reference documentation (http://bit.ly/1822_702) for the

CREATE

and

ALTER

TABLE

statements alone consists of almost 50 pages with

brief, keyword-like descriptions for all the options available for defining

and altering database tables. In the following sections, we’ll focus on basic

table definition and describe some interesting features from a developer

perspective. Thus, we won’t cover the parameterization for administration,

partitioning, storage behavior, etc., in this book.

7.1.1 Creating Database Tables

CREATE TABLE

In the simplest variant, to create a database table, you only need to define

the columns in the table definition of the

CREATE

TABLE

statement, for exam-

ple, as follows:

CREATE TABLE status (id INT ,

text NVARCHAR(30) );

All other table properties correspond to the default values of the

CREATE

TABLE

statement.

285

7.1 Tables

Column Definition

Columns are defined after the table name in parentheses. All column defi-

nitions are listed one after the other, separated by commas. These defini-

tions usually look like the ones shown in Listing 7.1.

[DEFAULT <defaultvalue>]

[<constraint>]

Listing 7.1 Column Definition

A default value is used when a new record is inserted, but no value is speci-

fied for this column. In addition, you can also define constraints for each

column, as shown in Listing 7.2, such as the following:

쐍 NOT

NULL

The column value must be defined; otherwise, a data record can’t be

inserted.

쐍 UNIQUE

In this column, every value (except

NULL

) may occur only once.

쐍 PRIMARY

KEY

The column is the sole primary key. As a result, this column automati-

cally has the properties

NOT

NULL

and

UNIQUE

CREATE TABLE status (id INT PRIMARY KEY,

sort_nr INT NOT NULL UNIQUE,

text NVARCHAR(30) );

Listing 7.2 Example of Constraints on Individual Columns

Constraints for

multiple columns

If the two constraints

UNIQUE

PRIMARY

KEY

refer to more than one column,

you can also specify the respective constraints after the relevant column

definitions, as shown in Listing 7.3.

286

7 Creating, Deleting, and Editing Database Objects

CREATE TABLE test_unique (a INT,

b INT,

c INT,

UNIQUE(a, b),

UNIQUE(b, c));

Listing 7.3 Example of Multiple UNIQUE Constraints on Multiple Columns

In the example shown in Listing 7.3, the combined values from columns A/

B and B/C must be unique for each row. Note that multiple

NULL

values are

permitted in this context. A composite primary key is similar to a

UNIQUE

constraint, and additionally, the

NOT

NULL

constraint is valid for each col-

umn, as shown in Listing 7.4.

CREATE TABLE test_composite_key (a INT,

bINT,

cINT,

PRIMARY KEY(a, b));

Listing 7.4 Example of a Composite Primary Key

Type of Table

In the context of an SAP HANA database, we basically differentiate between

two types,

ROW

and

COLUMN

, for tables. The type of a database table is deter-

mined optionally during the process of its creation using the following

statement:

CREATE [<type>] TABLE

ROW

The

ROW

value for a type means that the table contents will be stored row by

row in the row store. This type is therefore particularly suitable if only a few

data records need to be read but their full width is relevant.

COLUMN

In contrast, the table type,

COLUMN

stores the data column by column in the

column store. This type may seem absurd at first, but the advantage is that,

for example, with more complex SQL queries, only the columns relevant

for determining the result set will be processed first. Additional columns

can be added later. SAP recommends using

COLUMN

tables whenever you

need to process a large number of data records, of which you only need a

relatively small number of columns.

287

7.1 Tables

When selecting the appropriate storage type, you should also take into

account that read access to

ROW

and

COLUMN

tables are controlled by different

engines. A join using tables of both types forces the engines to change,

which means that the data must be copied once.

Types

The following four types can be used to create temporary tables. Their prop-

erties have already been described in detail in Chapter 6, Section 6.1.4.

쐍 GLOBAL

TEMPORARY

[ROW]

쐍 GLOBAL

TEMPORARY

COLUMN

쐍 LOCAL

TEMPORARY

[ROW]

쐍 LOCAL

TEMPORARY

COLUMN

Default Value for the Table Type

Up to and including SAP HANA 2.0 SPS 02, the default value for the type is

ROW

. With the release of SPS 03, the default value has been set to

COLUMN

. If

you need to reverse this change, you can define the default value yourself

in the configuration file indexserver.ini via the

default_table_type

param-

eter. This parameter was available before SPS 03 and won’t be overwritten

during upgrades.

You can find more information on this topic in the SAP Note 2551355.

Automatic Number Assignment

You can specify that a column be automatically filled with sequential num-

bers, which is similar to using a sequence, which we’ll describe in detail in

Section 7.4.

Automatic number

assignment

The addition

GENERATED

DEFAULT

IDENTITY

at the end of a column defini-

tion causes the assignment of a sequential number if no value for the col-

umn has been specified during the insertion process. Alternatively, you can

use

GENERATED

ALWAYS

IDENTITY

to constantly force the generation of a

sequential number, as shown in Listing 7.5.

CREATE COLUMN TABLE test_identity (

a INT GENERATED BY DEFAULT AS IDENTITY,

b VARCHAR(10));

288

7 Creating, Deleting, and Editing Database Objects

INSERT INTO test_identity (b) VALUES ('One');

INSERT INTO test_identity (b) VALUES ('Two');

INSERT INTO test_identity (a,b) VALUES (3, 'Three');

INSERT INTO test_identity (b) VALUES ('Four');

SELECT * FROM test_identity;

Listing 7.5 Automatic Number Assignment

For the number assignment, you can also use other options such as a start

value or an increment by which an increase should be carried out. These

options are described in more detail in Section 7.4.1, which deals with

sequences.

Copying a Table

CREATE TABLE LIKE

If you want to create a table that should be defined exactly like an existing

table, you can use the following statement:

CREATE TABLE <tablename>

LIKE <originaltable>

[WITH DATA];

The addition

WITH

DATA

ensures that all data from the original table is copied

into the new table. Especially for testing and error analysis, such table cop-

ies can be quite useful.

Creating a Table Based on an SQL Query

CREATE TABLE AS

You can also create database tables with a

SELECT

query, as shown in Listing

7.6. In this context, the columns of the field list are used for the column

definition.

CREATE TABLE tmp_tasks AS (

SELECT a.id,

b.firstname,

b.lastname,

t.team_text

FROM tasks AS a

LEFT OUTER JOIN users AS b

ON a.assignee = b.id

289

7.1 Tables

LEFT OUTER JOIN team_text AS t

ON b.team = t.id

)

WITH DATA;

Listing 7.6 Creating a New Table Based on an SQL Query

As with

CREATE

TABLE

...

, you can use the

WITH

DATA

addition as well.

This addition inserts the query data directly into the new database table,

which can be quite helpful for testing and error analysis. For example, if

you want to save the contents of a table for later comparison, this variant of

the

CREATE

TABLE

statement enables you to store the result as a new table.

7.1.2 Changing Database Tables

ALTER TABLE

When database tables are changed, the individual properties of the existing

definition are changed. For this reason, the

ALTER

statement doesn’t contain

the entire table definition, only the properties that are actually to be

changed. Listing 7.7 contains some examples of changing the most import-

ant table properties.

CREATE ROW TABLE demo_table(

col1 INT,

col2 INT

);

--Adding a column

ALTER TABLE demo_table ADD (col3 VARCHAR(20));

--Change column properties, e.g. set default value:

ALTER TABLE demo_table ALTER (col1 INT DEFAULT 42);

--Add the primary key definition:

ALTER TABLE demo_table

ADD CONSTRAINT pk PRIMARY KEY (col1, col2);

--Changing the type:

ALTER TABLE demo_table COLUMN;

Listing 7.7 Example of Changing the Table Properties

290

7 Creating, Deleting, and Editing Database Objects

In the database catalog, right-click on the relevant table and select Open

Definition from the context menu to view the current table definition.

Now, you can reproduce the changes from the listing step by step. Figure 7.1

shows the state of the table definition once all changes have been made.

Figure 7.1 Definition of the Table from Listing 7.7

7.1.3 Deleting Database Tables

DROP TABLE

When deleting database tables, the details of the definition aren’t neces-

sary. The statement

DROP

TABLE

causes the permanent deletion

of a table including its entire contents.

Proceed with Caution when Deleting

The

DROP

TABLE

statement enables you to delete entire tables including

their contents. No security question will ask, “Are you sure you want to

delete the table and its contents?” to confirm the action.

Thus, this statement is extremely dangerous, especially in combination

with dynamic SQL. You should only execute this statement for production

tables if you are 100% sure deletion won’t cause a major problem.

Before deleting a table, cautious people will use

CREATE

TABLE

...

WITH

DATA

to create a backup copy of the table, including its data.

RESTRICT/CASCADE

The

RESTRICT

addition ensures that a table is deleted only if no dependen-

cies with other objects exist. Alternatively, the

CASCADE

addition allows you

to determine that dependent objects should be deleted as well.

291

7.3 Views

7.2 Table Types

User-defined table types are used exclusively for defining and calling proce-

dures and functions. Correspondingly, in the database catalog, these table

types are represented as subitems for the procedures in most development

environments, as shown in Figure 7.2.

Figure 7.2 Table Types in the SAP HANA Web-Based Development Workbench

Database Catalog

The definition of a table type consists only of the columns that are defined

exactly as in the definition of database tables, as shown in Listing 7.8.

CREATE TYPE my_type AS TABLE(

col1 INT,

col2 VARCHAR(10) );

CREATE PROCEDURE my_procedure( IN it_data my_type )

AS BEGIN

...

Listing 7.8 Example of Defining and Using a Table Type in a Procedure Definition

7.3 Views

A view is a named

SELECT

query that can be accessed by its name. Views are

also sometimes called virtual tables because they do not contain any data

themselves. However, you can use views like a table in a

SELECT

query.

292

7 Creating, Deleting, and Editing Database Objects

Example

Listing 7.9 shows an example: The table

invoice_positions

contains the

individual items of an invoice. The

invoices

view shows the amount per

row and totals the amounts per invoice and currency.

CREATE TABLE invoice_position(

invoice_nr INT,

position INT,

product NVARCHAR(30),

quantity INT,

amount DEC(17,2),

currency VARCHAR(5),

primary key (invoice_nr, position)

);

CREATE VIEW invoices

AS SELECT invoice_nr,

SUM(amount*currency) as amount,

currency

FROM invoice_position

GROUP BY invoice_nr,

currency;

INSERT INTO invoice_position

VALUES (1, 10, 'TVs', 1, 765.23, 'EUR');

INSERT INTO invoice_position

VALUES (1, 20, 'Cable' , 1, 12.99 , 'EUR');

INSERT INTO invoice_position

VALUES (1, 30, 'Batteries', 4, 1.99 , 'EUR');

INSERT INTO invoice_position

VALUES (2, 10, 'Computer mouse', 1, 23.99 , 'EUR');

INSERT INTO invoice_position

VALUES (3, 10, 'Cable' , 2, 12.99 , 'EUR');

INSERT INTO invoice_position

VALUES (3, 20, 'Network switch' , 1, 27.99 , 'USD');

--SELECT query to the table

SELECT invoice_nr,

SUM(amount*quantity) as amount,

currency

FROM invoice_position

293

7.3 Views

GROUP BY invoice_nr,

currency;

--SELECT query on the VIEW

SELECT * FROM invoices;

Listing 7.9 Example of a View as a Stored SELECT Query

When you run this example, you’ll see that the two queries return exactly

the same result. The advantage of using the view, however, is that the logic

in the view can be reused.

In this example, the simplest syntax for creating a view is as follows:

CREATE VIEW <viewname> AS <query>;

Parameters

As of SAP HANA 2.0 SPS 02, views can have input parameters. These input

parameters are defined in a similar manner as functions and procedures

and can then be used in different places within a query, as shown in Listing

7.10.

CREATE VIEW invoices (IN iv_currency VARCHAR(5))

AS SELECT invoice_nr,

SUM(amount*quantity) as amount,

currency

FROM invoice_position

WHERE currency = :iv_currency

GROUP BY invoice_nr,

currency;

Listing 7.10 Defining Parameterized Views

For expressions in the

SELECT

query to refer to the parameters, the parame-

ters are preceded by a colon, much like in procedures or functions.

The parameterization for the querying of views is carried out in the same

way as the parameterization of procedures and functions:

SELECT * FROM invoices( 'USD' );

SELECT * FROM invoices( iv_currency=>'USD' );

294

7 Creating, Deleting, and Editing Database Objects

Parameters are assigned either by their position or sequence, or by the

names of the parameters. Even if all parameters of a view have been

assigned a default value, the call must still be made with parentheses.

7.4 Sequences

A sequence enables you to generate unique, sequential integer numbers

across an entire system. The database ensures that these properties are

maintained, even if several users query numbers over a sequence in several

sessions in parallel. As a result, sequences are suitable for assigning techni-

cal keys or document numbers, for example. Database columns with auto-

matic number assignment (Section 7.1.1) also contain sequences.

ABAP developers are familiar with a similar concept: number ranges. Their

properties largely correspond to those of the sequences.

CREATE SEQUENCE

The statement

CREATE

SEQUENCE

<sequencename>;

generates a simple

sequence that starts at 1 and counts up continuously until its maximum

value of 2

63-1

is reached. The statement

<sequencename>.nextval

always pro-

vides the next value of the sequence until the maximum value is reached,

after which a database error is generated.

The expression

<sequencename>.currval

allows you to query the last deliv-

ered value without counting upward.

If the properties of a simple sequence do not fit, you can change them with

additional parameters, as in the following statement:

CREATE SEQUENCE <sequencename> <parameter>

7.4.1 Increment

Normally, a sequence counts continuously up by 1. The parameter

INCRE-

MENT

<value>

allows you to define a different value. This value can also be

negative if the sequence needs to count down instead of up.

7.4.2 Limits

You can use the following parameters to define the limits within which a

sequence operates:

295

7.4 Sequences

쐍 MINVALUE

쐍 MAXVALUE

쐍 NO

MINVALUE

쐍 NO

MAXVALUE

Depending on the counting direction of the sequence, the value at the first

call of

NEXTVAL

corresponds to the values

MINVALUE

MAXVALUE

7.4.3 Behavior When Reaching the Limit

If the upper or lower limit of a sequence is reached, a database error occurs.

By using the

CYCLE

parameter in this situation, you can avoid this error, and

the sequence will start again at its original start value.

Listing 7.11 shows an example of defining a sequence that counts down

from 10 to 0 and then starts over again. In the following anonymous block,

the sequence is queried a hundred times in a loop, the result is stored in an

array, and the array is converted into a local table. This table is then read out

at the end.

CREATE SEQUENCE countdown INCREMENT BY-1

MAXVALUE 10

MINVALUE 0

CYCLE;

DO BEGIN

DECLARE lv_counter INT;

DECLARE lv_tmp INT;

DECLARE la_array INT ARRAY;

FOR lv_counter IN 1..100 DO

SELECT countdown.nextval

INTO lv_tmp

FROM DUMMY;

la_array[:lv_counter] = lv_tmp;

END FOR;

lt_output = UNNEST(:la_array);

SELECT *

FROM :LT_OUTPUT;

END;

Listing 7.11 Example of Using a Sequence

296

7 Creating, Deleting, and Editing Database Objects

The result shows that the sequence worked as desired. What is remarkable

about this listing is that the sequence was used in a

SELECT

query of the

DUMMY

table. The reason for this is that it is not permitted to use the direct

assignment

lv_tmp

countdown.nextval

. Sequences may only be used in

SELECT

INSERT

, or

UPDATE

statements.

7.4.4 Resetting the Sequence

RESTART WITH

A sequence should normally count continuously. Manual intervention is

not desired at all. However, intervention is possible via the statement

ALTER

SEQUENCE

RESTART

WITH

<startvalue>;

7.4.5 Changing and Deleting a Sequence

As with other database objects, the

ALTER

SEQUENCE

statement allows you to

change individual properties of a sequence.

You can use

DROP

SEQUENCE

to delete a sequence. If you create a new

sequence with the same name, it starts again with its start value.

7.5 Triggers

A trigger is an SQLScript procedure that is automatically executed when-

ever a database table is accessed and modified. In this context, you can

specify the time (

BEFORE

AFTER

) and the type of the event (

INSERT

UPDATE

DELETE

). Listing 7.12 contains an example of triggers that log insert and

update operations to table

invoice_position

(including time, date, and

user) into the table

RP_LOG

Creating triggers

Two different triggers are created so that, in the log in the Action field, a dis-

tinction can be made between insert (

) and update (

CREATE SEQUENCE lognr; --technical key of the log

CREATE TABLE invoice_position(

invoice_nr INT,

position INT,

product NVARCHAR(30),

297

7.5 Triggers

quantity INT,

amount DEC(17,2),

currency VARCHAR(5),

primary key (invoice_nr, position)

);

CREATE TABLE rp_log(

log_nr INT,

invoice_nr INT,

position INT,

users VARCHAR(80),

datum DATE,

zeit TIME,

action VARCHAR(1)

);

--Trigger for inserting

CREATE TRIGGER rp_insert AFTER INSERT

ON invoice_position

REFERENCING NEW ROW ls_new

FOR EACH ROW

BEGIN

INSERT INTO rp_log VALUES( lognr.nextval,

:ls_new.invoice_nr,

:ls_new.position,

current_user,

current_date,

current_time,

'I');

END;

--Trigger for updating

CREATE TRIGGER rp_update AFTER UPDATE

ON invoice_position

REFERENCING NEW ROW ls_new

FOR EACH ROW

BEGIN

INSERT INTO rp_log VALUES( lognr.nextval,

:ls_new.invoice_nr,

:ls_new.position,

298

7 Creating, Deleting, and Editing Database Objects

current_user,

current_date,

current_time,

'U');

END;

INSERT INTO invoice_position

VALUES (1, 10, 'Chocolate', 1, 1.99, 'EUR');

INSERT INTO invoice_position

VALUES (1,20, 'Espresso coffee beans', 1, 13.99, 'EUR');

UPDATE invoice_position

SET amount = '12.99'

WHERE invoice_nr = 1

AND position = 20;

SELECT * FROM rp_log;

Listing 7.12 Example of a Trigger for Automatic Change Logging

Triggers are always executed at the defined times. Triggers cannot be

bypassed during write access. Triggers are therefore suitable for require-

ments such as change logging, access control, or consistency assurance. For

example, a trigger can remove all dependent records when deleting a

record.

Triggers Limit Reproducibility

Triggers can be used to execute a portion of the application logic in an

event-driven manner when accessing a database table. However, other

programmers could find understanding the program sequence difficult

and the code hard to read. Triggers can also trigger other triggers them-

selves, which further reduces the clarity.

Therefore, triggers should only be used sparingly, if at all. For a develop-

ment team, full visibility about all existing triggers can be difficult to

achieve for all team members.

299

7.5 Triggers

7.5.1 Parameters

Within a trigger, the data of the SQL statement can be accessed using two

values,

OLD

and

NEW

, which can be assigned to a variable in the trigger defini-

tion.

OLD and NEW

The value

OLD

corresponds to the old data in the database that is affected by

the statement. This value is only available with

DELETE

and

UPDATE

. The value

NEW

contains the new data and can therefore only be used with

INSERT

and

UPDATE

CREATE TRIGGER rp_update AFTER UPDATE

ON invoice_position

REFERENCING NEW ROW ls_new

OLD ROW ls_old

FOREACHROW

...

Listing 7.13 Assigning OLD and NEW to Local Variables

7.5.2 Per Row or Per Statement

In the example shown in Listing 7.12, triggers are executed for each inserted

or updated row. You can also execute one trigger per statement to be

updated. However, as a result, more than one row could be modified at a

time. Accordingly, the assignments of the

OLD

and

NEW

parameters of the

trigger must be defined as a table as well. Listing 7.14 contains an example

of creating a trigger with row-based processing. Listing 7.15 shows an exam-

ple of processing per statement.

CREATE TRIGGER rp_insert AFTER INSERT

ON invoice_position

REFERENCING NEW ROW ls_new

FOR EACH ROW

...

Listing 7.14 Parameterization for Row-Based Processing

300

7 Creating, Deleting, and Editing Database Objects

CREATE TRIGGER rp_insert AFTER INSERT

ON invoice_position

REFERENCING NEW TABLE AS lt_new

FOR EACH STATEMENT

...

Listing 7.15 Parameterization for Processing per Statement with

Table Parameters

By wrapping up with SQL statements for creating, changing, and deleting

database objects, we are finished with our exploration of individual lan-

guage components. In the next section, we'll deal with the SAP NetWeaver

application server and the ABAP programming language, located above the

database. This is how we'll access SQLScript.

Contents

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

1 SAP HANA

1.1 What Is SAP HANA?

...................................................................................... 24

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

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

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

1.2 System Architecture

..................................................................................... 32

1.2.1 SAP HANA Server Components .................................................. 32

1.2.2 Databases and Tenants ................................................................ 34

1.3 Organizing Database Objects

................................................................... 36

1.3.1 Database Schemas ......................................................................... 36

1.3.2 Database Catalogs ......................................................................... 38

1.3.3 Content and Repositories ............................................................ 39

1.4 Development Environments

..................................................................... 40

1.4.1 SAP HANA Studio ............................................................................ 41

1.4.2 SAP HANA Web-Based Development Workbench .............. 45

1.4.3 SAP Web IDE ..................................................................................... 48

1.5 The SQL Console

............................................................................................. 50

2 Getting Started with SQLScript

2.1 SQL Versus SQLScript

.................................................................................... 56

2.2 Basic Language Elements

........................................................................... 59

2.2.1 Statements ....................................................................................... 60

2.2.2 Whitespace ....................................................................................... 60

2.2.3 Comments ........................................................................................ 61

2.2.4 Literals ................................................................................................ 62

Contents

2.2.5 Identifiers .......................................................................................... 65

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

2.2.7 System Variables ............................................................................ 67

2.2.8 Reserved Words .............................................................................. 68

2.2.9 Operators .......................................................................................... 69

2.2.10 Expressions ....................................................................................... 72

2.2.11 Predicates .......................................................................................... 73

2.2.12 Data Types ........................................................................................ 73

2.2.13 The NULL Value ............................................................................... 74

2.2.14 The DUMMY Table ......................................................................... 76

2.3 Modularization and Logical Containers

.............................................. 78

2.3.1 Blocks .................................................................................................. 80

2.3.2 Procedures ........................................................................................ 83

2.3.3 User-Defined Functions ............................................................... 90

2.4 Sample Program

............................................................................................. 94

2.4.1 Requirements .................................................................................. 94

2.4.2 Requirements Analysis ................................................................. 95

2.4.3 Implementation .............................................................................. 97

2.4.4 Testing the Implementation ....................................................... 104

3 Declarative Programming in SQLScript

109

3.1 Table Variables

............................................................................................... 110

3.1.1 Declaring Table Variables ............................................................ 111

3.1.2 Using Table Variables .................................................................... 112

3.2 SELECT Statements

........................................................................................ 112

3.2.1 SELECT Clauses ................................................................................ 113

3.2.2 Field List of SELECT Clauses ......................................................... 114

3.2.3 FROM Clauses .................................................................................. 130

3.2.4 Joins .................................................................................................... 132

3.2.5 WHERE Conditions ......................................................................... 138

3.2.6 WITH Clauses ................................................................................... 147

3.2.7 GROUP BY Clauses .......................................................................... 149

3.2.8 HAVING Clauses .............................................................................. 152

3.2.9 ORDER BY Clauses .......................................................................... 153

Contents

3.2.10 Set Theory ......................................................................................... 154

3.2.11 Subqueries ........................................................................................ 156

3.2.12 Alias Names ...................................................................................... 158

3.3 Other Operators

............................................................................................. 160

3.3.1 Calculation Engine Plan Operators ........................................... 160

3.3.2 Map Merge ........................................................................................ 161

4 Data Types and Their Processing

165

4.1 Character Strings

........................................................................................... 166

4.1.1 Data Types for Character Strings .............................................. 166

4.1.2 Conversions ...................................................................................... 169

4.1.3 Character String Functions .......................................................... 169

4.2 Date and Time

................................................................................................. 185

4.2.1 Date Information ............................................................................ 185

4.2.2 Time Information ........................................................................... 191

4.2.3 Combined Time and Date Information ................................... 192

4.2.4 Processing Time and Date Values ............................................. 193

4.2.5 Examples of Processing Time Values ....................................... 198

4.3 Numerical Data

............................................................................................... 201

4.3.1 Basic Arithmetic Operations ....................................................... 202

4.3.2 Square Roots and Exponents ...................................................... 203

4.3.3 Logarithms ........................................................................................ 203

4.3.4 Rounding or Trimming .................................................................. 203

4.3.5 Trigonometry ................................................................................... 206

4.3.6 Random Numbers .......................................................................... 206

4.3.7 Sign ...................................................................................................... 207

4.3.8 Quantities and Amounts .............................................................. 207

4.4 Binary Data Types

.......................................................................................... 213

4.4.1 Conversion Between Binary Data, Hexadecimal Data,

and Character Strings ................................................................... 215

4.4.2 Bits and Bytes ................................................................................... 216

4.5 Conversions Between Data Types

.......................................................... 218

Contents

5 Write Access to the Database

221

5.1 INSERT

................................................................................................................. 222

5.1.1 Individual Data Records ............................................................... 222

5.1.2 Inserting Multiple Records Simultaneously .......................... 224

5.2 UPDATE

.............................................................................................................. 226

5.2.1 Simple UPDATE Statement ......................................................... 226

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

5.3 UPSERT or REPLACE

....................................................................................... 227

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

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

5.4 MERGE INTO

..................................................................................................... 229

5.5 DELETE

................................................................................................................. 232

5.6 TRUNCATE TABLE

........................................................................................... 232

6 Imperative Programming

233

6.1 Variables

............................................................................................................ 233

6.1.1 Local Scalar Variables .................................................................... 233

6.1.2 Local Table Variables ..................................................................... 238

6.1.3 Session Variables ............................................................................ 247

6.1.4 Temporary Tables ........................................................................... 248

6.2 Flow Control Using IF and ELSE

................................................................ 249

6.3 Loops

................................................................................................................... 253

6.3.1 FOR Loop ............................................................................................ 253

6.3.2 WHILE Loop ....................................................................................... 254

6.3.3 Controlling Loop Passes ............................................................... 255

6.3.4 Exercise: Greatest Common Divisor ......................................... 255

6.4 Cursors

................................................................................................................ 256

6.4.1 FOR Loop via a Cursor .................................................................... 257

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

Contents

6.5 Arrays

.................................................................................................................. 260

6.5.1 Generating an Array ...................................................................... 261

6.5.2 Accessing the Array ........................................................................ 261

6.5.3 Arrays as Local Variables .............................................................. 262

6.5.4 Splitting and Concatenating Arrays ......................................... 263

6.5.5 Arrays and Table Columns ........................................................... 264

6.5.6 Bubble Sort Exercise ...................................................................... 265

6.6 Transaction Control

...................................................................................... 268

6.6.1 Transactions ..................................................................................... 268

6.6.2 Autonomous Transactions .......................................................... 269

6.7 Executing Dynamic SQL

.............................................................................. 271

6.7.1 Parameters of Dynamic SQL ....................................................... 272

6.7.2 Input Parameters ............................................................................ 274

6.8 Error Handling

................................................................................................. 276

6.8.1 What Are Exceptions? ................................................................... 276

6.8.2 Triggering Exceptions ................................................................... 277

6.8.3 Catching Exceptions ...................................................................... 278

7 Creating, Deleting, and Editing

Database Objects

283

7.1 Tables

.................................................................................................................. 284

7.1.1 Creating Database Tables ............................................................ 284

7.1.2 Changing Database Tables .......................................................... 289

7.1.3 Deleting Database Tables ............................................................ 290

7.2 Table Types

....................................................................................................... 291

7.3 Views

................................................................................................................... 291

7.4 Sequences

......................................................................................................... 294

7.4.1 Increment .......................................................................................... 294

7.4.2 Limits .................................................................................................. 294

7.4.3 Behavior When Reaching the Limit .......................................... 295

7.4.4 Resetting the Sequence ................................................................ 296

7.4.5 Changing and Deleting a Sequence ......................................... 296

Contents

7.5 Triggers

.............................................................................................................. 296

7.5.1 Parameters ........................................................................................ 299

7.5.2 Per Row or Per Statement ............................................................ 299

8 SQLScript in ABAP Programs

301

8.1 AMDP Procedures

.......................................................................................... 304

8.1.1 Creating AMDP Procedures ......................................................... 305

8.1.2 Generated Objects of an AMDP Method ................................ 308

8.1.3 Lifecycle of the Generated Objects ........................................... 312

8.1.4 Two-Track Development .............................................................. 313

8.1.5 Using AMDP Procedures in AMDP Procedures ..................... 316

8.2 CDS Table Functions

..................................................................................... 319

8.2.1 Creating a CDS Table Function .................................................. 319

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

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

8.3 AMDP Functions for AMDP Methods

.................................................... 327

8.4 Alternatives to AMDPs for Calling SQLScript Code from

ABAP Programs

............................................................................................... 329

8.5 Recommendations

........................................................................................ 330

9 SQLScript in SAP Business Warehouse

333

9.1 Transformation Routines as AMDP

....................................................... 338

9.2 Successive Transformations and Mixed Execution

........................ 339

9.3 The Generated AMDP Classes

.................................................................. 341

9.3.1 Signature of AMDP Methods ...................................................... 342

9.3.2 Assigning the Output Tables ...................................................... 344

9.3.3 Access to Data from Other Data Models ................................ 345

9.4 The Individual Routines

.............................................................................. 348

9.4.1 Start Routines .................................................................................. 348

9.4.2 End Routines .................................................................................... 349

Contents

9.4.3 Expert Routines ............................................................................... 350

9.4.4 Field Routines ................................................................................... 351

9.5 Error Processing and Error Stack

............................................................. 352

9.5.1 Processing Flow in the Data Transfer Process ...................... 353

9.5.2 Example: Recognizing Incorrect Data in Table OUTTAB ... 354

9.5.3 Example: Finding Invalid Field Contents with

Regular Expressions ....................................................................... 355

10 Clean SQLScript Code

357

10.1 The Readability of the Code

...................................................................... 358

10.1.1 Formatting the Code ..................................................................... 358

10.1.2 Mnemonic Names .......................................................................... 359

10.1.3 Granularity of Procedures and Functions ............................... 361

10.1.4 Comments ........................................................................................ 364

10.1.5 Decomposing Complex Queries ................................................ 366

10.1.6 Readable SQLScript Statements ................................................ 371

10.2 Performance Recommendations

............................................................ 373

10.2.1 Reducing Data Volumes ............................................................... 373

10.2.2 Avoid Switching Between Row and Column Engines ........ 373

10.2.3 Declarative Queries ........................................................................ 373

10.2.4 Scalar Functions .............................................................................. 374

11 Tests, Errors, and Performance Analysis

375

11.1 Testing SQLScript Code

............................................................................... 376

11.1.1 SQL Console ...................................................................................... 376

11.1.2 Testing ABAP Managed Database Procedure Methods ..... 378

11.1.3 The TRACE Statement ................................................................... 380

11.2 The Debugger for SQLScript

...................................................................... 381

11.2.1 The SAP HANA Web-Based Development Workbench

Debugger ........................................................................................... 381

11.2.2 SQLScript Debugger in SAP HANA Studio ............................... 387

Contents

11.2.3 AMDP Debugger in the ABAP Development Tools .............. 391

11.2.4 Debugging in the SAP Web IDE .................................................. 394

11.3 Performance Analysis

.................................................................................. 397

11.3.1 Runtime Measurement ................................................................ 397

11.3.2 Execution Plan ................................................................................. 399

11.3.3 Performance Analysis in the SAP HANA Web-Based

Development Workbench ............................................................ 400

11.3.4 PlanViz ................................................................................................ 402

11.3.5 SQL Analyzer of the Database Explorer of the

SAP Web IDE ..................................................................................... 410

11.3.6 SQLScript Code Analyzer .............................................................. 412

Appendices

417

A Data Model for Task Management

........................................................ 419

B List of Abbreviations

.................................................................................... 423

CThe Author

........................................................................................................ 425

Index .................................................................................................................................. 427

427

Index

ABAP ................................................................. 75

ABAP Managed Database

Procedure (AMDP) .......................... 314

date format ............................................ 189

unit test .................................................... 316

ABAP Database Connectivity

(ABDC) ...................................................... 329

ABAP Managed Database Procedure

(AMDP) .............................................. 77, 301

ABAP .......................................................... 314

debugger .................................................. 391

field routine ............................................ 351

framework .............................................. 303

function ........................................... 302, 327

implementing a procedure ............... 307

method ..................................................... 305

objects ....................................................... 308

procedure ....................................... 302, 304

PROCEDURE method .......................... 342

recommendations ............................... 330

retroactive implementation ............ 315

testing the method .............................. 378

tools ........................................................... 338

ABAP_ALPHANUM() ................................ 169

ABAP_LOWER() .......................................... 171

ABAP_UPPER() ........................................... 171

ABS() ............................................................... 207

ACOS() ............................................................ 206

Adaptive Server Enterprise (ASE) .......... 56

ADD_*() ......................................................... 194

ADD_MONTHS_LAST() ........................... 194

ADD_MONTHS() ........................................ 194

ADD_WORKDAYS() .................................. 195

Advanced DataStore objects ................. 346

Aggregate expression .............................. 121

Aggregate function .................................. 121

Alias ................................................................ 115

Alias name ................................................... 158

Alignment .................................................... 359

Alpha conversion ...................................... 169

ALPHANUM ................................................. 167

ALTER TABLE ............................................... 289

Amount ......................................................... 207

APPLY_FILTER ............................................. 275

Array ............................................................... 260

access ........................................................ 261

as local variable .................................... 262

concatenate and split ......................... 263

generate ................................................... 261

table and .................................................. 264

ARRAY_AGG() ............................................. 265

AS BEGIN ................................................... 88, 91

ASCII ...................................................... 166, 184

character set .............................................. 60

ASIN() ............................................................. 206

Assigning an output table ..................... 344

Asterisk .......................................................... 116

ATAN() ........................................................... 206

ATAN2() ......................................................... 206

Automatic number assignment .......... 287

Autonomous transaction ...................... 269

AVG ................................................................. 123

Basic arithmetic operations .................. 202

BEGIN

AUTONOMOUS TRANSACTION ...... 269

block ............................................................. 80

Best practices .............................................. 357

BETWEEN ...................................................... 141

Binary data ................................................... 215

Binary data type ......................................... 213

Binary floating-point number ............. 201

BINTOHEX() ................................................. 215

BINTONHEX() .............................................. 215

BINTOSTR() .................................................. 215

BITCOUNT() ................................................. 218

BITSET() ......................................................... 216

BITUNSET() ................................................... 216

Blank characters ........................................... 60

Blank line ...................................................... 359

428

Index

BLOB ............................................................... 213

Block ................................................................. 80

anonymous ............................................... 82

comment .................................................... 61

BREAK ............................................................ 255

Breakpoint .......................................... 383, 392

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

Bubble sort algorithm ............................. 265

Calculation engine .................................... 399

Calculation engine plan operators

(CE functions) ......................................... 160

Calendar week ............................................. 197

CALL .................................................................. 88

CALL DATABASE PROCEDURE .............. 330

Call stack ....................................................... 385

CARDINALITY() ........................................... 260

CASCADE ....................................................... 290

CASE .................................................................. 99

CASE expression ........................................ 117

searched ................................................... 118

simple ........................................................ 117

CAST() ............................................................. 218

CEIL() ............................................................... 204

CHAR() ........................................................... 184

Character string ......................................... 166

data type .................................................. 166

function .................................................... 169

literals ......................................................... 64

search within .......................................... 178

Client capability ........................................... 34

Client concept ............................................. 210

Client handling ........................................... 326

CLOB ............................................................... 169

Code pushdown ........................................... 59

Code-to-data paradigm ............................. 57

Colon ................................................................ 67

Column alias ............................................... 158

Column definition .................................... 285

Column engine ........................................... 399

Column list UPSERT ................................. 228

Column name ............................................. 115

Column store ....................................... 24, 286

Column-based storage .............................. 25

Comment ............................................... 61, 364

Commercial rounding ............................. 203

COMMIT ........................................................ 268

CompositeProvider ................................... 333

Compression ................................................. 26

CONCAT() ...................................................... 171

array ........................................................... 263

Concatenation ............................................ 170

Constraint ..................................................... 285

CONTAINS .................................................... 145

Content ............................................................ 39

CONTINUE .................................................... 255

Control character ....................................... 355

Conversion

data type .................................................. 218

explicit ....................................................... 218

implicit ...................................................... 218

permissible .............................................. 219

CONVERT_CURRENCY() .......................... 210

CONVERT_UNIT() ...................................... 207

Core data services (CDS) ............................ 23

client .......................................................... 326

objects of a table function ................. 325

table function ...................... 302, 319, 320

view ............................................................ 319

Correlation name ............................ 116, 372

COS() ............................................................... 206

COSH() ............................................................ 206

Cosine ............................................................. 206

COT() ............................................................... 206

COUNT ................................................. 121, 123

CREATE FUNCTION ..................................... 90

CREATE PROCEDURE .................................. 83

CREATE TABLE ............................................. 284

CREATE TABLE AS ...................................... 288

CREATE TABLE LIKE .................................. 288

Critical path .................................................. 405

Cross-join ...................................................... 132

Currency conversion ................................ 210

CURRENT_DATE ......................................... 193

CURRENT_LINE_NUMBER ....................... 68

CURRENT_OBJECT_NAME ....................... 68

CURRENT_OBJECT_SCHEMA .................. 68

CURRENT_TIME ......................................... 193

CURRENT_TIMESTAMP ........................... 193

CURRENT_UTCDATE ................................ 193

429

Index

CURRENT_UTCTIME ................................ 193

CURRENT_UTCTIMESTAMP ................. 193

Data control language (DCL) ................... 57

Data definition language (DDL) ............. 57

Data manipulation language (DML) ..... 57

Data model

data ........................................................... 421

example ................................................... 419

installation ............................................. 421

table ........................................................... 419

task management ................................ 419

Data preview ............................................... 378

Data provisioning server .......................... 34

Data transfer process (DTP) .................. 335

Data type ......................................................... 73

composite ................................................... 73

convert ............................................ 169, 218

primitive ..................................................... 73

scalar ............................................................ 73

Data volume ............................................... 373

Database

catalog ......................................................... 38

different systems .................................. 313

schema ........................................................ 36

write access ............................................. 221

Database explorer ..................................... 410

Database object ............................................. 36

DATE ............................................................... 185

DATS ............................................................... 189

DAYNAME() ................................................. 197

DAYOFYEAR() .............................................. 197

Deadlock ....................................................... 270

Debug browser ........................................... 384

Debugger ................................... 376, 381, 387

Debugging

external ........................................... 387, 390

in SAP HANA Studio ............................ 390

Decimal floating-point number ......... 201

Declarative programming ..................... 110

DECLARE

CONDITION ............................................ 277

CURSOR .................................................... 257

Declaring table variables ....................... 111

DEFAULT

DECLARE .................................................. 234

IN parameter ............................................. 86

SCHEMA ...................................................... 87

DELETE .................................................. 232, 245

Delta merge operation ............................... 27

DETERMINISTIC ............................................ 91

Development environment ..................... 40

Deviating fiscal year ................................. 197

Dictionary compression ............................ 26

DISTINCT ...................................................... 114

DO BEGIN ........................................................ 82

DROP TABLE ................................................ 290

DUMMY ............................................................ 76

Dynamic SQL .............................................. 271

Eclipse ............................................................... 41

debugger .................................................. 388

installation ................................................. 43

SAP Cloud platform ................................ 43

EMPTY ............................................................... 86

Empty date ................................................... 186

Empty result ................................................ 157

END .................................................................... 88

anonymous block .................................... 82

block ............................................................. 80

functions ..................................................... 91

End routine .................................................. 349

End-of-line comment ................................. 61

Engine ............................................................ 399

Equivalent join ........................................... 135

Error code ..................................................... 276

Error handling ................................... 276, 278

Error processing ........................................ 352

ERRORTAB .................................................... 344

Error-tolerant search .................................. 31

Escape expression ..................................... 140

ESCAPE_DOUBLE_QUOTES ................... 275

ESCAPE_SINGLE_QUOTES ..................... 275

Euclidean algorithm ................................ 256

Evaluation sequence of the

operators .................................................... 70

EXCEPT .......................................................... 156

Exception, trigger ...................................... 277

430

Index

EXEC ................................................................ 271

EXEC SQL ....................................................... 329

EXECUTE IMMEDIATE ............................. 272

Execution plan ................................. 399, 405

Execution, mixed ...................................... 339

EXISTS ............................................................ 142

Expert routine ............................................ 350

EXPLAIN PLAN ............................................ 400

Exponent ...................................................... 203

Expression ...................................................... 72

regular ....................................................... 174

Expression editor ...................................... 387

Extended store server ............................... 33

EXTRACT() .................................................... 196

FETCH INTO ................................................. 258

Field list .................................................. 72, 114

Field routine ................................................ 351

Fiscal year ..................................................... 197

Fixed-point number ................................. 201

Floating-point number ........................... 201

FLOOR() ......................................................... 204

Flow control ................................................. 249

FOR loop .............................................. 253, 257

Formatting ................................................... 358

FROM clause ................................................ 130

UPDATE .................................................... 227

Full outer join ............................................. 136

Function .......................................................... 78

Function call in the field list ................. 120

Generic programming ............................ 271

GEO data ......................................................... 31

Global temporary table (GTT) ............... 248

Granularity .................................................. 361

Graph engine ................................................ 31

Graph processing ........................................ 31

Greatest common divisor ...................... 255

GROUP BY clause ............................. 121, 149

GROUPING SETS ........................................ 151

Hamming distance ................................... 183

HAMMING_DISTANCE() ......................... 183

HAVING clause ........................................... 152

Header comment ....................................... 364

Hexadecimal representation ................ 215

HEXTOBIN() ................................................. 215

High Performance Analytic

Appliance ................................................... 24

Hungarian notation ................................. 360

Identifiers ....................................................... 65

IF ....................................................................... 249

IF_AMDP_MARKER_HDB ...................... 305

Imperative programming ...................... 233

implicit client handling .......................... 326

Implicit date conversion ........................ 187

IN ...................................................................... 141

Increment ..................................................... 294

Index server ................................................... 32

Index-based access .................................... 239

Infinite loop ................................................. 254

Initial value, variable ................................ 234

In-memory ..................................................... 25

Inner join ................................... 124, 133, 155

Input parameter, dynamic SQL ............ 274

INSERT .................................................. 222, 241

Insert-Only ..................................................... 27

Integer ............................................................ 201

INTERSECT .................................................... 155

Intersection .................................................. 155

Interval .......................................................... 141

INTO clause .................................................. 235

INTO clause EXEC ...................................... 273

IS ....................................................................... 144

IS_EMPTY ...................................................... 247

IS_SQL_INJECTION_SAVE ...................... 275

ISCLOSED ...................................................... 259

ISOWEEK() ..................................................... 197

431

Index

Join .................................................................. 132

condition ................................................. 133

engine ....................................................... 399

type ............................................................ 132

LAG ................................................................. 129

LANGUAGE ..................................................... 86

LANGUAGE SQLSCRIPT

functions ..................................................... 91

LAST_DAY(<date>) ................................... 195

LCASE() .......................................................... 171

LEAD ............................................................... 129

Left outer join ............................................. 135

LEFT() .............................................................. 172

LENGTH() ...................................................... 170

Lexical element ............................................ 55

LIKE ................................................................. 140

LIKE_REGEXPR ........................................... 184

LIMIT .............................................................. 114

Limit ............................................................... 294

Line break .............................................. 60, 358

Linear dimension ......................................... 96

Linguistic search ................................ 31, 145

Literal ................................................................ 62

LN() ................................................................. 203

LOB .................................................................. 169

Local table variable ................................... 238

Local temporary table (LTT) .................. 249

Local variable ................................................. 67

LOCALTOUTC() ........................................... 198

LOCATE_REGEXPR() ........................ 175, 178

LOCATE() ....................................................... 178

LOG() .............................................................. 203

Logarithm .................................................... 203

Logging ......................................................... 269

Loop ................................................................ 253

LOWER() ........................................................ 171

LPAD() ............................................................ 180

LTRIM() .......................................................... 181

Map Merge ................................................... 161

Material number ....................................... 182

MAX ................................................................ 123

MAXVALUE .................................................. 295

MDC ................................................................... 35

MEMBER OF ........................................ 143, 262

MEMBER_AT() ............................................ 262

MERGE INTO ............................................... 229

MIN ................................................................. 123

MINVALUE ................................................... 295

MOD() ............................................................. 202

MONTHNAME() ......................................... 197

Multicontainer database ........................... 35

Multitenant database containers .......... 35

Name server ................................................... 32

Naming .......................................................... 359

Native SQL .................................................... 301

NCHAR() ........................................................ 184

NCLOB ............................................................ 169

NDIV0() .......................................................... 202

NEXT_DAY( <date>) .................................. 195

Non-equivalent join ................................. 135

NOT EXISTS .................................................. 156

NOT NULL ..................................................... 285

NOT NULL DECLARE ................................. 234

NOTFOUND ................................................. 259

NOW() ............................................................. 193

NULL .................................................................. 74

NULL in aggregate functions ................ 124

NULLS FIRST ................................................ 153

NULLS LAST ................................................. 153

Number of rows ......................................... 247

Numerical data ........................................... 201

Numerical literal ........................................... 63

NVARCHAR .................................................. 166

OCCURRENCES_REGEXPR() ......... 175, 179

OData ................................................................ 30

OFFSET ........................................................... 114

432

Index

OLAP engine ................................................ 399

ON .................................................................... 133

OPEN CURSOR ............................................ 258

Open SQL ............................................... 75, 301

OpenUI5 total ............................................... 30

Operator .......................................................... 69

Operator expression ................................ 117

Operator list ................................................ 407

OPTIONS READ-ONLY .............................. 307

ORDER BY ........................................... 127, 153

ORDINALITY ................................................ 264

OUTTAB ............................................... 344, 354

Overlapping ................................................. 237

Parameter

dynamic SQL ........................................... 272

example ...................................................... 96

named ......................................................... 88

trigger ........................................................ 299

view ............................................................ 293

Parameterization ......................................... 95

Parentheses .......................................... 70, 371

PARTITION BY ............................................. 127

Performance ................................................ 373

Performance analysis .................... 375, 397

Performance Trace .................................... 408

Personal schema .......................................... 36

Phonetic code ............................................. 183

Placeholders ................................................ 140

Plan operator .............................................. 399

PlanViz ........................................................... 402

POWER() ........................................................ 203

Predicate ......................................................... 73

Predictive analytics .................................... 32

Pretty printer .............................................. 359

PRIMARY KEY .............................................. 285

Primary key ....................................... 221, 285

Procedure ................................................ 78, 83

call ................................................................ 88

create ........................................................... 83

parameter list ........................................... 84

properties ................................................... 86

PUBLIC schema ............................................ 36

Public synonym ........................................... 38

Quantity ........................................................ 207

Quantity conversion ...................... 207, 210

QUARTER() .................................................... 197

Query result, dynamic SQL .................... 273

Query, decompose .................................... 366

Question mark .............................................. 89

Quotation marks ......................................... 65

RAND_SECURE() ......................................... 206

RAND() ........................................................... 206

Random numbers ..................................... 206

Readability ................................................... 358

READS SQL DATA ......................................... 87

RECORD ......................................................... 343

RECORD_COUNT ....................................... 247

Regular expression ................................... 355

REPLACE ........................................................ 227

REPLACE_REGEXPR() ............. 175, 176, 179

REPLACE() ..................................................... 179

Repository ...................................................... 39

Request .......................................................... 335

Requirements analysis .............................. 95

Reserved words ............................................ 68

RESIGNAL ...................................................... 281

RESTART ........................................................ 296

RESTRICT ....................................................... 290

RETURNS ......................................................... 91

Reuse ................................................................ 78

Right outer join .......................................... 136

RIGHT() .......................................................... 172

ROLLBACK .......................................... 232, 268

ROUND() ........................................................ 203

Rounding ...................................................... 203

Row engine ................................................... 399

Row store ...................................................... 286

ROWCOUNT .......................................... 68, 259

ROWS clause ................................................ 128

RPAD() ............................................................ 181

RTRIM() .......................................................... 181

Runtime measurement ........................... 397

433

Index

SAP Business Warehouse (SAP BW)

query ......................................................... 409

transformation ..................................... 334

transformation routine ..................... 393

SAP Cloud Platform .................................... 43

SAP Fiori app .................................................. 30

SAP HANA

database ..................................................... 24

deployment infrastructure .................. 48

introduction ....................................... 23, 24

SAP HANA database explorer ................. 48

SAP HANA deployment infrastructure

(HDI) container ........................................ 30

SAP HANA deployment infrastructure

(HDI) server ............................................... 33

SAP HANA extended services

(SAP HANA XS)

classic model ............................................. 29

server ............................................................ 33

SAP HANA extended services,

advanced model (SAP HANA XSA) ... 29

server ............................................................ 33

SAP HANA Studio .................................. 40, 41

database connection ............................. 43

debugger .................................................. 387

SAP HANA Web-Based Development

Workbench ......................................... 40, 45

SAP NetWeaver schema ............................ 37

SAP Web IDE ............................................ 40, 48

SAPUI5 total ................................................... 30

Savepoint ........................................................ 26

Scalar query .................................................... 73

Search ............................................................... 31

SEARCH table operator ........................... 246

SECONDDATE ............................................. 192

SECURITY MODE functions ..................... 91

SELECT ........................................................... 109

SELECT clause ............................................. 113

SELECT INTO ............................................... 236

SELECT query .............................................. 113

SELECT statement ..................................... 112

UPSERT ..................................................... 229

Self-join ......................................................... 132

Semantic search ........................................... 31

Semicolon ....................................................... 60

Separation of concerns ........................... 313

Sequence ....................................................... 294

change ...................................................... 296

delete ......................................................... 296

reset ........................................................... 296

Sequence of the operators ........................ 70

SERIES_GENERATE_DATE ...................... 162

Server component ....................................... 32

Session variable ......................................... 247

SET clause ..................................................... 226

Set operations ............................................. 154

SHORTTEXT ................................................. 168

Sign ................................................................. 207

SIGN() ............................................................. 207

SIGNAL ........................................................... 277

SIN() ................................................................ 206

Single container database ......................... 34

SINH() ............................................................. 206

Sinus ............................................................... 206

SLT schema ..................................................... 37

SOUNDEX() .................................................. 183

Special characters ......................................... 65

SQL ..................................................................... 56

SQL Analyzer ............................................... 410

SQL console .......................................... 50, 376

SQL injection ............................................... 275

SQL SECURITY ................................................ 87

SQL__PROCEDURE__SOURCE__

RECORD .................................................... 344

SQL_ERROR_CODE ............................ 68, 278

SQL_ERROR_MESSAGE .................... 68, 278

SQLScript ......................................................... 56

best practices ......................................... 357

performance ........................................... 373

readability ............................................... 371

test .............................................................. 376

SQLScript, Code Analyzer ....................... 412

SQRT() ............................................................ 203

Square root .................................................. 203

Start routine ................................................ 348

Statement ........................................................ 60

Streaming cluster ......................................... 34

String

padding .................................................... 180

replace variable ..................................... 179

434

Index

String (Cont.)

similarity .................................................. 183

trimming .................................................. 180

STRING_AGG ............................................... 123

Structure comment .................................. 365

Stub procedure ........................................... 310

SUBARRAY() ................................................. 264

Subquery ....................................................... 156

correlated ................................................. 125

in field lists .............................................. 124

SUBSTR_AFTER() ........................................ 173

SUBSTR_BEFORE() ..................................... 173

SUBSTR_REGEXPR() ........................ 173, 175

SUBSTRING() ............................................... 172

Subtracting sets ......................................... 156

SUM ...................................................... 121, 123

System architecture ................................... 32

System database .......................................... 35

System schema ............................................ 37

System variable ............................................ 67

Table ............................................................... 284

alias ............................................................ 159

change ....................................................... 289

copy ............................................................ 288

create with SQL ...................................... 288

definition ................................................. 284

global temporary .................................. 311

local variable .......................................... 238

parameter ................................................ 310

type ................................................... 286, 291

variable .............................................. 74, 110

Table parameter ........................................... 74

Tables

tables used ............................................... 408

Tabs ................................................................... 60

TAN() ............................................................... 206

TANH() ........................................................... 206

Task management .................................... 419

Temporary table ........................................ 248

Tenant .............................................................. 34

Tenant database ........................................... 35

Test ..................................... 100, 104, 375, 376

TEXT ................................................................ 169

Text mining ................................................... 31

TIME ................................................................ 191

Time ................................................................ 185

Time information ...................................... 191

Time zone ..................................................... 198

Timeline ........................................................ 407

TIMESTAMP ................................................. 192

TO_ALPHANUM() ...................................... 169

TO_DATE() .................................................... 188

TO_NVARCHAR .......................................... 169

TO_NVARCHAR() ....................................... 188

TO_TIME() ..................................................... 191

TO_VARCHAR() ....................... 169, 188, 191

TOP .................................................................. 114

TRACE ............................................................. 380

Transaction .................................................. 268

Transaction control .................................. 268

Transaction log ............................................. 26

Transaction RSA1 ....................................... 339

Transformation .......................................... 334

on SAP HANA .......................................... 336

routine ....................................................... 338

Transport ...................................................... 341

Triggers .......................................................... 296

Trigonometry .............................................. 206

TRIM_ARRAY() ............................................ 264

TRIM() ............................................................. 181

Troubleshooting ........................................ 375

TRUNCATE TABLE ...................................... 232

UCASE() .......................................................... 171

UDF_SORT_2 ............................................... 251

UDF_SORT_3 ............................................... 252

UMINUS() ...................................................... 207

Unicode ......................................................... 166

UNICODE() .................................................... 184

UNION ............................................................ 154

Union .............................................................. 154

UNIQUE ......................................................... 285

UNNEST() ....................................................... 264

UPDATE ......................................................... 226

table operator ........................................ 243

UPPER() .......................................................... 171

UPSERT ........................................................... 227

435

Index

User-defined function (UDF) ......... 90, 361

create ........................................................... 90

implementation ....................................... 97

properties ................................................... 90

sample application ................................. 94

sample program flow ............................ 96

scalar ......................................................... 120

USING ............................................................ 307

view ............................................................ 310

WADP routine ........................................ 345

UTC ................................................................. 198

UTCTOLOCAL() ........................................... 198

Value

constant ................................................... 121

VARBINARY ................................................. 213

VARCHAR ..................................................... 166

Variable ......................................................... 233

local scalar .............................................. 233

scalar ......................................................... 121

visibility .................................................... 237

VERTICAL_UNION .................................... 351

View ................................................................ 291

Visibility ........................................................ 237

Watchpoint .................................................. 396

WBDW 씮 SAP HANA Web-Based

Development Workbench

WEEK() ........................................................... 197

WEEKDAY() .................................................. 197

WHEN MATCHED ...................................... 231

WHERE

comparison ............................................. 138

with multiple values ............................ 139

WHERE clause

DELETE ...................................................... 232

UPSERT ..................................................... 229

WHERE condition ...................................... 138

WHILE Loop ................................................. 254

Whitespace ..................................................... 60

Window functions .................................... 125

WITH clause ................................................. 147

WITH ENCRYPTION ..................................... 87

WITH ORDINALITY ................................... 264

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 usage

and exploitation rights are reserved by the author and the publisher.

Jörg Brandeis is an independent consultant, de-

veloper, and trainer with a focus on SAP BW, SAP

HANA, and ABAP OO. He advises well-known custo-

mers on technical topics in the area of architecture and

development. In addition, he is the leading indepen-

dent trainer for SQLScript in Germany. Before that, he

worked until mid-2015 as development manager at

zetVisions AG in Heidelberg. There he was responsible for the develop-

ment and architecture of the SAP NetWeaver based products zetVisions

CIM and SPoT. In this role, he gained extensive experience with agile

development methods and clean code.

Jörg Brandeis

SQLScript for SAP HANA

435 Pages, 2019, $79.95

ISBN 978-1-4932-1822-6