SAP HANA, Currency Conversion with SQLScript

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

This example demonstrates how the SQLScript operator CE_CONVERSION is used.

Source Code

REPORT demo_sqlscript_curr_conv.

CLASS demo DEFINITION.
  PUBLIC SECTION.
    CLASS-METHODS main.
  PRIVATE SECTION.
    CLASS-METHODS setup.
ENDCLASS.

CLASS demo IMPLEMENTATION.
  METHOD main.
    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_abap_dbfeatures=>use_features(
          EXPORTING
            requested_features =
              VALUE #( ( cl_abap_dbfeatures=>call_amdp_method ) ) ).
      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( ).

  ENDMETHOD.
  METHOD setup.
    DELETE FROM demo_prices.
    INSERT demo_prices FROM TABLE @( VALUE #(
      ( id = 1 amount = '1.00' currency = 'EUR' )
      ( id = 2 amount = '1.00' currency = 'GBP' )
      ( id = 3 amount = '1.00' currency = 'JPY' )
      ( id = 4 amount = '1.00' currency = 'USD' ) ) ).
  ENDMETHOD.
ENDCLASS.

START-OF-SELECTION.
  demo=>main( ).

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