Skip to content

ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  AMDP - ABAP Managed Database Procedures →  AMDP - Examples 

AMDP, Fill a Mesh with SQLScript

This example demonstrates how a mesh is filled with a database procedure.

Other versions: 7.31 | 7.40 | 7.54

Source Code

    DATA order_ids TYPE RANGE OF snwd_so-so_id.
    DATA(rows) = 1.
    cl_demo_input=>request( EXPORTING text = `Orders`
                            CHANGING field = rows ).
    SELECT 'I' AS sign, 'EQ' AS option, so_id AS low
           FROM snwd_so
           ORDER BY low
           INTO CORRESPONDING FIELDS OF TABLE @order_ids
           UP TO @rows ROWS. "#EC CI_NOWHERE

    DATA(out) = cl_demo_output=>new( ).
    TRY.
        DATA(order) = NEW cl_demo_amdp_mesh( )->select( order_ids ).
      CATCH cx_amdp_error INTO DATA(amdp_error).
        out->display( amdp_error->get_text( ) ).
        RETURN.
    ENDTRY.

    LOOP AT order-orders ASSIGNING FIELD-SYMBOL(<order>).
      out->begin_section( |Order #{ <order>-so_id }| ).
      ASSIGN order-orders\_buyers[ <order> ]
        TO FIELD-SYMBOL(<buyer>).
      out->begin_section(
        |Buyer: { <buyer>-company_name } | &&
        |in { order-bupas\_addresses[ <buyer> ]-city } | ).
      out->write(
        name  = 'Sales Order Items'
        data = VALUE output(
          FOR <item> IN order-orders\_items[ <order> ]
          ( position = <item>-so_item_pos
            product = order-items\_products[ <item> ]-product_id
          ) ) ).
      out->end_section( )->end_section( ).
    ENDLOOP.
    out->display( ).

Description

The AMDP class CL_DEMO_AMDP_MESH has a private AMDP method SELECT_HDB called by the public method SELECT if the current standard database is the SAP HANA database. For all other databases, the private method SELECT_OPEN_SQL is called. Both methods return the same results, verified by a unit test in the class.

The methods fill the nodes of a mesh for sales orders whose type is declared in the AMDP class. The number of sales orders is restricted using a ranges table. In the method SELECT_OPEN_SQL, the ABAP SQL addition FOR ALL ENTRIES is used, but in SELECT_OPEN_SQL the intermediate results can be evaluated in subqueries.

SQLScript implementation of the AMDP method

METHOD select_hdb  BY DATABASE PROCEDURE
                   FOR HDB LANGUAGE SQLSCRIPT
                   OPTIONS READ-ONLY
                   USING snwd_so snwd_so_i snwd_pd snwd_bpa snwd_ad.
  orders = select * from snwd_so
    where client = :clnt and so_id in (
      select low from :order_ids );

  bupas = select * from snwd_bpa
    where client = :clnt and node_key in (
      select DISTINCT buyer_guid from :orders );

  adresses = select * from snwd_ad
    where client = :clnt and node_key in (
      select address_guid from :bupas );

  items = select * from snwd_so_i
    where parent_key in ( select node_key from :orders );

  products = select * from snwd_pd as product
    where client = :clnt and node_key in (
      select distinct product_guid from :items );
ENDMETHOD.

ABAP implementation of the regular method

METHOD select_open_sql.
  SELECT *
         FROM snwd_so
         INTO TABLE @orders
         WHERE so_id IN @order_ids.

  SELECT *
         FROM snwd_so_i
         FOR ALL ENTRIES IN @orders
         WHERE parent_key = @orders-node_key
         INTO TABLE @items.

  SELECT *
        FROM snwd_pd
        FOR ALL ENTRIES IN @items
        WHERE node_key = @items-product_guid
        INTO TABLE @products.

  SELECT *
        FROM snwd_bpa
        INTO TABLE bupas
        FOR ALL ENTRIES IN orders
        WHERE node_key = orders-buyer_guid
        ##SELECT_FAE_WITH_LOB[WEB_ADDRESS].

  SELECT *
         FROM snwd_ad
         FOR ALL ENTRIES IN @bupas
         WHERE node_key = @bupas-address_guid
         INTO TABLE @adresses.
ENDMETHOD.

Note

This is a syntax example. The same function can be provided with the same efficiency in ABAP SQL. AMDP is not needed in simple cases like this.