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.

Watch this video to look at an example:

In the studied example, we want to find out which publisher has the most books:

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

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.

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.

SQLScript also adds extra data types to help address missing features from standard SQL - for example, spatial data types and text data types.

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:

Passing input parameters to the calculation view

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

Code Snippet

Copy codeSwitch to dark mode

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

Referring to the input parameter in SQL Script

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

Code Snippet

Copy codeSwitch to dark mode

1
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.

The IF statement

Code Snippet

Copy codeSwitch to dark mode

12345678910
IF <bool-expr1> 
THEN 
  {then-stmts1} 
{ELSEIF <bool-expr2> 
THEN 
  {then-stmts2}} 
{ELSE 
  {else-stmts3}} 
END IF 

The previous figure shows you an example of the IF statement, which consists of a Boolean expression, :found is NULL. If the expression is evaluated as true, then the statement CALL ins_msg_proc('result of count(*) cannot be NULL'); in the mandatory THEN block is executed. The IF statement ends with END IF. The remaining parts are optional.

If the Boolean expression :found is NULL does not evaluate as true, then the ELSE branch is evaluated. In most cases this branch starts with ELSE. The statement CALL ins_msg_proc('result of count(*) not NULL- as expected'); 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 can be evaluated. If It evaluates as true, the following statement 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.

The WHILE loop

Code Snippet

Copy codeSwitch to dark mode

123
WHILE <bool-stmt> DO
  {stmts}
END WHILE

The WHILE loop executes the statement stmts in the body of the loop as long as the Boolean expression at the beginning bool-stmt of the loop evaluates as true.

The FOR loop

Code Snippet

Copy codeSwitch to dark mode

123
FOR <loop-var> IN {REVERSE} <start>..<end> DO
  {stmts}
END FOR

The FOR loop iterates a range of numeric values – denoted by start and end in the syntax – and binds the current value to a variable (loop-var) in ascending order. Iteration starts with value start and is incremented by one until the loop-var is larger than end.

Therefore, if start is larger than end, the body loop will not be evaluated. For each enumerated value of the loop variable the statements in the body of the loop are evaluated. The optional keyword REVERSE specifies to iterate the range in descending order.