How to Execute SAP HANA SQL Expressions – Learn in Few Minutes!

After completing SAP HANA SQL synonyms, we will subsequently move on to learn SAP HANA SQL expressions. We will be discussing the SQL expressions supported and used in SAP HANA. We will cover all four major types of SQL expressions used in SAP HANA.

Without wasting any time, let’s start our tutorial.

SAP HANA SQL Expressions

Expressions in any scripting language are used to evaluate a clause to produce the desired output i.e. return values. The general syntax of an SQL expression used in SAP HANA is given below. It is represented in BNF form.

Syntax:

<expression> ::= <case_expression>
| <function_expression>
| <aggregate_expression>
| (<expression> )
| ( <subquery> )
| - <expression>
| <expression> <operator> <expression>
| <variable_name>
| <constant>
| [<correlation_name>.]<column_name>

The four types of SQL expressions that SAP HANA supports are:

1. Case Expressions

We use the case expressions to pass multiple condition statements having a conditional clause like IF, ELSE, THEN in a SQL statement. Using case expression, we can evaluate a statement having multiple conditions and provide the output as per the situation.

Below is the BNF representation of a case expression and its two types; simple case expression and search case expression.

Syntax:

<case_expression> ::= <simple_case_expression> | <search_case_expression>

<simple_case_expression> ::=
CASE <expression>
WHEN <expression> THEN <expression>
[{ WHEN <expression> THEN <expression>}…]
[ ELSE <expression>]
END

<search_case_expression> > ::=
CASE
WHEN <condition> THEN <expression>
[{ WHEN <condition> THEN <expression>}…]
[ ELSE <expression>]
END

<condition> ::= <condition> OR <condition> | <condition> AND <condition> | NOT <condition> | ( <condition> ) | <predicate>

If the input matches the expression following WHEN statement, then the expression following THEN statement returns as output. If not, then the expression following ELSE statement returns.

2. Function Expressions

The function expressions in SAP HANA scripting includes all the SQL built-in functions that you can use as expressions in SQL scripts.

Syntax:

<function_expression> ::= <function_name> ( <expression> [{, <expression>}...] )

3. Aggregate Expressions

The aggregate expressions in SAP HANA essentially use aggregate functions to carry out complex calculations. Aggregate expressions evaluate single values from a set of multiple values taken from multiple rows of a data table. You can also sort the order of the values.

The default order of returning the values is in the form of ascending order. In this type of order, NULL values returns first followed by greater values. You can change the ordering to descending.

Syntax:

<aggregate_expression> ::= COUNT(*) | COUNT ( DISTINCT <expression_list> ) | <agg_name> ( [ ALL | DISTINCT ] <expression> ) | STRING_AGG ( <expression> [, <delimiter>] [<aggregate_order_by_clause>])

<agg_name> ::= CORR | CORR_SPEARMAN | COUNT | MIN | MEDIAN | MAX | SUM | AVG | STDDEV | VAR | STDDEV_POP | VAR_POP | STDDEV_SAMP | VAR_SAMP

<delimiter> ::= <string_constant>

<aggregate_order_by_clause> ::= ORDER BY <expression> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST]

Commonly used aggregate functions in aggregate expressions are:

• SUM – Returns the sum of the aggregate expression.
• AVG – Returns the arithmetic mean of the aggregate expression.
• MIN – Returns the minimum value of the aggregate expression.
• MAX – Returns the maximum value of the aggregate expression.
• MEDIAN – Returns the statistical median of the set of values given in aggregate expression as an input column.
• COUNT – Returns the total number of rows evaluated in the aggregate expression.
• CORR – Returns the result of the Pearson product momentum correlation coefficient between two columns specified in the expression.
• CORR_SPEARMAN – Returns the Spearman’s rank correlation coefficient of the values present in the corresponding rows of two columns specified in the expression.
• STDDEV – Returns the standard deviation of the aggregate expression in terms of the square root of the VAR function.
• STDDEV_POP – Returns the standard deviation of the aggregate expression in terms of the square root of the VAR_POP function.
• STDDEV_SAMP – Returns the standard deviation of the aggregate expression in terms of the square root of the VAR_SAMP function.
• VAR – Returns the variance value of the aggregate expression in terms of the square of the standard deviation value.
• VAR_POP – Returns the population variance of the given aggregate expression.
• VAR_SAMP – Returns the sample variance of the given aggregate expression.
• STRING_AGG – Returns the concatenated string of the input given in the expression.

4. JSON Object Expressions

The JSON object expressions used in SAP HANA generate a JSON object. A typical JSON object has two elements; Key and Value. A “<key>” is a string literal which must be always enclosed in double quotes. And a <value> is an expression specific to a key and can be a simple value (string, integer).

JSON object is enclosed in {} braces or an array []. You can only use JSON object expressions when you are working with the JSON collection tables. You can refer the JSON object expressions by statements like SELECT, INSERT INTO, UPDATE or you can use it with operators like +, -, /, *.

Syntax:

<json_object_expression>::=
{"<key>":<json_value_expression>}

<json_value_expression> ::= '<string>'
| <numeric_literal>
| <boolean_literal>
| NULL
| <path_expression>
| <json_object_expression>
| <json_array_expression>

<json_array_expression>::= [<json_array_value_expression>,… ]

<json_array_value_expression> ::= '<string>'
| <numeric_literal>
| <boolean_literal>
| NULL
| <path_expression>
| <json_object_expression>

Example:

{ "firstname":'Aditya', "lastname":'Kapoor', "age":45 }

{ "firstname":'Aditya', "lastname":'Kapoor', "age":45, "address": { 'street': ‘Flat no.512, wing B, Leela Residency', 'city': 'Mumbai' } }

Adding Subqueries in SQL Expressions

Subqueries passes an additional logic to the database as a SQL statement. A subquery statement is a SELECT statement enclosed in parentheses. A subquery SELECT statement can only contain one select list item.

You can use a subquery SELECT statement anywhere where a column name involves. For instance, in the SELECT list of top-level SELECT or in the SET clause of an UPDATE statement.

Syntax:

<scalar_subquery_expression> ::= (<subquery>)
A sample SQL script with a subquery select statement is given below.

SELECT DataFlairDepartments, COUNT(*), 'out of',
(SELECT COUNT(*) FROM Employees)
FROM Departments AS Dep, Employees AS Emp
WHERE Dep.DepartmentID = Emp.DepartmentID
GROUP BY DataFlairDepartment;

Summary

These were all the different types of SQL expressions used in SAP HANA to generate a script and perform operations on the SAP HANA database. There are many implementations of SQL in the SAP HANA database which we will cover in upcoming tutorials.

We hope the explanation was helpful. Please leave your comments below in case of any doubts or feedbacks.