SAP HANA ABAP: SQLScript: Functions Part-1

The Goal of AMDP implementation is always to remove any additional logic required at the abap layer i.e. avoid loops, further joins, formatting or data conversions. To achieve this, we can leverage HANA SQL functions to ensure the that the data being returned from AMDP is complete and needs zero or a light touch while passing to the ABAP Layer.

Using the below explained functions we as a HANA developer can make our lives easier.

Function1 - CAST

Syntax: CAST (<expression> AS <data_type>)

Description: This function converts any value to the desired data type.

Sample:

         ex_data = SELECT lfa1.lifnr,
                     lfa1.name1,
                     ekko.ebeln,
                     ekko.bukrs,
                     concat( 'EXRATE',cast(ekko.wkurs as varchar(9))) as wkurs
              FROM lfa1 AS lfa1 LEFT OUTER JOIN ekko AS ekko
              ON lfa1.lifnr = ekko.lifnr;

Explanation:

In the above example, WKURS field of the table EKKO is of DEC 9 type. But at the time of display to the end user we want to concatenate a fixed text with the value of this field. For concatenation we shall need to convert the value of this field to the string type. So we have used the cast operator to convert decimal to string type.

Output:

Function2 -  IFNULL:

Syntax: IFNULL (expression1, expression2)

Description:

Returns the first not NULL input expression.

Returns expression1 if expression1 is not NULL.

Returns expression2 if expression1 is NULL.

Returns NULL if both input expressions are NULL.

Sample:

ex_data = SELECT  lfa1.lifnr,

                     lfa1.name1,

                     ekko.ebeln,

                     ekko.bukrs,

                     ifnull( ekko.kdatb, CURRENT_DATE) as kdatb

              FROM lfa1 AS lfa1 LEFT OUTER JOIN ekko AS ekko

              ON lfa1.lifnr = ekko.lifnr

              WHERE ekko.kdatb is NULL;

Output:

Explanation:

In the above example we want to fill the field KDATB of the table EKKO with the current date if the value of the field is NULL. For this we have used the IFNULL function which has the field on which we want to perform this function as the first parameter, and the second parameter is the expression which we want to get executed if the value of the field is NULL.

Function3 - COALESCE

Syntax: COALESCE (expression_list)

Description:

Returns the first non-NULL expression from a list. At least two expressions must be contained in expression_list, and all expressions must be comparable. The result will be NULL if all the arguments are NULL.

Sample:

ex_data = SELECT lfa1.lifnr,

                     lfa1.name1,

                     ekko.ebeln,

                     ekko.bukrs,

                     COALESCE( ekko.lifnr, 'No PO' ) AS vendor

              FROM lfa1 AS lfa1 LEFT OUTER JOIN ekko AS ekko

              ON lfa1.lifnr = ekko.lifnr;

Output:

Explanation:

In the parameters of COALESCE function we can pass as many expressions as we want. The Function will look for the first non-null value and insert in our output. In the above example we have fixed the value in the second expression which is ‘No PO’, so wherever the value of lifnr is null it will place the hard-coded value.

Function4 - CONVERT_CURRENCY

Syntax: COVERT_CURRENCY( <pass all the mandatory static parameters>)

Description: We can use this function in AMDP to display a column with values in the desired currency, just like we have the same functionality in Modelling Views.

Sample code:

ex_data =  select so_id,

                       gross_amount,

                       convert_currency (amount=>gross_amount,

                                                source_unit_column=>currency_code,

                                                schema=>'SAPABAP1',

                                                target_unit_column=>'USD',

                                                reference_date=>current_Date,

                                                error_handling=>'set to null',

                                                client=>'100') as gross_usd

                       from snwd_so;

Output:

Explanation:

In the above example, we have converted the Gross_amount field values which were earlier in EUR to USD using the above mentioned function.