Skip to content
NextLytics
Megamenü_2023_Über-uns

Shaping Business Intelligence

Whether clever add-on products for SAP BI, development of meaningful dashboards or implementation of AI-based applications - we shape the future of Business Intelligence together with you. 

Megamenü_2023_Über-uns_1

About us

As a partner with deep process know-how, knowledge of the latest SAP technologies as well as high social competence and many years of project experience, we shape the future of Business Intelligence in your company too.

Megamenü_2023_Methodik

Our Methodology

The mixture of classic waterfall model and agile methodology guarantees our projects a high level of efficiency and satisfaction on both sides. Learn more about our project approach.

Products
Megamenü_2023_NextTables

NextTables

Edit data in SAP BW out of the box: NextTables makes editing tables easier, faster and more intuitive, whether you use SAP BW on HANA, SAP S/4HANA or SAP BW 4/HANA.

Megamenü_2023_Connector

NextLytics Connectors

The increasing automation of processes requires the connectivity of IT systems. NextLytics Connectors allow you to connect your SAP ecosystem with various open-source technologies.

IT-Services
Megamenü_2023_Data-Science

Data Science & Engineering

Ready for the future? As a strong partner, we will support you in the design, implementation and optimization of your AI application.

Megamenü_2023_Planning

SAP Planning

We design new planning applications using SAP BPC Embedded, IP or SAC Planning which create added value for your company.

Megamenü_2023_Dashboarding

Dashboarding

We help you with our expertise to create meaningful dashboards based on Tableau, Power BI, SAP Analytics Cloud or SAP Lumira. 

Megamenü_2023_Data-Warehouse-1

SAP Data Warehouse

Are you planning a migration to SAP HANA? We show you the challenges and which advantages a migration provides.

Business Analytics
Megamenü_2023_Procurement

Procurement Analytics

Transparent and valid figures are important, especially in companies with a decentralized structure. SAP Procurement Analytics allows you to evaluate SAP ERP data in SAP BI.

Megamenü_2023_Reporting

SAP HR Reporting & Analytics

With our standard model for reporting from SAP HCM with SAP BW, you accelerate business activities and make data from various systems available centrally and validly.

Megamenü_2023_Dataquality

Data Quality Management

In times of Big Data and IoT, maintaining high data quality is of the utmost importance. With our Data Quality Management (DQM) solution, you always keep the overview.

Career
Megamenü_2023_Karriere-2b

Working at NextLytics

If you would like to work with pleasure and don't want to miss out on your professional and personal development, we are the right choice for you!

Megamenü_2023_Karriere-1

Senior

Time for a change? Take your next professional step and work with us to shape innovation and growth in an exciting business environment!

Megamenü_2023_Karriere-5

Junior

Enough of grey theory - time to get to know the colourful reality! Start your working life with us and enjoy your work with interesting projects.

Megamenü_2023_Karriere-4-1

Students

You don't just want to study theory, but also want to experience it in practice? Check out theory and practice with us and experience where the differences are made.

Megamenü_2023_Karriere-3

Jobs

You can find all open vacancies here. Look around and submit your application - we look forward to it! If there is no matching position, please send us your unsolicited application.

Blog
NextLytics Newsletter Teaser
Sign up now for our monthly newsletter!
Sign up for newsletter
 

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:

Operator

Description

=

Equal to

>

Greater than

<

Less than

>=

Greater than or equal to

<=

Less than or equal to

!=, <>

Not equal

 

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

Neuer Call-to-Action


Example

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

Customer segments

Following data is available:

Client

Wealth

Currency

1000

100.000.000 

EUR

2000

50.000.000 

EUR

3000

30.000.000 

EUR

4000

5.000.000 

EUR

5000

4.999.999 

EUR

6000

2.000.000 

EUR

7000

1.000.000 

EUR

8000

500.000 

EUR

 

As a result, we would expect the following segmentation:

Client

Wealth

Currency

Category

1000

100.000.000 

EUR

A

2000

50.000.000 

EUR

B

3000

30.000.000 

EUR

B

4000

5.000.000 

EUR

B

5000

4.999.999 

EUR

C

6000

2.000.000 

EUR

C

7000

1.000.000 

EUR

C

8000

500.000 

EUR

D

 

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:

Output table

The customer segmentation can then be evaluated in a query:

Query  result

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.

Learn more about  SAP HANA SQLScript

, ,

avatar

Chris

Chris Fidanidis has been working in the SAP BW environment since 2007. During these years he has implemented several planning projects and used various SAP tools such as SAP BSP, SAP BW-IP, SAP BPC, SAP BW Embedded BPC. He has gained experience primarily as a developer, architect, project manager and team leader. He enjoys playing basketball and barbecue whenever possible.

Got a question about this blog?
Ask Chris

Blog - NextLytics AG 

Welcome to our blog. In this section we regularly report on news and background information on topics such as SAP Business Intelligence (BI), SAP Dashboarding with Lumira Designer or SAP Analytics Cloud, Machine Learning with SAP BW, Data Science and Planning with SAP Business Planning and Consolidation (BPC), SAP Integrated Planning (IP) and SAC Planning and much more.

Subscribe to our newsletter

Related Posts

Recent Posts