ABAP Keyword Documentation → ABAP - Reference → Processing External Data → ABAP Database Accesses → Native SQL → AMDP - ABAP Managed Database Procedures → AMDP - Examples
AMDP, Filling 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\to_buyers[ <order> ]
TO FIELD-SYMBOL(<buyer>).
out->begin_section(
|Buyer: { <buyer>-company_name } | &&
|in { order-bupas\to_addresses[ <buyer> ]-city } | ).
out->write(
name = 'Sales Order Items'
data = VALUE output(
FOR <item> IN order-orders\to_items[ <order> ]
( position = <item>-so_item_pos
product = order-items\to_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 central 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 Open 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
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
SELECT *
FROM snwd_so
INTO TABLE @orders
WHERE so_id IN @order_ids.
SELECT *
FROM snwd_so_i
INTO TABLE @items
FOR ALL ENTRIES IN @orders
WHERE parent_key = @orders-node_key.
SELECT *
FROM snwd_pd
INTO TABLE @products
FOR ALL ENTRIES IN @items
WHERE node_key = @items-product_guid.
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 INTO TABLE @adresses
FOR ALL ENTRIES IN @bupas
WHERE node_key = @bupas-address_guid.
ENDMETHOD.
Note
This is a syntax example. The same function can be provided with the same efficiency in Open SQL. AMDP is not needed in simple cases like this.