Introduction

15

Structure of the Book

18

Acknowledgments

20

1 SAP HANA

23

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

55

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

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

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

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

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

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

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

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

A.1 Overview of the Tables

419

A.2 Data

421

A.3 Installation

421

B List of Abbreviations

423

C The Author

425