Working with SQLScript

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:

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);

You could 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 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 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.

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:

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

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

Sales with tax = Sales Revenue * :Tax_Amount 

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