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;

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.

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.

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.

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

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

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.

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.

Evaluation order of the operators

Evaluation order of operators from top to bottom:

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;

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 )

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.

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!

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

Catch NULL

Replace NULL values with functions:

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

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