CASE in SAP HANA - Control flow for SQLScript Transformation Routines

In this article you will learn how to implement IF ELSE constructs in your SQLScript transformation routine. Actually, there are no IF ELSE statements in SQL SELECT statements. However, you can use a native SQL statement to achieve the same goal. We will show you how to do it.

You can use the native CASE WHEN statement to implement the IF - THEN - ELSE logic in your SQL routines. I will explain this statement in detail. Afterwards I illustrate the functionality using a practical example.

Syntax

The syntax of the CASE WHEN statement is as follows.

CASE
WHEN Condition1 THEN Result1
WHEN Condition2 THEN Result2
WHEN ConditionN THEN ResultN
ELSE Result
END;

You can use the following comparison operators for the conditions:

Furthermore, you can use the logical operators OR, AND as well as NOT.

<Condition> ::= <Condition> OR <Condition> | <Condition> AND <Condition>
| NOT <Condition> | ( <Condition> ) |

If you use multiple operators, they are executed in a certain order. First the comparison operators like = or > are evaluated. Then the logical operators are evaluated. The NOT operator has priority over AND and OR. The OR operator has the lowest priority. You can override this order by using parentheses. Conditions in parentheses are always evaluated first.

The CASE statement works similar to an IF-THEN-ELSE statement. The system iterates through the conditions and returns a value when the first condition is met. Once a condition is met, the process stops and the result is returned. If none of the conditions are met, the value in the ELSE clause is returned. If no ELSE part is defined and no conditions are met, NULL is returned.

Processing Sequence

In the context of SAP HANA, the following situation is interesting. Since the database takes some shortcuts to improve performance, it cannot be guaranteed whether all WHEN clauses will be evaluated.

Consider the following example, where c stands for Condition and r for Result.

CASE 
WHEN c1 THEN r1 
WHEN c2 THEN r2 
ELSE r3 
END

In a HANA database, c1, r1, c2, r2 and r3 can be evaluated in any order. Let's assume that c1 would always return TRUE and r2 would always throw an exception.

If c1 is evaluated first and returns TRUE, r1 is evaluated and returned as the result of the CASE expression. The remaining statements are never executed, so r2 would not throw an exception. However, if r2 is evaluated first, an exception is thrown. Therefore, the remaining conditions can no longer be checked. 

Increase the performance of your BW with SQLScript

Example

For a better understanding, I would like to bring a practical example. Here, customers should be segmented based on their wealth.

Following data is available:

As a result, we would expect the following segmentation:

The segmentation is performed using the CASE WHEN statement:

METHOD PROCEDURE BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY.
OUTTAB = SELECT "/BIC/ZDRCLNT",
CASE
WHEN AMOUNT > 50000000 THEN 'A' --Ultra High Net Worth
WHEN AMOUNT BETWEEN 5000000 AND 50000000 THEN 'B' --Very High Net Worth
WHEN AMOUNT BETWEEN 1000000 AND 4999999 THEN 'C' --High Net Worth
ELSE 'D' --Affluent
END AS "/BIC/ZDRCAT",
CURRENCY, RECORDMODE, AMOUNT, RECORD, SQL__PROCEDURE__SOURCE__RECORD
FROM :INTAB;

ERRORTAB = SELECT '' AS ERROR_TEXT, '' AS SQL__PROCEDURE__SOURCE__RECORD FROM DUMMY WHERE DUMMY <> 'X';
ENDMETHOD.

This leads to the following result:

The customer segmentation can then be evaluated in a query:

CASE in SAP HANA - Our Conclusion 

Although IF statements are not provided in native SQL, you can achieve the desired result with CASE WHEN. However, please note that the order in which the conditions are checked is random. Therefore, you should always define conditions that are mutually exclusive and the result is always unique. I hope that this post has helped you.

Are you planning to migrate to SQLScript and need help in planning the right strategy? Or do you need experienced developers to implement your requirements? Please do not hesitate to contact us - we will be happy to advise you.