SQLScript basics

Syntax basics of the SQLScript language

(C) Brandeis Consulting.

Syntax basics of the SQLScript language

  • Statements
  • Whitespace
  • Comments
  • Literals
  • Identifiers
  • Local variables and parameters
  • Operators
  • Expressions
  • NULL
  • DUMMY
(C) Brandeis Consulting.

Statements

The SQLScript language consists of statements. Except for assignments, a statement begins with a keyword and ends with a semicolon.

DECLARE lv_var INT;
SELECT count(*) INTO lv_var FROM tasks ; 
lv_var = 1;
(C) Brandeis Consulting.

Nested statements

Some statements can themselves contain statements. For example, an IF statement forms a bracket around one or more statement blocks (more on this later).

1 IF lv_counter > 0 
2 THEN 
3 INSERT INTO colors VALUES ('purple');
4 END IF;

In the listing, the IF statement begins on line 1 and ends with the semicolon on line 4.
The INSERT statement starts and ends on line 3.

(C) Brandeis Consulting.

Whitespace

In SQLScript, whitespace is required only where it would otherwise not be possible to clearly separate consecutive key words, fields, variables, etc. However, it may also be inserted between the individual language elements everywhere else.

Whitespace is primarily useful for better readability.

(C) Brandeis Consulting.

Comments

Comments are parts of the source code that are completely ignored by the system. Thus, the content of a comment is used only by the human reader to understand the code. SQLScript distinguishes two variants of comments: line-end comments and block comments.

(C) Brandeis Consulting.

Block comments

Block comments start with a slash and an asterisk and end with an asterisk and a slash:

SELECT /* This is a block comment */ id FROM TASKS;

SELECT /* id,
          title,
          due_date, */
          assignee, 
          effort
  FROM tasks;

Block comments can go over several lines. They can also start in the middle of a line.

(C) Brandeis Consulting.

End-of-line comments

An end-of-line comment starts with a double hyphen. Anything written after that until the end of the line is not interpreted as an instruction. Parts of statements can precede the line-end comment.

SELECT COUNT(*) 
INTO lv_var --line end comment
FROM tasks ; 
(C) Brandeis Consulting.

ABAP comments

In AMDP procedures and functions the ABAP line comments can also be used. These start with a * at the first position of a line.

* SELECT COUNT(*) 
* INTO lv_var    
* FROM tasks ; 

Please avoid these ABAP comments, otherwise the AMDP code cannot be copied to the SQL console!

ABAP line end comments with quotation marks " cannot be used. This character always introduces the beginning of an identifier in special notation.

(C) Brandeis Consulting.

Literals

Literals represent constant values in source code that is entered directly. This can be used in various places, such as in assignments, as a field value, or as a comparison value in a condition.

Data types of literals

name format example
Strings In quotation mark 'Peter'
integers sequence of digits '123'
decimal numbers sequence of digits with decimal point 123.456
date prefix DATE DATE'2017-11-10'
time prefix TIME TIME'15:42:04'
timestamp prefix TIMESTAMP TIMESTAMP'2011-12-31 23:59:59.123'
(C) Brandeis Consulting.

Why are the data types of literals important?

Because otherwise the values must be converted into suitable types. This is usually done implicitly, but costs runtime. Where implicit conversion is not possible, errors occur.

--bad:
SELECT DAYS_BETWEEN( CURRENT_DATE, '2000-01-01') FROM DUMMY

--Good:
SELECT DAYS_BETWEEN( CURRENT_DATE, DATE'2000-01-01') FROM DUMMY

In BW transformations fields are e.g. emptied with ''. But this sets the type of this column as a string. This leads to error messages with numeric key figures.

SELECT plant,
       material,
       '' as recordmode,
       0 as amount,
      ...
  FROM ...
(C) Brandeis Consulting.

identifiers

Identifiers are names for objects in HANA such as tables, views and columns. These identifiers are always case sensitive. It is important how the source code is interpreted by the system. There are two different notations for this:

  • Special notation
  • Simple notation

Both notations can also be mixed arbitrarily within a statement.

(C) Brandeis Consulting.

Special notation

In the special notation the identifiers are delimited by quotes. Thereby all Unicode characters are allowed at any position. This means that spaces, special characters like period and comma as well as all other characters are allowed.

SELECT "ID",             -- ID
       "/BIC/BMERRCODE", -- /BIC/BMERRCODE
       "title"           -- title
       FROM "My table";  -- My table
(C) Brandeis Consulting.

Simple notation

In simple notation, the identifiers in the source text are without delimiters. Thus it is internally automatically converted to uppercase. In addition, the following restrictions apply to it:

  • It must start with a letter or an underscore.
  • It may only consist of the following characters
    • letters A-Z of the Latin alphabet, no umlauts
    • digits 0-9
    • underscore _
    • Dollar sign $
    • double cross #
SELECT id,         --ID
       status,     --STATUS
       title       --TITLE
       FROM tasks; --TASKS
(C) Brandeis Consulting.

Identifiers in BW

All identifiers (table names and column names) in DDic are in capital letters. That's why the simple notation is the best choice.

But for all field and table names with the namespace prefixes /BIC/ and /BI0/ the special notation is necessary.


tmp = SELECT calday, 
             plant,
             "/BIC/PFLX"
             "/BIC/PFOXL"
        FROM "/BIC/ADTSA0012";

For all self-assigned names, especially within the source code, only the simple notation should be used if possible.

(C) Brandeis Consulting.

SAP DWC resp. DataSphere

In the different editors of the Data Warehouse Cloud the notation is handled differently. In some cases, quotation marks are added subsequently.

In case of error situations, it is helpful to look at the generated code. There, the previously described notations apply.

(C) Brandeis Consulting.

Local variables

In SQLScript, local table variables and table parameters can be accessed within a SELECT statement in the same way as database tables. Thus, a distinction is necessary for read access so that the source code remains unique. For this purpose, a colon is placed in front of the corresponding variable.

CREATE PROCEDURE get_name(IN iv_id INT)
AS BEGIN

   tmp = SELECT id, 
                lastname, 
                firstname 
           FROM users;

   SELECT * 
     FROM :tmp 
    WHERE id = :iv_id; 
END;

The colon for tabular variables is only necessary and allowed if there is a danger of confusion with database objects.

(C) Brandeis Consulting.

Operators

Operators calculate a result from the operands. For example, you can concatenate two numbers to your sum using the plus (+) operator.

Arithmetic operators

...return a numeric result

  • Addition: +
  • Subtraction: -
  • Multiplication : *
  • Division : /
  • Negation: - as sign

String operators

  • Concatenate strings into a new string: ||

Comparison operators

  • Equal =
  • Unequal != or <>
  • Less than <
  • Greater than >
  • Greater than/equal to >=
  • Less than/equal to <=

Logical operators

  • AND
  • OR
  • NOT - TRUE becomes FALSE.
(C) Brandeis Consulting.

Evaluation order of the operators

Evaluation order of operators from top to bottom:

group operators
brackets
Arithmetic Operators Sign
Multiplication and Division
Addition and Subtraction
String Operators Concatenation
Comparison Operators All
Logical Operators NOT
AND
OR
(C) Brandeis Consulting.

Expressions...

...are a language construct which is evaluated in its context and thereby returns a value.
...can be used in different places of an SQL statement
...can contain expressions
...can have as result a scalar value or a table.

(C) Brandeis Consulting.

scalar values vs. tables

Scalar values store exactly one value, e.g. a number or a date.

Tables contain rows and columns. Also, the result of a SELECT query that returns exactly one row and one column is a table. These queries are called scalar queries.

Using a scalar query as a comparison value

``sql
select *
from tasks
where due_date = ( select max(due_date) from tasks )

# What are the scalar expressions?
- literals
- variable names
- column names
- Function calls
  - SQL functions - e.g. `SUBSTRING()`
  - Aggregate functions - e.g. `MAX()`
  - User Defined Functions (UDF)
- Operator expressions - linking expressions with operators
- `CASE` expression
- (Correlated) subqueries

# An example of different expressions
<div class="col">
 <div>


```sql
SELECT 
-- field name as expression
       id,
-- concatenation operation as expression
       firstname || ' ' || lastname AS name,
-- CASE expression.
       CASE sex
-- with function call as expression
          WHEN 'F' THEN NCHAR(9792)
          WHEN 'M' THEN NCHAR(9794)
          ELSE '' 
       END 
          AS MW,
-- function call as expression  
       COALESCE(team, 0) as team
   FROM users;

(C) Brandeis Consulting.

Where can I use expressions?

In all places where a value is needed, you can use expressions.

Example

SELECT *
  FROM tasks
 WHERE SUBSTRING(title, 3, 1) = 'a';
(C) Brandeis Consulting.

table expressions

Table expressions can be used in the FROM clause.

SELECT ... 
  FROM <table expression>

List of table expressions

  • Names of database tables or views
  • Table functions
  • Local table variables
  • Subqueries
SELECT avg(id) FROM (SELECT id FROM tasks
                     UNION
                     SELECT id FROM user )
(C) Brandeis Consulting.

Predicates

A predicate is an expression that is evaluated to TRUE, FALSE, or UNKNOWN. For example:

  • Comparison with a comparison operator (<, =, >, ....).
  • LIKE
  • BETWEEN
  • IS NULL
  • EXISTS
  • IN
  • ...
(C) Brandeis Consulting.

NULL

NULL is not a concrete value, but the absence of a value. In particular, it is not a valid initial value such as 0 or SPACE.

NULL is displayed in the data preview either as NULL or as a question mark.

NULL can never be compared in a meaningful way.
The result is always UNKNOWN !

(C) Brandeis Consulting.

An example for NULL

CREATE TABLE test_null( id INT,
                        name VARCHAR(10) );
INSERT INTO test_null VALUES(1, 'Peter');
INSERT INTO test_null VALUES(2, 'Paul');
INSERT INTO test_null VALUES(3, 'Petra');
INSERT INTO test_null VALUES(4, 'Andrea');
INSERT INTO test_null(id) VALUES(5);

SELECT id, name FROM test_null;
SELECT id, name as "LIKE P%" FROM test_null WHERE name LIKE 'P%';


(C) Brandeis Consulting.

Exercise task

  • Display all rows
    • SELECT id, name FROM test_null;
  • Find all rows where the name starts with P
    • SELECT id, name as "LIKE P%" FROM test_null WHERE name LIKE 'P%';
  • Find all rows where the name does not start with P
    • SELECT id, name as "NOT LIKE P%" FROM test_null WHERE name NOT LIKE 'P%';
      Row with NULL is not found ;-(
    • SELECT id, name as "= NULL" FROM test_null WHERE name = NULL;
      Finds nothing at all, because the comparison with NULL always produces UNKNOWN.
    • SELECT id, name as "IS NULL" FROM test_null WHERE name IS NULL;
      Only with the IS NULL predicate the selection of the row succeeds!
(C) Brandeis Consulting.

NULL in BW

In transformation routines the value of a characteristic may not be NULL in the output table!

(C) Brandeis Consulting.

Calculation with NULL

NULL is not a defined value. Therefore, the result of a calculation with NULL is also not a defined value.
A NULL value can infect complex expressions so that the final result is also NULL.

Simple examples

NULL + 5 = NULL
NULL || 'ABC' = NULL
(C) Brandeis Consulting.

NULL in predicates

No logical statement can be made about NULL. The condition
WHERE col1 = NULL;
is never fulfilled.

IS NULL

Only this predicate can filter out NULL!

(C) Brandeis Consulting.

Catch NULL

Replace NULL values with functions:

  • IFNULL( <value1>, <value2>)
  • COALESCE( <value1>, <value2>, ..., <valueN>)
(C) Brandeis Consulting.

The DUMMY table

In every SAP HANA system there is a table named DUMMY. This has a column named DUMMY and contains exactly one row with the value X. The content of DUMMY cannot be changed.

The table DUMMY is very useful for testing expressions or for creating empty tables with a fixed structure.

(C) Brandeis Consulting.

Examples of the use of DUMMY

Test of an expression:

select substring('ABCDEFG', 3, 2) FrOM DUMMY;

Creating an empty table with two columns:

errorTab = SELECT '' AS ERROR_TEXT,
                   '' AS SQL__PROCEDURE__SOURCE__RECORD
              FROM DUMMY 
              WHERE DUMMY = 'Y';

(C) Brandeis Consulting.

Another example...

lt_fiscper3 = SELECT '001' AS fiscper3 FROM DUMMY UNION ALL
              SELECT '002' AS fiscper3 FROM DUMMY UNION ALL
              SELECT '003' AS fiscper3 FROM DUMMY UNION ALL
              SELECT '004' AS fiscper3 FROM DUMMY UNION ALL
              SELECT '005' AS fiscper3 FROM DUMMY UNION ALL
              SELECT '006' AS fiscper3 FROM DUMMY UNION ALL
              SELECT '007' AS fiscper3 FROM DUMMY UNION ALL
              SELECT '008' AS fiscper3 FROM DUMMY UNION ALL
              SELECT '009' AS fiscper3 FROM DUMMY UNION ALL
              SELECT '010' AS fiscper3 FROM DUMMY UNION ALL
              SELECT '011' AS fiscper3 FROM DUMMY UNION ALL
              SELECT '012' AS fiscper3 FROM DUMMY UNION ALL
              SELECT '013' AS fiscper3 FROM DUMMY UNION ALL
              SELECT '014' AS fiscper3 FROM DUMMY UNION ALL
              SELECT '015' AS fiscper3 FROM DUMMY UNION ALL
              SELECT '016' AS fiscper3 FROM DUMMY ;

(C) Brandeis Consulting.