New Open SQL

Comma and Escaping variable

Comma: With New SQL, fields to be selected are separated by comma operator. Old SQL statement is still valid however the written SQL should be either fully new SQL or old SQL… mixing both syntaxes give a syntax error! e.g. If a field list is separated by a comma, the host variables must be escaped with @ sign.

Escaping Variable: Now any Variable, Work area or  Internal table which are local to logic, must be escaped with @ sign when using New SQL. This clearly distinguishes the table fields from external variables.

 Explanation:

With new open SQL:

• Selection list of fields has to be separated by a comma AND
• At the same time, any host variable used for either for comparison or storing a value must be escaped by @ sign

With new open SQL, either both of above should hold true or new SQL shouldn’t be used at all and old SQL should be used.

Selecting a Constant

ABAP HANA 7.51: Selecting a Constant

Selecting a constant is possible now in a SELECT query. There may be different reasons for selecting a constant, however, in reality, it doesn’t fetch anything from the database. e.g. This can be readily used for existence check.

 Explanation:

Here we are selecting a constant ABAP_TRUE which is defined in TYPE-POOL ABAP with type BOOLEAN and value X. 

If Material number 112 is found in MARA table, nothing is fetched from Database but since the query is successful, LV_EXISTS is set to ‘X’ which is the value of ABAP_TRUE.  

Hence, we didn’t fetch any field from Database and selecting a constant helped to do an existence check.

Arithmetic Calculation

ABAP HANA 7.51: Arithmetic Calculation

With ABAP 7.4+, it is now possible to do basic arithmetic calculation inside SQL query. Some arithmetic operations which are now possible are:

• Addition,
• Subtraction,
• Multiplication,
• Division, and
• Calculating remainder value(MOD)

Apart from basic mathematical operations, new ABAP offers a good number of value-added bonus operations which make ABAPing faster and elegant. To name a few of them would be:

• ABS(argument) – Gives absolute value of argument
• DIVISION(argument1,argument2,DEC) – Result is division of argument1
  “by argument2 and rounded to DEC number of places
• DIV(argument1,argument2) – Gives Integer part of Division operation
• FLOOR(argument) – Provides just immediate smaller neighbor Integer
• CEIL(argument) – Provides just immediate bigger neighbor Integer
• DATS_ADD_DAYS(date1, dayCount,if_error) – Returns a date, dayCount days after date1
• DATS_DAYS_BETWEEN(date1,date2) – Returns interval between 2 dates
• COALESCE(argument1,….) – Returns first non-null argument
• CONCAT_WITH_SPACE(argument1,argument2,) – concatenates
  “arguments with number of spaces provided in 3rd argument
• CONCAT(argument1,….) – concatenates arguments, trailing blanks are not taken into account
• LENGTH(argument) – Gives length of argument but ignores trailing blanks
• LOWER(argument) – Converts the string argument to lower case

The output will create an Internal table with four columns. Name of the fourth column will be ‘SEATSFREE’ and it will be a calculated value.

Concatenation in SQL

ABAP HANA 7.51: Concatenation

Enhanced ABAP gives another new feature of Concatenation which makes it possible to concatenate multiple selected fields which can be merged with hardcoded strings, spaces or delimiters.

Concatenation in new Open SQL query is achieved with && operator. If any hardcoded string is to be also concatenated, it is enclosed inside single quotes. All Variables, Constants and hardcoded literals are kept inside small brackets “( … )”. As the last syntax action, ‘to be concatenated text’ is given an alias with AS keyword. This will effectively create a field with given alias in the created Internal table.

Refer below Source code Example!

The output of above query will create an Internal table with only one field called KEY!

CASE Statement 

ABAP HANA 7.51: CASE Statement

Newly introduced CASE statement comes as a longtime prayer of ABAPers heard by SAP. Case inside SELECT simplifies application logic and make code easy to understand. To name a few, some of the most important advantages of the CASE statement is as below:

• No need to do a separate loop on Internal table generated by SQL query just for CASE-based filtering data fetched from the database.
• This reduces overall effort and number of lines of code to be written
• It is possible to Code push down a complex business logic to the database layer which practically makes execution faster on HANA database

CASE keyword works with WHEN, THEN and ELSE keywords and entire CASE statement have to be given an alias with AS.

The output of above SQL query will create Internal table LT_MARA with two fields viz. MATNR and MATERIAL_TYPE. The second column MATERIAL_TYPE is calculated by taking a CASE of Material Type available in Material Master and classify a Material into three categories.

Case Insensitive search with SELECT

ABAP HANA 7.51: Case Insensitive search with SELECT

Case Insensitive search can be achieved by using a combination of either of two:

1. String function TO_LOWER( ) and SQL string function LOWER( ) with LIKE operator
2. String function TO_UPPER( ) and SQL string function UPPER( ) with LIKE operator

Sample Source Code: By converting Search string to lower case

” Lower case

DATA(search_term) = `Box`.

search_term =  |%{ to_lower( search_term ) }%|.

SELECT
DISTINCT maktx FROM makt

 WHERE lower( maktx ) LIKE @search_term

 INTO TABLE @DATA(lt_search_result).

IF sy–subrc IS INITIAL.

  cl_demo_output=>display( lt_search_result ).

ELSE.

  WRITE : ‘Search failed!’.

ENDIF.

In above code, the search term is first converted to lower case which precedes and trails with a %(works as a wildcard to be used with LIKE operator). A SELECT query is made on MAKT which contains Material descriptions(may contain mixed lower and upper cases).

If someone makes a query to find Material description checking equality with a string, there is a high chance to miss the right hit.

To get rid of this situation, query converts material description to lower case inside query and then compares it with search term using LIKE operator. This technique offers a good chance for the right hit.

Output: By converting Search string to lower case

The output gives all Material descriptions which contain ‘Box’ in any part of the string with any case.

Sample Source Code: By converting Search string to upper case

” Upper case

DATA(search_term) = `Box`.

search_term =  |%{ to_upper( search_term ) }%|.

SELECT
DISTINCTmaktx FROM makt

 WHERE upper( maktx ) LIKE @search_term

 INTO TABLE @DATA(lt_search_result).

IF sy–subrc IS INITIAL.

  cl_demo_output=>display( lt_search_result ).

ELSE.

  WRITE : ‘Search failed!’.

ENDIF.

This a very similar code with only two differences:

1. Search term string is converted to UPPER case using to_upper string function
2. SELECT query uses UPPER string function to convert Material description to upper case

All the functionalities work same!

 Output: By converting Search string to upper case

The output gives all Material descriptions which contain ‘Box’ in any part of the string with any case. This is exactly same to the first Variant of case-insensitive search discussed above.

New SQL functions with ABAP 7.51

ABAP HANA 7.51: New SQL functions with ABAP 7.51

SAP provides many new SQL functions which help to perform in-SQL calculations and hence facilitates Code push down. In some of the cases, it can enhance code performance in a great way. Some of the new functions are as below:

• DATS_ADD_DAYS(date1, dayCount,if_error) – Returns a date, dayCount days after date1
• DATS_DAYS_BETWEEN(date1,date2) – Returns interval between 2 dates
• COALESCE(argument1,….) – Returns first non-null argument
• CONCAT_WITH_SPACE(argument1,argument2,) – concatenates arguments with number of spaces provided in 3rd argument
• CONCAT(argument1,….) – concatenates arguments, trailing blanks are not taken into account
• LENGTH(argument) – Gives length of argument but ignores trailing blanks
• LOWER(argument) – Converts the string argument to lower case
• MOD(argument1,argument2) – Gives remainder after argument1/argument2
• DIVISION(argument1,argument2,DEC) – Result is division of argument1 by argument2 and rounded to DEC number of places
• DIV(argument1,argument2) – Gives Integer part of Division operation
• FLOOR(argument) – Provides just immediate smaller neighbor Integer
• CEIL(argument) – Provides just immediate bigger neighbor Integer
• ABS(argument) – Gives absolute value of argument