How to use IF ELSE statements in SAP HANA SQLScript

In the previous article, "CASE in SAP HANA - Control flow for SQLScript Transformation Routines", you learned that IF ELSE constructs are not possible within a SELECT statement. However, you can use the native CASE WHEN statement to implement these control structures in SQLScript.

However, it does not mean that IF ELSE commands are not used in SQLScript. These can continue to be used as conditional in imperative logic. In this article, I explain the use of IF ELSE and also provide a concrete example to illustrate it.

Syntax

The syntax of the ELSE IF statement in SQLScript is similar to that in ABAP.

IF Condition1
THEN 
Statement1
[ELSEIF Condition2
THEN 
Statement2...]
[ELSE 
Statement] 
END IF

You can use the following comparison operators for the conditions:

The IF command allows you to define a control structure that can contain multiple statements. However, only one of the multiple statements will be executed. Which it is, depends on the Boolean conditions that are defined. The conditions are checked from top to bottom and the statement after the first true condition is executed. If none of the conditions are true, the statement block after ELSE is executed. If no ELSE statement has been defined, processing continues after END IF.

By default, the IF statement consists of a Boolean expression and a THEN block. The remaining parts are optional.  In practice, however, you will often use an ELSE branch. If the first condition is not true, the statement after the ELSE branch is executed without any further checks. No further ELSE or ELSEIF branches are allowed after an ELSE command. However, you can add any number of ELSEIF clauses before the ELSE command.

Increase the performance of your BW with SQLScript

Example

For a better understanding I will explain the functionality with an example. In this case, a currency conversion is to be performed. You can learn how to perform a currency conversion with SQLScript in our article "Currency conversion with SAP HANA SQLScript in transformation routines".

The key date for the currency translation is to be determined using an IF ELSE construct. If the currency translation is performed in the first half of the year, the exchange rates of the previous year should be used. Otherwise, the current date is used as the key date.

DECLARE lv_date nvarchar(10);
*current_date is YYYY-MM-DD
SELECT SUBSTRING (TO_DATS(current_date),1,6) INTO lv_date FROM DUMMY;

IF lv_date <= 202106
THEN
lv_date = '2020-12-01';
ELSE
SELECT to_nvarchar (current_date) INTO lv_date FROM DUMMY ;
END IF;

You can find the complete code below.

Code

METHOD PROCEDURE BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY.
DECLARE lv_date nvarchar(10);
*current_date is YYYY-MM-DD
SELECT SUBSTRING (TO_DATS(current_date),1,6) INTO lv_date FROM DUMMY;

IF lv_date <= 202106
THEN
lv_date = '2020-12-01';
ELSE
SELECT to_nvarchar (current_date) INTO lv_date FROM DUMMY ;
END IF;

outTab =
SELECT comp_code, calmonth,
'EUR' AS loc_currcy,
doc_currcy,
recordmode,
CONVERT_CURRENCY( AMOUNT => deb_cre_dc,
SOURCE_UNIT => doc_currcy,
SCHEMA => 'SAPABAP1',
TARGET_UNIT => 'EUR',
REFERENCE_DATE => :lv_date,
CLIENT => '100',
CONVERSION_TYPE => 'EURX')
AS deb_cre_lc,
deb_cre_dc,
record, SQL__PROCEDURE__SOURCE__RECORD
FROM :inTab;

ERRORTAB= SELECT * FROM :ERRORTAB;
ENDMETHOD.

IF ELSE statements - Our Conclusion 

Although IF ELSE commands cannot be used within SQLScript SELECT statements, you can use them as usual with imperative flow control. I hope that this post has been helpful to 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.