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!

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.


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.


Book p. 69 SAP Documentation

Literals

Literals are constant values in SQLScript code.

LabelFormatExample
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. 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:

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:


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 info@brandeis.de.
(C) Brandeis Consulting GmbH

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 Predikate] 
  [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 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 *.

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

Joins

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

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

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

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

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

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. 82SAP 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.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:

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

Time data types

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

TypeStandard
DATE'YYYY-MM-DD'
TIME'HH24-MI-SS'
SECONDDATE'YYYY-MM-DD HH24-MI-SS'
Timestamp'YYYY-MM-DD HH24-MI-SS.FF7'

SQL Functions

SQL functionDescription
CURRENT_DTLocal time for the data type (DT)
CURRENT_UTCDTdito 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

UnitSymbolDescription
YearYYYYyear, 4 digits
 YYYear, 2 Digits
QuarterQNumeric
MonthMMNumeric, 2-digit
 MONTHName in EN
 MONabbreviation in EN
 RMRoman spelling
WeekWW. in the month
 WWW. in the year, not ISO!!!
DayDNumeric
 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:

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

Data typeDescriptionMax. Length
NVARCHAR(N)Unicode String5000
VARCHAR(N)ASCII String5000
ALPHANUM(N)Alphaconverted127
CLOB, NCLOBLarge Strings2GB

SQL Functions

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

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

SQL Functions

SQL functionDescription
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
FLOOR(Num)

And many more in the SAP documentary…


Book P.207SAP Documentation

This post is also available in: Deutsch