SQLScript for SAP HANA (First Edition) (SAP PRESS)

Description

Developers, your complete guide to SQL Script programming for SAP HANA is here! Get hands-on with the SQLScript language, understand the data types you'll work with, and master the function library. Learn to implement SAP HANA database procedures and functions using imperative and declarative SQL script. See how SQLScript plays with ABAP, SAP BW on SAP HANA, and SAP BW / 4HANA. Finally, optimize, troubleshoot and analyze your SQLScript programs. Contents Introduction Structure of the Book Acknowledgments 1 SAP HANA 1.1 What Is SAP HANA? 1.1.1 SAP HANA: A Fast SQL Database 1.1.2 SAP HANA: An Application Server 1.1.3 SAP HANA: A Collection of Tools 1.2 System Architecture 1.2.1 SAP HANA Server Components 1.2.2 Databases and Tenants 1.3 Organizing Database Objects 1.3.1 Database Schemas 1.3.2 Database Catalogs 1.3.3 Content and Repositories 1.4 Development Environments 1.4.1 SAP HANA Studio 1.4.2 SAP HANA Web-Based Development Workbench 1.4.3 SAP Web IDE 1.5 The SQL Console 2 Getting Started with SQLScript 2.1 SQL Versus SQLScript 2.2 Basic Language Elements 2.2.1 Statements 2.2.2 Whitespace 2.2.3 Comments 2.2.4 Literals 2.2.5 Identifiers 2.2.6 Access to Local Variables and Parameters 2.2.7 System Variables 2.2.8 Reserved Words 2.2.9 Operators 2.2.10 Expressions 2.2.11 Predicates 2.2.12 Data Types 2.2.13 The NULL Value 2.2.14 The DUMMY Table 2.3 Modularization and Logical Containers 2.3.1 Blocks 2.3.2 Procedures 2.3.3 User-Defined Functions 2.4 Sample Program 2.4.1 Requirements 2.4.2 Requirements Analysis 2.4.3 Implementation 2.4.4 Testing the Implementation 3 Declarative Programming in SQLScript 3.1 Table Variables 3.1.1 Declaring Table Variables 3.1.2 Using Table Variables 3.2 SELECT Statements 3.2.1 SELECT Clauses 3.2.2 Field List of SELECT Clauses 3.2.3 FROM Clauses 3.2.4 Joins 3.2.5 WHERE Conditions 3.2.6 WITH Clauses 3.2.7 GROUP BY Clauses 3.2.8 HAVING Clauses 3.2.9 ORDER BY Clauses 3.2.10 Set Theory 3.2.11 Subqueries 3.2.12 Alias Names 3.3 Other Operators 3.3.1 Calculation Engine Plan Operators 3.3.2 Map Merge 4 Data Types and Their Processing 4.1 Character Strings 4.1.1 Data Types for Character Strings 4.1.2 Conversions 4.1.3 Character String Functions 4.2 Date and Time 4.2.1 Date Information 4.2.2 Time Information 4.2.3 Combined Time and Date Information 4.2.4 Processing Time and Date Values 4.2.5 Examples of Processing Time Values 4.3 Numerical Data 4.3.1 Basic Arithmetic Operations 4.3.2 Square Roots and Exponents 4.3.3 Logarithms 4.3.4 Rounding or Trimming 4.3.5 Trigonometry 4.3.6 Random Numbers 4.3.7 Sign 4.3.8 Quantities and Amounts 4.4 Binary Data Types 4.4.1 Conversion Between Binary Data, Hexadecimal Data, and Character Strings 4.4.2 Bits and Bytes 4.5 Conversions Between Data Types 5 Write Access to the Database 5.1 INSERT 5.1.1 Individual Data Records 5.1.2 Inserting Multiple Records Simultaneously 5.2 UPDATE 5.2.1 Simple UPDATE Statement 5.2.2 UPDATE Statement with Reference to Other Tables 5.3 UPSERT or REPLACE 5.3.1 Inserting or Updating Individual Data Records 5.3.2 Inserting or Updating Multiple Data Records 5.4 MERGE INTO 5.5 DELETE 5.6 TRUNCATE TABLE 6 Imperative Programming 6.1 Variables 6.1.1 Local Scalar Variables 6.1.2 Local Table Variables 6.1.3 Session Variables 6.1.4 Temporary Tables 6.2 Flow Control Using IF and ELSE 6.3 Loops 6.3.1 FOR Loop 6.3.2 WHILE Loop 6.3.3 Controlling Loop Passes 6.3.4 Exercise: Greatest Common Divisor 6.4 Cursors 6.4.1 FOR Loop via a Cursor 6.4.2 Open, Read, and Close Explicitly 6.5 Arrays 6.5.1 Generating an Array 6.5.2 Accessing the Array 6.5.3 Arrays as Local Variables 6.5.4 Splitting and Concatenating Arrays 6.5.5 Arrays and Table Columns 6.5.6 Bubble Sort Exercise 6.6 Transaction Control 6.6.1 Transactions 6.6.2 Autonomous Transactions 6.7 Executing Dynamic SQL 6.7.1 Parameters of Dynamic SQL 6.7.2 Input Parameters 6.8 Error Handling 6.8.1 What Are Exceptions? 6.8.2 Triggering Exceptions 6.8.3 Catching Exceptions 7 Creating, Deleting, and Editing Database Objects 7.1 Tables 7.1.1 Creating Database Tables 7.1.2 Changing Database Tables 7.1.3 Deleting Database Tables 7.2 Table Types 7.3 Views 7.4 Sequences 7.4.1 Increment 7.4.2 Limits 7.4.3 Behavior When Reaching the Limit 7.4.4 Resetting the Sequence 7.4.5 Changing and Deleting a Sequence 7.5 Triggers 7.5.1 Parameters 7.5.2 Per Row or Per Statement 8 SQLScript in ABAP Programs 8.1 AMDP Procedures 8.1.1 Creating AMDP Procedures 8.1.2 Generated Objects of an AMDP Method 8.1.3 Lifecycle of the Generated Objects 8.1.4 Two-Track Development 8.1.5 Using AMDP Procedures in AMDP Procedures 8.2 CDS Table Functions 8.2.1 Creating a CDS Table Function 8.2.2 Generated Objects of a CDS Table Function 8.2.3 Implicit Client Handling of CDS Table Functions 8.3 AMDP Functions for AMDP Methods 8.4 Alternatives to AMDPs for Calling SQLScript Code from ABAP Programs 8.5 Recommendations 9 SQLScript in SAP Business Warehouse 9.1 Transformation Routines as AMDP 9.2 Successive Transformations and Mixed Execution 9.3 The Generated AMDP Classes 9.3.1 Signature of AMDP Methods 9.3.2 Assigning the Output Tables 9.3.3 Access to Data from Other Data Models 9.4 The Individual Routines 9.4.1 Start Routines 9.4.2 End Routines 9.4.3 Expert Routines 9.4.4 Field Routines 9.5 Error Processing and Error Stack 9.5.1 Processing Flow in the Data Transfer Process 9.5.2 Example: Recognizing Incorrect Data in Table OUTTAB 9.5.3 Example: Finding Invalid Field Contents with Regular Expressions 10 Clean SQLScript Code 10.1 The Readability of the Code 10.1.1 Formatting the Code 10.1.2 Mnemonic Names 10.1.3 Granularity of Procedures and Functions 10.1.4 Comments 10.1.5 Decomposing Complex Queries 10.1.6 Readable SQLScript Statements 10.2 Performance Recommendations 10.2.1 Reducing Data Volumes 10.2.2 Avoid Switching Between Row and Column Engines 10.2.3 Declarative Queries 10.2.4 Scalar Functions 11 Tests, Errors, and Performance Analysis 11.1 Testing SQLScript Code 11.1.1 SQL Console 11.1.2 Testing ABAP Managed Database Procedure Methods 11.1.3 The TRACE Statement 11.2 The Debugger for SQLScript 11.2.1 The SAP HANA Web-Based Development Workbench Debugger 11.2.2 SQLScript Debugger in SAP HANA Studio 11.2.3 AMDP Debugger in the ABAP Development Tools 11.2.4 Debugging in the SAP Web IDE 11.3 Performance Analysis 11.3.1 Runtime Measurement 11.3.2 Execution Plan 11.3.3 Performance Analysis in the SAP HANA Web-Based Development Workbench 11.3.4 PlanViz 11.3.5 SQL Analyzer of the Database Explorer of the SAP Web IDE 11.3.6 SQLScript Code Analyzer Appendices A Data Model for Task Management A.1 Overview of the Tables A.2 Data A.3 Installation B List of Abbreviations C The Author Index Service Pages