Cheat Sheet - The SAP HANA SQLScript Overview

Cheat Sheet - The SAP HANA SQLScript Overview

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!


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


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.

Book p. 69 SAP Documentation


Literals are constant values in SQLScript code.

ASCII StringsIn quotes'Peter'
Unicode StringsIn quotes with an N as a prefixN'Jörg'
IntegersSequence of digits123
DecimalsNumber sequence with decimal point123.456
Floating-point numbersMantisse and exponent, separated by an E1.23E+04
DatePrefix DATEDATE'2017-11-10'
TimePrefix TIMETIME'15:42:04.123'
TimestampPrefix TIMESTAMPTIMESTAMP'2011-12-31 23:59:59'

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:

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:

       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:

         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:

Book p. 59

Brandeis Consulting

Schulungen und Beratung vom Autor des Buches SQLScript für SAP HANA. Bei Fragen zur SQLScript, CDS ABAP oder unseren Schulungen einfach eine Mail an schicken.

(C) Brandeis Consulting GmbH


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


SELECT [TOP <number>]  
  [DISTINCT] <FieldList>
  FROM <TableExpression> 
  [WHERE <Predicate>] 
  [GROUP BY <ExpressionList>] 
  [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 *.

       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


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.

  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


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,
      (SELECT lastname
         FROM users AS u
        WHERE o.assignee =
        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.

FROM tasks
   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,
            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 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.

  FROM tasks AS t 
      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.

  lt_tmp = SELECT id, 
             FROM :tasks;
     FROM :lt_tmp; 

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.


Book p. 81 ff. SAP Documentation

UDF Functions

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

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

  RETURNS <ParameterDefinition>
  [DEFAULT SCHEMA <DefaultSchema>] 

Book p. 91 SAP Documentation


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, 
  FROM tasks
  GROUP BY assignee, 

Book p. 123 SAP Documentation


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:

     [DEFAULT SCHEMA <DefaultSchema>] 

Book p. 82 SAP Documentation


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

  • 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:


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
  • 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

SQL functionDescription
CURRENT_<DT>local time for the data type (<DT>)
CURRENT_UTC<DT>dito with coordinated world time
ADD_<UOT>S(<PIT>, <distance> )adds the distance (+/-) in the Unit of time (<UOT>) to the point in time (<PIT>)
<UOT>S_BETWEEN(<PIT1>, <PIT2>)distance of the points in time in the unit of time.
TC(<PIT>)time component (<TC>) as INTEGER
ISOWEEK(<PIT>)calendarWeek ISO, e.B. 2021-W12
WEEK(<PIT>)calendarWeek US, num.
WEEKDAY(<PIT>)numeric: Mo=0, Sun=6
QUARTER(<PIT>,[<offset>])quarter, if necessary <offset> for differerent fiscal year
LOCALTOUTC(<PIT>, [<TimeZone>])local time to <UTC>
UTCTOLOCAL(<PIT>, [<TimeZone>])<UTC> to local time

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

YearYYYYyear, 4 digits
YYyear, 2 digits
MonthMMnumeric, 2-digit
MONTHname in EN
MONabbreviation in EN
RMroman spelling
WeekWW. in the month
WWW. in the year, not ISO!!!
DDnumeric, 2-digit
DAYname in EN
DYabbreviation in EN
HourHH1212h time without AM/PM
HH24hours (0-23)
AM / PMmorning or afternoon
MinuteMInumeric, 2-digit
SecondSSnumeric, 2-digit
SSSSSsec. after midnight
FF[1..7]digits of sec. N-digit

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


Book p. 193 SAP Documentation

Character Strings

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


Data typeDescriptionMax. Length
NVARCHAR(N)Unicode String5000
CLOB, NCLOBLarge Strings2GB

SQL Functions

SQL FunctionDescription
ABAP_LOWER(<Str>)Conversion of the string to lowercase / uppercase letters
INITCAP(<Str>)Captialize the first character of each word
LEFT(<Str>, <length>)Left/Right part of the string with the length
RIGHT(<Str>, <length>)
SUBSTR(<Str>, <Pos>, <length>)Part of the string
SUBSTR_BEFORE(<Str1>, <Str2>)Part of String1 before/after String2
SUBSTR_AFTER(<Str1>, <Str2>)
LOCATE(<Str1>, <Str2>)Position of String2 in String1
REPLACE(<Str1>, <Str2>, <Str3>)Replaces String2 in String1 with String3
LPAD(<Str>, <length> [<Pattern>])Filling left/right with pattern up to length
RPAD(<Str>, <length> [<Pattern>])
ABAP_ALPHANUM(<Str>, <length>)Alpha conversion
LTRIM(<Str> [,<CS>])Remove the character set (<CS>) from left/right
RTRIM(<Str> [,<CS>])

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

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

DEC(p,s)Fixed-point number
DECDecimal Float
RealBinary Float

SQL Functions

SQL functionDescription
ROUND(<Number>, <Digits>)Commercial rounding
NDIV0(<Number>, <N>)Division of number by N, 0 if N=0
RAND()Random number between 0 and 1
ABS(<Number>)Absolute value
SIGN(<Number>)Sign 1 or -1
MOD(<Number>, <N>)Division remainder of number/N
FLOOR(<Number>).... or down on INTEGER

And many more in the SAP documentary…

Book p. 207 SAP Documentation