SQLScript for SAP HANA

· ... 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
· ... Index ... 401