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 S. – 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.


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.

Label Format Example
ASCII Strings In Quotes ‘Peter’
Unicode Strings In quotes with an N as a prefix N’Jörg’
Integers Sequence of digits 123
Decimals Number sequence with decimal point 123.456
Floating-point numbers Mantisse and exponent, separated by an E 1.23E+04
Date Prefix DATE DATE’2017-11-10′
Time Prefix TIME TIME’15:42:04.123′
Timestamp Prefix TIMESTAMP TIMESTAMP’2011-12-31 23:59:59′

Book p. 61SAP 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. 167ffSAP 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

SQLScript Übersicht

Brandeis Consulting

Training and advice from the author of the book SQLScript for SAP HANA.
If you have questions about SQLScript and training, simply email
(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 Predikate] 
  [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 one.

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

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. 134SAP 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 S.159ffSAP 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.

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 S.146SAP Documentary

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. 91SAP 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 S.123 SAP Documentation


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. 82SAP 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.73SAP 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. 157SAP Documentation

Time data types

The following data types can be used for Points in time (PIT).

Type Standard
Timestamp 'YYYY-MM-DD HH24-MI-SS.FF7'

SQL Functions

SQL function Description
CURRENT_DT Local time for the data type (DT)
CURRENT_UTCDT dito with coordinated world time
ADD_UOTS(PIT, distance) adds the distance (+/-) in the Unit of Time (UOT) to the point in time (PIT)
UOTS_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 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

Unit Symbol Description
Year YYYY year, 4 digits
  YY Year, 2 Digits
Quarter Q Numeric
Month MM Numeric, 2-digit
  MONTH Name in EN
  MON abbreviation in EN
  RM Roman spelling
Week W W. in the month
  WW W. in the year, not ISO!!!
Day D Numeric
  DD Numeric, 2-digit
  DAY Name in EN
  DY abbreviation in EN
Hour HH12 12h Time without AM/PM
  HH24 Hours (0-23)
  AM | PM morning or afternoon
Minute MI Numeric, 2-digit
Second SS Numeric, 2-digit
  SSSSS Sec. 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 type Description Max. Length
NVARCHAR(N) Unicode String 5000
VARCHAR(N) ASCII String 5000
ALPHANUM(N) Alphaconverted 127
CLOB, NCLOB Large Strings 2GB

SQL Functions

SQL Function Description
LENGTH(Str) Length
ABAP_LOWER( Str) Conversion of the string to lowercase / uppercase letters
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
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. 😥

Type Description
Int Integer
DEC(p,s) Fixed-point number
DEC Decimal Float
Real Binary Float

SQL Functions

SQL function Description
ROUND(Num, Digits) Commercial rounding
NDIV0(Num, N) Division of Num by N, NULL if N=0
RAND() Random number between 0 and 1
ABS(Num) Absolute value
SIGN(Num) Sign 1 or -1
MOD(Num, N) Division remainder of Num/N
CEIL(Num) Up/down on INTEGER

And many more in the SAP documentary…

Book P.207SAP Documentation

This post is also available in: Deutsch