Working with SQLScript

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Describe the additional features provided by SQLScript compared to standard SQL

SQLScript Capabilities

SQLScript is a database language developed by SAP that is based on standard SQL, but adds additional capabilities to exploit the advanced features of SAP HANA Cloud. The figure, SQLScript: Concept, lists some of the key features of SQLScript.

Note

The version of standard SQL that SQLScript is based on is the popular ANSI-92 version which is supported by nearly all databases.

Additional features of SQLScript include working with SAP HANA Cloud column tables, passing parameters between calculation views and SQLScript, working with spatial data types, and predictive analysis libraries.

SQLScript Extends SQL

In traditional client-server approaches, the business logic is defined and executed in the application server using application programming languages such as ABAP or C++. With SAP HANA Cloud, much of this logic can now be defined using SQLScript which allows the developer to define and execute all data processing tasks in the database in order to achieve improved performance.

SQLScript allows you to use variables to break a large complex SQL statement into smaller, simpler statements. This makes the code much easier to understand. It also helps with SQL HANA Cloud’s performance because many of these smaller statements can be run in parallel.

Let’s have a look at an example where we want to find out which publisher has the most books:

Code snippet
books_per_publisher = SELECT publisher, COUNT (*) AS num_books FROM BOOKS GROUP BY publisher;

publisher_with_most_books = SELECT * FROM :books_per_publisher WHERE num_books >= (SELECT MAX (num_books) FROM :books_per_publisher);
Copy code

You normally write this example as a single SQL statement using nested sub-queries. However, in this example we break things up into two smaller SQL statements by using a table variable to store the interim result. Notice how the table variable does not even need to be declared in advance; its definition is taken from the output result.

The first statement counts the number of books each publisher has and stores the entire result set into the table variable called books_per_publisher.

This variable, containing the entire result set, is then read twice in the second statement, first in the sub-query and then in the main query.

Notice that the table variable is prefixed in SQLScript with a colon (‘:’) to indicate that this is used as an input variable. All output variables just have the name, and all input variables have a colon prefix.

The second statement uses :books_per_publisher as inputs, and uses a nested SELECT.

The SQLScript compiler and optimizer determine how to best execute these statements, whether by using a common sub-query expression with database hints or by combining this into a single complex query. The code becomes easier to write and understand, more maintainable, and developer productivity increases.

By breaking the SQLScript into smaller statements and filling table variables, you also mirror the way in which you have learned to build your calculation views. Just like when you start building calculation views in layers, starting from the bottom up, you do the same with your SQLScript code. Keep in mind that the precise sequence of your SQLScript steps is not necessarily the runtime order, because the optimizer will always decide on the best execution plan. However, the optimizer will never alter your desired outcome. This is also true for calculation views.

SQLScript Data Types

SQLScript also adds extra data types to help address missing features from standard SQL; for example, spatial data types and text data types. These data types are listed in the figure, ANSI-92 and SQLScript Data Types.

The Use of Variables

The use of variables in SQL statements is not specified by the ANSI-92 SQL. In SAP HANA Cloud, SQLScript implements the concept of variable as an extension to ANSI-92 SQL.

Variables can be declared, assigned, and reused within a script.

Using Variables in SQLScript

  • Declare a variable

    DECLARE <variable_name> <type> [NOT NULL] [= <value>]

    Example 1: DECLARE a int;

    Example 2: DECLARE b int = 0; (the value is assigned when creating the variable)

  • Assign a variable (define the value of a variable)

    <variable_name> = <value> or <expression>

    Example 1: a = 3;

    Example 2: b = select count(*) from baseTable;

  • Use a variable in an expression

    :<variable_name> (with a colon) returns the current value of the variable

    Example: b = :a + 5; (assigns the value of a + 5 to b)

Processing Input Parameters Defined in Calculation Views with SQLScript

When you use a select statement in SQLScript to query a calculation view that has input parameters, you use the PLACEHOLDER keyword with the name of the input parameter wrapped in $$ symbols, and the value you want to pass to the input parameter. For example:

Using Input Parameters in SQLScript

Use the PLACEHOLDER keyword to pass input parameters to the calculation view in a select statement:

Code snippet
SELECT "PRODUCT", sum("TAXED_AMOUNT")
      FROM "_SYS_BIC"."MyPackage/SalesResults" 
       ('PLACEHOLDER' = ('$$Tax Amount$$', '17.5')) 
GROUP BY "PRODUCT"  
Copy code

Within SQLScript you refer to the input parameter using a colon:;

Code snippet
Sales with tax = Sales Revenue * :Tax_Amount 
Copy code

It is also possible to pass output parameters generated from an SQLScript, into a calculation view using the parameter mapping feature.

Looping and Conditional Logic

In general, SQL code is written in a declarative way. This means you state the intent of the action and do not dictate how the data processing steps should be carried out in a particular order. We do this so that you allow the SQL optimizer to figure out the best execution plan based on many dynamic factors at run-time.

However, sometimes you need to control the flow logic of the SQL so that steps are performed in a particular order. For example, when you need to perform a step only if a certain condition is met, or you need to loop a number of times. In these cases, you will use imperative logic.

This is (mostly) not available in ANSI SQL, and therefore SAP HANA provides declarative logic language as part of SQLScript to provide flow control.

A lack of imperative logic language is what often causes the developer to move the processing of data to the application layer, When an application runs on HANA, you really should push as much data processing to the database. SAP HANA SQLScript imperative logic enables this so less data handling needs to be done in the application layers and more can be done in the database.

Hint
Remember that imperative logic cannot have the same performance as declarative logic. This is because the SQL optimizer is not free to decide on the sequence of steps and has to take care to follow your logic, which might destroy optimizations. If you require the best performance, try to avoid imperative logic.

The figure, The IF Statement, shows an example of this statement, which consists of a Boolean expression bool-expr1. If this expression evaluates to true, then the statement then-stmts1 in the mandatory THEN block is executed. The IF statement ends with END IF. The remaining parts are optional.

If the Boolean expression bool-expr1 does not evaluate to true, then the ELSE branch is evaluated. In most cases this branch starts with ELSE. The statement else-stmts3 is executed without further checks. After an else branch, no further ELSE branch or ELSEIF branch is allowed.

Alternatively, when ELSEIF is used instead of ELSE, another Boolean expression bool-expr2 is evaluated. If it evaluates to true, the statement then-stmts2 is executed. You can add an arbitrary number of ELSEIF clauses in this way.

This statement can be used to simulate the switch-case statement known from many programming languages.

WHILE and FOR Loops

Save progress to your learning plan by logging in or creating an account