ABAP Keyword Documentation → ABAP - Reference → Processing External Data → ABAP Database Accesses → ABAP and SAP HANA → ABAP and SAP HANA, Examples

SAP HANA, Currency Conversion with SQLScript

This example demonstrates how the SQLScript operator CE_CONVERSION is used.

Other versions: ~~7.31~~ | 7.40 | 7.54

Source Code

    DATA(out) = cl_demo_output=>new( ).

    DATA currency TYPE c LENGTH 5 VALUE 'USD'.
    cl_demo_input=>request( CHANGING field = currency ).
    setup( ).

    SELECT *
           FROM demo_prices
           ORDER BY id
           INTO TABLE @DATA(original_prices).

    out->begin_section( `Original Prices`
      )->write( original_prices ).

    IF cl_db_sys=>is_in_memory_db = abap_true.
      NEW cl_demo_sqlscript_curr_conv(
        )->convert(
          EXPORTING
            to_currency      = to_upper( currency )
            mandt            = sy-mandt
            date             = sy-datlo ).
    ELSE.
      NEW cl_demo_sqlscript_curr_conv(
        )->abap_convert(
          EXPORTING
            to_currency      = to_upper( currency )
            mandt            = sy-mandt
            date             = sy-datlo ).
    ENDIF.

    SELECT *
           FROM demo_prices
           ORDER BY id
           INTO TABLE @DATA(converted_prices).

    out->next_section( `Converted Prices`
      )->write( converted_prices
      )->display( ).

Description

If possible, the example program calls the AMDP method CONVERT of the AMDP class CL_DEMO_SQLSCRIPT_CURR_CONV.

METHOD convert BY DATABASE PROCEDURE FOR HDB
                           LANGUAGE SQLSCRIPT
                           USING demo_prices.
  PRICES = select * from DEMO_PRICES;
  PRICES =
    CE_CONVERSION (
      :PRICES,
      [ family             = 'currency',
        method             = 'ERP',
        steps              = 'shift,convert,shift_back',
        target_unit        = :TO_CURRENCY,
        client             = :MANDT,
        source_unit_column = "CURRENCY",
        reference_date     = :DATE,
        output_unit_column = "CURRENCY",
        error_handling     = 'keep unconverted' ],
      [ amount AS amount ] );
   replace DEMO_PRICES select * from :PRICES;
ENDMETHOD.

The SQLScript implementation of this method uses the predefined SQLScript operator CE_CONVERSION to convert the amounts in the column amount in the database DEMO_PRICES to a currency entered by the user. Conversions of this type cannot be expressed in standard-SQL.

If the current database is not an SAP HANA database, the method ABAP_CONVERT is called as an alternative.

METHOD abap_convert.
  DATA prices TYPE STANDARD TABLE OF demo_prices.
  SELECT *
         FROM demo_prices
         INTO TABLE prices.
  LOOP AT prices ASSIGNING FIELD-SYMBOL(<price>).
    CALL FUNCTION 'CONVERT_TO_LOCAL_CURRENCY'
      EXPORTING
        client           = mandt
        date             = date
        foreign_amount   = <price>-amount
        foreign_currency = <price>-currency
        local_currency   = to_currency
      IMPORTING
        local_amount     = <price>-amount
      EXCEPTIONS
        OTHERS           = 4.
    IF sy-subrc <> 0.
      CONTINUE.
    ENDIF.
    <price>-currency = to_currency.
  ENDLOOP.
  MODIFY demo_prices FROM table prices.
ENDMETHOD.

This method uses the function module CONVERT_TO_LOCAL_CURRENCY. Here, the table in question is first read to an internal table of the application server and must be written again after the conversion.

Note