Within newer versions of Open SQL, you can now add both simple and complex conditions using the CASE.. ENDCASE expression. In addition, you may use the SUBSTRING function in order to extract part of a character field. In this article, we will see the two topics, along with coding examples and output illustrations.
CASE Expressions
Before the addition of the CASE expression in SELECT statements, developers used to first write SELECT statements for fetching data into the application server and later used to apply IF statements for data filtering. It is now much efficient to apply CASE statements with the Open SQL SELECT statement. This is very beneficial to achieve Code push-down in HANA systems.
Let us see a simple requirement. We will use in this example the table T512T - SAP table used to store wage type text information. Suppose we need to get wage-type code and determine wage-type text according to following rules:
Wage type - 111 should be mentioned as ‘Wage Type 1’
Wage type - 112 should be mentioned as ‘Wage Type 2’
All other wage-types should be mentioned as ‘Others’
In the earlier versions of SAP, where CASE expressions cannot be used within SELECT statements, we must read wage-type values from table T512T using SELECT statements and store in an internal table.
Thereafter, use CASE….ENDCASE statement to go through internal table values and display the required information. We can also use, IF…ENDIF statement instead of CASE….ENDCASE statement.
Advanced Open SQL allows us to club two statements together in a single statement. Consider below code example:
TYPES: BEGIN OF MY_TYPES,
LGART TYPE T512T-LGART,
WAGE_TYPE_TEXT TYPE STRING,
END OF MY_TYPES.
DATA MYTABLE TYPE STANDARD TABLE OF MY_TYPES.
SELECT LGART,
CASE
WHEN LGART = ‘/111’ THEN ‘WTYPE 111’
WHEN LGART = ‘/112’ THEN ‘WTYPE 112’
ELSE ‘OTHER VALUES’
END AS WAGE_TYPE_TEXT
FROM T512T INTO TABLE @MYTABLE.
In the above code, an internal table has been defined named MYTABLE of type MY_TYPES. This table has two fields named LGART and WAGE_TYPE_TEXT. In this statement, we have first read the field LGART from table T512T. We then applied CASE expression to determine the relevant wage type text. The text is stored into WAGE_TYPE_TEXT field of internal table MYTABLE.
The content of the internal table MYTABLE after execution of the SELECT statement will look like the one shown
The above example shows a simple condition based on single field. However, we can also write complicated CASE expressions based on multiple fields.
Suppose we have a different requirement:
We have TWB01 table and need to read values of field BOPNR and BORESVOL and display CATEGORY as follows:
If BOPNR = ‘80FT’ & BORESVOL is equal to 10 or higher than 10, the CATEGORY field must be ‘80Ft box’
If BOPNR = ‘80RF’ or BORESVOL is equal to 2000, the CATEGORY field must be ‘Non-80Ft Box’
For all other cases, the CATEGORY field should be ‘NORMAL’
We also need to display BORESVOL field along with BOPNR and CATEGORY fields.
In the above code, we have defined an internal table MYTABLE with CATEGORY, BOPNR and BORESVOL fields. A CASE expression is written within the SELECT statement which matches the conditions defined on BOPNR and BORESVOL field values. The output text is stored in the CATEGORY field which is defined in the internal table MYTABLE.
SUBSTRING Function
The function SUBSTRING is support in Open SQL. The SUBSTRING function has three parameters where you will have the first parameter as the string STR who subset is to be considered. This could be a field of the database table read. The second parameter specifies the starting character of the string STR from which the substring start is to be determined. The third parameter is the length of the substring.
We can write a simple program to demonstrate the usage of the SUBSTRING function. Such a piece of code is shown:
Here we use a SELECT statement. Within the statement, we have a SUBSTRING function that specifies a substring of the field LGTXT. Here we read from position 10 till a total length of 16 characters. This substring is then outputted as a new field TRIMMED_FIELD in the resulting internal table MYITAB, specified via inline declaration.
The contents of the internal table MYITAB is shown in Figure 5.
