This SQLScript Overview is the perfect complement to the training of Brandeis Consulting

Syntax, examples and descriptions, matching the contents of the SQLScript basic training at Brandeis Consulting. This overview shows only the most important aspects. For details, please follow the links to the reference. If there are several identical concepts (e.g. identical SQL functions), only one of them is usually described here. The text only includes links if there are relevant additional information behind it. Examples always refer to the English demo data model for the book.

Notation in code

• [Square Brackets] – Optional elements
• UPPERCASE – Keywords and SQL functions
• lowercase – Identifiers
• <AngleBrackets> – descriptive elements. Abbreviations are explained in the text
• {Repetitions}... are optional and are written in curly brackets with 3 dots.

Under the line are references, for example

• Book p. – Refers to the page number of the 2nd, german edition SQLScript für SAP HANA . The 2. english edition will follow this year…
• SAP Documentation – A link to the corresponding chapter of the latest SAP documentation from SAP HANA on Premise! Not Cloud!

Identifier

Table or column identifiers are case ensitive. This is not noticeable with the identifiers in simple notation without quotes, since they are always implicitly converted to UPPERCASE. There is no difference between the names ColumX, CoLuMx and COLUMX . Internally, all three are COLUMX interpreted as. Only ASCII letters, digits and the characters #_ are allowed.

Names in special notation are included in double quotes. This will interpret you exactly as they appear. The three names "ColumX", "CoLuMx" and "COLUMX" are different. All Unicode characters are allowed between the double quotes.

Book p. 61 SAP Documentation

Comments

Line-end comments are introduced with two hyphens -- and continue until the line break.

Block comments start with /* and end with */ . They can extend within a line or over several lines.

ABAP comments with * at the first position only work in AMDP and should therefore not be used.

Book p. 59 SAP Documentation

Scalar expressions

Deliver exactly one elementary value, such as a date, string, or number.

• Fieldnames
• Operator Expressions
• scalar variables or parameters
• Literals
• CASE expressions
• scalar subqueries in parentheses
• SQL Functions and UDF Functions

Book p. 69 SAP Documentation

Literals

Literals are constant values in SQLScript code.

Book p. 61 ff. SAP Documentation

SQL Functions

The HANA database comes with hundreds of SQL functions for calculating data. The most important ones can be found on this page for the respective data types:

• Time data types
• Strings
• Numeric data types

Book p. 167 ff. SAP Documentation

CASE Expressions

A CASE expression returns a scalar value. There are two variants of CASE expressions.
The simple CASE expression compares an expression to several other expressions for equality:

SELECT id, 
       CASE status
         WHEN 1 THEN 'Neu'
         WHEN 2 THEN 'In Bearbeitung'
         ELSE 'Nicht relevant'
       END AS status_text
  FROM tasks;

The complex CASE expression evaluates N independent predicates. The first to TRUE be evaluated yields the result:

SELECT CASE 
         WHEN status NOT IN (1, 2, 3) 
           THEN 'Open'
         WHEN due_date < current_date 
           THEN 'Critical'
         ELSE 'Others' END AS statustext
  FROM tasks;

If no true comparison or predicate is found, either the value is returned from the ELSE clause or NULL

Book p. 119 SAP Documentation

Table Expressions

An expression that returns a table. These can be:

• Names of DB tables or views
• Table Expressions combined with JOINs
• Table variables
• Table subqueries
• SELECT queries combined with set operators

Book p. 59

SELECT

The SELECT statement defines a table. The columns are created with the field list, while the corresponding rows result from the other clauses.

Syntax:

SELECT [TOP <number>]  
  [DISTINCT] <FieldList>
  FROM <TableExpression> 
  [WHERE <Predicate>] 
  [GROUP BY <ExpressionList>] 
  [<SetOperation>] 
  [ORDER BY <ExpressionList>] 
  [LIMIT <NumberOfLines>[OFFSET <Offset>]]

The FROM clause describes the source of the data

FROM <TableExpression> [[AS] <Alias>]

If necessary, there is a further table expression with JOIN ON.

The WHERE clause filters the data. Only records for which a predicate evaluates to TRUE are added to the result set of the query.

Book p. 114 SAP Documentation

Field List

Definition of the columns of the query.

<ScalarExpression1> [[AS] <Alias1> ] [ { ,
<ScalarExpression2> [[AS] <Alias2>] } ... ]

Multiple columns are separated by commas. All columns of the sources are addressed with *.

SELECT *, 
       LEFT(coarea, 2) AS country, 
       amount * 1,16   AS net_amount, 
       CASE LEFT(coarea,2) 
         WHEN 'DE' THEN '1' 
         WHEN 'FR' THEN '2' 
         ELSE '9' 
       END AS ccode, 
  FROM <TableExpression>

Book p. 116 SAP Documentation

JOIN

One JOIN is used to create a combination of multiple tables. The ON condition defines which rows from the participating tables are combined in a row of the query result.

SELECT ...
  FROM <TableExpression1>
  <JoinType> JOIN <TableExpression2>
  ON <JoinPredicate>;

The CROSS JOIN is the only join type without a ON condition. It is the Cartesian product of two tables.

As a INNER JOIN result, only rows that have found a partner on the other side.

If no partner is found in an OUTER JOIN, the NULL value is populated.

Book p. 134 SAP Documentation

Subqueries

Scalar subqueries provide exactly one row and one column, for example in field lists or for comparison. More than one result row results in a runtime error.

SELECT assignee,
       id,
       due_date,
      (SELECT lastname
         FROM users AS u
        WHERE o.assignee = u.id)
        AS assignee_name
  FROM tasks AS o
 WHERE  o.due_date =
           (SELECT MAX(due_date)
              FROM tasks AS i
             WHERE o.assignee = i.assignee )

Column subqueries return multiple values in exactly one column. They are used for quantity comparison with IN.

SELECT * 
FROM tasks
WHERE
   status IN (SELECT id
                FROM status
               WHERE is_final = true)

Table subqueries provide a table that can be used as a table expression in the FROM clause. They can be elegantly replaced with table variables.

SELECT category,
       COUNT(*)
  FROM ( SELECT
          CASE
            WHEN status IN (1, 2)
            AND due_date < current_date
               THEN 'Critical'
            WHEN status IN (1, 2)
               THEN 'Open'
            ELSE 'OK' END AS category
         FROM tasks)
 GROUP BY category;

Book p. 159 ff. SAP Documentation

Predicates

Predicates are logical expressions that have the value TRUE, FALSE or UNKNOWN . In WHERE clauses, ON clauses, or when evaluating conditions in CASE expressions, it is only relevant whether a predicate evaluates to TRUE or not.

Predicates can be combined with the logical operators NOT , AND and OR. Brackets increase the readability enormously here!

The main predicates are:

• Comparisons:
  <Expression1> <Operator> <Expression2>
• IS NULL – The only predicate that can determine NULL values
• EXISTS – Evaluated TRUE when the subquery returns at least one row.
• Set comparisons with IN, ANY, SOME or ALL
• LIKE and LIKE_REGEXPR – Search for patterns, also possible with regular expressions.

Book p. 71 SAP Documentation

EXISTS Predicate

The EXISTS quantor checks whether a subquery returns a result or not.

SELECT DISTINCT assignee
  FROM tasks AS t 
 WHERE NOT EXISTS (
      SELECT id
      FROM projects AS p
      WHERE p.project_manager= t.assignee );

Book p. 146 SAP Documentation

Table Variables

Table variables are usually declared by assignment and populated with data. Because they represent a table expression with a leading colon, table variables can be used within a SELECT query in the same way as a DB table. You can also think of a table variable as a view, which also describes their role in execution quite well.

DO BEGIN
  lt_tmp = SELECT id, 
                  title,
                  assignee 
             FROM :tasks;
   SELECT *
     FROM :lt_tmp; 
END;

Book p. 112 SAP Documentation

Anonymous Blocks

An Anonymous Block is a procedure that is not stored under a name in the DB. Instead, the entire code is passed from the application or console to the DB.

DO BEGIN
  <SourceCode>
END;

Book p. 81 ff. SAP Documentation

UDF Functions

User Defined Functions (UDF) are sub-programs that represent an expression, either

• Scalar expressions or
• Table expressions

They are often created via WebIDE, AMDP, or the HANA XS repository. This is the SQL way:

CREATE FUNCTION <FunctionName> 
  [(<ParameterList>)]
  RETURNS <ParameterDefinition>
  [LANGUAGE SQLSCRIPT] 
  [SQL SECURITY {DEFINER|INVOKER} ] 
  [DEFAULT SCHEMA <DefaultSchema>] 
  [DETERMINISTIC] 
AS BEGIN 
  <SourceCode>
END

Book p. 91 SAP Documentation

Aggregation

Aggregation reduces the number of rows. The expression list in the GROUP BY clause determines the granularity of the query result because a row is formed for each existing combination.

Columns that do not appear in the GROUP BY clause must be summarized with an aggregate function, such as.B. with MIN() or SUM(). Typical query:

SELECT assignee, 
       status, 
       SUM(effort)
  FROM tasks
  GROUP BY assignee, 
           status;   

Book p. 123 SAP Documentation

Procedures

Procedures are sub-programs in SQLScript. They are often created through a development environment such as WebIDE, Eclipse-HANA repository, or AMDP. Direct creation via SQL is also possible:

CREATE [OR REPLACE] PROCEDURE <ProcedureName> 
     [(<ParameterList>)] 
     [LANGUAGE {SQLSCRIPT|RLANG} ] 
     [SQL SECURITY {DEFINER|INVOKER} ] 
     [DEFAULT SCHEMA <DefaultSchema>] 
     [READS SQL DATA] 
     [WITH ENCRYPTION] 
AS 
BEGIN [SEQUENTIAL EXECUTION]
    <SourceCode>
END

Book p. 82 SAP Documentation

NULL

Actually, NULL it is not a value, but a symbol that represents the absence of a value. A comparison with NULL always results UNKNOWN. Each calculation with NULL results in the NULL.

Only the IS NULL predicate can filter NULL in a column. To handle it in expressions, there are the two SQL functions:

• IFNULL( <Expression>, <Fallback> )
• COALESCE( <Expression1> {, <Expression2>} ...)

Typical causes of NULL values

• OUTER JOINs
• CASE without ELSE branch
• NULL in DB tables
• The NULLIF() SQL function

Book p. 73 SAP Documentation

The table DUMMY

This table cannot be changed and contains exactly one column named DUMMY and a row that holds the value X. The table is useful for testing scalar expressions:

SELECT <Expression> FROM DUMMY;

Or for the construction of fixed table variables:

lt_year = SELECT '2020' AS year FROM DUMMY 
          UNION ALL
          SELECT '2021' AS year FROM DUMMY;

Book p. 76

UNION ALL and Set Operators

SELECT queries with a compatible column structure can be combined with the following operators:

• UNION ALL – The union of two tables.
• UNION – dito, but is slower because it eliminates duplicates
• INTERSECT – Forms the intersection, which can alternatively be realized with
  INNER JOIN
  one.
• EXCEPT or MINUS – Is a Set-Subtraction that can alternatively be implemented with the EXISTS predicate.

These operators, except UNION ALL , consider the rows to be elements of a set. The elements are identical if all fields are identical, i.e. there are no “key fields” here. The specified alternatives are suitable if you do not want to perform the operations on all columns.

Book p. 157 SAP Documentation

Time data types

The following data types can be used for points in time (<PIT>).

SQL Functions

The unit of time (<UOT>) in the functions is either SECOND, DAY, MONTH or YEAR .

Book p. 191 SAP Documentation

Conversion between time and character strings

TO_VARCHAR(<PIT> [, Format])
Conversion of the point in time (<PIT>) to a character string.

TO_<TDT>(<String> [, Format])
Conversion string to time data type (<TDT>).

Symbols for formatting

In addition to the symbols, delimiters can also be used:

SELECT TO_VARCHAR(CURRENT_TIME, 
                  'HH24.MI.SS') 
       TO_DATE('DEC-29-20', 
               'MON-DD-YY')
FROM DUMMY;

Book p. 193 SAP Documentation

Character Strings

Unlike ABAP, spaces are not automatically removed at the end! The operator || concatenates two strings.

Types

SQL Functions

There are also some of the functions available in a variant for use with regular expressions:

• LOCATE_REGEXPR( )
• OCCURRENCES_REGEXPR( )
• REPLACE_REGEXPR(<Pattern> IN <Str> WITH <Replacement>)
• SUBSTRING_REGEXPR(<Pattern> IN <Str> [GROUP <Group>])

Book p. 82 SAP Documentation

Numeric data types

In contrast to ABAP, an assignment is not automatically rounded off commercially! If the target format does not fit, it is truncated. 😥

SQL Functions

And many more in the SAP documentary…

Book p. 207 SAP Documentation