Introduction

15

Structure of the Book

18

Acknowledgments

20

1 SAP HANA

21

1.1 What Is SAP HANA?

22

1.1.1 SAP HANA: A Fast SQL Database

22

1.1.2 SAP HANA: An Application Server

26

1.1.3 SAP HANA: A Collection of Tools

27

1.2 System Architecture

29

1.2.1 SAP HANA Server Components

29

1.2.2 Databases and Tenants

30

1.3 Organizing Database Objects

32

1.3.1 Database Schemas

32

1.3.2 Database Catalogs

34

1.3.3 Content and Repositories

35

1.4 Development Environments

36

1.4.1 SAP HANA Studio

37

1.4.2 SAP HANA Database Explorer

40

1.5 The SQL Console

44

1.6 Summary

47

2 Getting Started with SQLScript

49

2.1 SQL versus SQLScript

49

2.2 Basic Language Elements

53

2.2.1 Statements

53

2.2.2 Whitespace

54

2.2.3 Comments

54

2.2.4 Literals

56

2.2.5 Identifiers

58

2.2.6 Access to Local Variables and Parameters

59

2.2.7 System Variables

60

2.2.8 Reserved Words

61

2.2.9 Operators

61

2.2.10 Expressions

63

2.2.11 Predicates

65

2.2.12 Data Types

66

2.2.13 The NULL Value

67

2.2.14 The DUMMY Table

69

2.3 Modularization and Logical Containers

70

2.3.1 Blocks

72

2.3.2 Procedures

75

2.3.3 User-Defined Functions

83

2.3.4 User-Defined Libraries

87

2.4 Sample Program

89

2.4.1 Requirements

89

2.4.2 Requirements Analysis

90

2.4.3 Implementation

91

2.4.4 Testing the Implementation

97

2.5 Summary

100

3 Declarative Programming in SQLScript

101

3.1 Table Variables

102

3.1.1 Declaring Table Variables

102

3.1.2 Using Table Variables

103

3.2 SELECT Statements

104

3.2.1 SELECT Clauses

105

3.2.2 Field List of SELECT Clauses

105

3.2.3 FROM Clauses

119

3.2.4 Joins

122

3.2.5 WHERE Conditions

130

3.2.6 WITH Clauses

137

3.2.7 GROUP BY Clauses

139

3.2.8 HAVING Clauses

141

3.2.9 ORDER BY Clauses

142

3.2.10 Set Theory

143

3.2.11 Subqueries

145

3.2.12 Alias Names

146

3.3 Other Operators

148

3.3.1 Calculation Engine Plan Operators

148

3.3.2 MAP_MERGE Operator

149

3.3.3 MAP_REDUCE Operator

150

3.4 Summary

151

4 Data Types and Their Processing

153

4.1 Character Strings

153

4.1.1 Data Types for Character Strings

154

4.1.2 Conversions

157

4.1.3 Character String Functions

157

4.1.4 SQLSCRIPT_STRING Library

171

4.2 Date and Time

176

4.2.1 Date Information

176

4.2.2 Time Information

181

4.2.3 Combined Time and Date Information

182

4.2.4 Processing Time and Date Values

182

4.2.5 Examples of Processing Time Values

187

4.3 Numerical Data

189

4.3.1 Basic Arithmetic Operations

191

4.3.2 Square Roots and Exponents

191

4.3.3 Logarithms

192

4.3.4 Rounding or Trimming

192

4.3.5 Trigonometry

194

4.3.6 Random Numbers

194

4.3.7 Sign

194

4.3.8 Quantities and Amounts

195

4.4 Binary Data Types

200

4.4.1 Conversion between Binary Data, Hexadecimal Data, and Character Strings

201

4.4.2 Bits and Bytes

202

4.5 Conversions between Data Types

204

4.6 Summary

205

5 Write Access to the Database

207

5.1 INSERT

208

5.1.1 Individual Data Records

208

5.1.2 Inserting Multiple Records Simultaneously

209

5.2 UPDATE

211

5.2.1 Simple UPDATE Statement

211

5.2.2 UPDATE Statement with Reference to Other Tables

212

5.3 UPSERT or REPLACE

213

5.3.1 Inserting or Updating Individual Data Records

213

5.3.2 Inserting or Updating Multiple Data Records

214

5.4 MERGE INTO

214

5.5 DELETE

217

5.6 TRUNCATE TABLE

217

5.7 Summary

217

6 Imperative Programming

219

6.1 Variables

219

6.1.1 Local Scalar Variables

219

6.1.2 Local Table Variables

224

6.1.3 Session Variables

234

6.1.4 Temporary Tables

235

6.2 Flow Control Using IF and ELSE

236

6.3 Loops

239

6.3.1 FOR Loop

239

6.3.2 WHILE Loop

240

6.3.3 Controlling Loop Passes

241

6.3.4 Exercise: Greatest Common Divisor

242

6.4 Cursors

243

6.4.1 FOR Loop via a Cursor

243

6.4.2 Open, Read, and Close Explicitly

244

6.4.3 Updateable Cursors

246

6.5 Arrays

246

6.5.1 Generating an Array

247

6.5.2 Accessing the Array

247

6.5.3 Arrays as Local Variables

248

6.5.4 Splitting and Concatenating Arrays

249

6.5.5 Arrays and Table Columns

250

6.5.6 Bubble Sort Exercise

251

6.6 Transaction Control

253

6.6.1 Transactions

253

6.6.2 Autonomous Transactions

254

6.7 Executing Dynamic SQL

255

6.7.1 Parameters of Dynamic SQL

257

6.7.2 Input Parameters

258

6.8 Error Handling

260

6.8.1 What Are Exceptions?

261

6.8.2 Triggering Exceptions

262

6.8.3 Catching Exceptions

262

6.9 Summary

266

7 Creating, Deleting, and Editing Database Objects

267

7.1 Tables

268

7.1.1 Creating Database Tables

268

7.1.2 Changing Database Tables

272

7.1.3 Deleting Database Tables

273

7.2 Table Types

273

7.3 Views

274

7.4 Sequences

276

7.4.1 Increment

277

7.4.2 Limits

277

7.4.3 Behavior When Reaching the Limit

277

7.4.4 Resetting the Sequence

278

7.4.5 Changing and Deleting a Sequence

278

7.5 Triggers

278

7.5.1 Parameters

280

7.5.2 Per Row or Per Statement

281

7.6 Summary

281

8 SQLScript in ABAP Programs

283

8.1 AMDP Procedures

283

8.1.1 Introduction to AMDP

284

8.1.2 Creating AMDP Procedures

287

8.1.3 Generated Objects of an AMDP Method

290

8.1.4 Lifecycle of the Generated Objects

294

8.1.5 Two-Track Development

294

8.1.6 Using AMDP Procedures in Other AMDP Procedures

297

8.2 CDS Table Functions

300

8.2.1 Creating a CDS Table Function

300

8.2.2 Generated Objects of a CDS Table Function

305

8.2.3 Implicit Client Handling of CDS Table Functions

306

8.3 AMDP Functions for AMDP Methods

307

8.3.1 AMDP Table Functions

307

8.3.2 Scalar AMDP Functions

309

8.4 Alternatives to AMDP for Calling SQLScript Code from ABAP Programs

310

8.5 Recommendations

311

8.6 Summary

312

9 SQLScript in SAP Business Warehouse

313

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

314

9.2 Transformation Routines as AMDP

318

9.2.1 Creating Transformation Routines in Eclipse

318

9.2.2 Creating Transformation Routines in SAP GUI

319

9.3 Successive Transformations and Mixed Execution

320

9.4 Generated AMDP Classes

321

9.4.1 Signature of AMDP Methods

323

9.4.2 Assigning the Output Tables

325

9.4.3 Access to Data from Other Data Models

325

9.5 Individual Routines

328

9.5.1 Start Routines

329

9.5.2 End Routines

329

9.5.3 Expert Routines

330

9.5.4 Field Routines

332

9.6 Error Processing and Error Stack

333

9.6.1 Processing Flow in the Data Transfer Process

335

9.6.2 Example: Recognizing Incorrect Data in Table OUTTAB

336

9.6.3 Example: Finding Invalid Field Contents with Regular Expressions

337

9.7 Summary

337

10 Clean SQLScript Code

339

10.1 Code Readability

339

10.1.1 Formatting the Code

340

10.1.2 Mnemonic Names

341

10.1.3 Granularity of Procedures and Functions

342

10.1.4 Comments

345

10.1.5 Decomposing Complex Queries

347

10.1.6 Readable SQLScript Statements

351

10.2 Performance Recommendations

352

10.2.1 Reducing Data Volumes

353

10.2.2 Avoid Switching between Row and Column Engines

353

10.2.3 Declarative Queries

353

10.2.4 Scalar Functions

353

10.3 Summary

354

11 Tests, Errors, and Performance Analysis

355

11.1 Testing SQLScript Code

356

11.1.1 SQL Console

356

11.1.2 Testing ABAP-Managed Database Procedure Methods

358

11.1.3 SQLSCRIPT_LOGGING Library

359

11.1.4 End-User Test Framework in SQLScript

361

11.2 Debugger for SQLScript

365

11.2.1 SQLScript Debugger in SAP HANA Studio

366

11.2.2 ABAP-Managed Database Procedure Debugger in the ABAP Development Tools

369

11.2.3 Debugging in the SAP HANA Database Explorer

372

11.3 Performance Analysis

374

11.3.1 Runtime Measurement

374

11.3.2 Execution Plan

375

11.3.3 Plan Visualizer

377

11.3.4 SQL Analyzer of the SAP HANA Database Explorer

384

11.3.5 SQLScript Code Analyzer

386

11.4 Summary

390

Appendices

391

A Data Model for Task Management

393

A.1 Overview of the Tables

393

A.2 Data

394

A.3 Installation

395

B List of Abbreviations

397

C The Author

399