- SAP Managed Tags:
In my last blog I wrote about 'bottom-up' and 'top-down' approaches to leverage SAP HANA capabilities from ABAP. In this blog I want to give you a glimpse on new features planned for the next support package of ABAP 7.4.
Advanced Open SQL
As you probably know Open SQL is our database independent interface to connect the ABAP application server to the underlying database. The big advantage is that code making use of Open SQL runs on all database platforms supported by SAP NetWeaver AS ABAP.
The big disadvantage is that the feature set of Open SQL is quite restricted. Take a look at the following examples:
- You want to sum up the costs of 12 periods which are stored in 12 different attributes of a record? Read all 12 attributes and sum them up in ABAP.
- You want to concatenate the product ID and name? Read both and concatenate them in ABAP.
- You want to calculate freight costs based on the maximum of the weight and the volume weight of a material? Read weight and volume weight and use an IF-clause in ABAP.
Is there really no better way? In the future there will be! :smile:
We plan to enrich the feature set of Open SQL. This will allow you to push down calculations to the database layer that could not be pushed down by means of Open SQL in the past. With the next support package of ABAP 7.4 we, for example, plan to support:
- string expressions (concatenation of attributes)
- usage of ABAP constants and variables in the projection list
- CASE expressions ('simple CASE')
- certain arithmetic expressions for integral, decimal and floating point calculations
- certain built-in SQL functions (e.g. CAST, COALESCE)
The following example shows how advanced Open SQL can look like:
"product ID and product category are concatenated using a string expression
SELECT product_id && ',' && @space && category AS product,
"the price including the VAT is calculated in the database by means of
"a CASE statement
CASE tax_tarif_code
WHEN 1 THEN price * @lc_factor_1
WHEN 2 THEN price * @lc_factor_2
WHEN 3 THEN price * @lc_factor_3
END AS price_vat, "projection list needs to be separated by comma
currency_code AS currency
FROM snwd_pd
INTO CORRESPONDING FIELDS OF @ls_result. "variables have to be escaped by @
WRITE: / ls_result-product,
ls_result-price_vat CURRENCY ls_result-currency,
ls_result-currency.
ENDSELECT.
Advanced view building
What I have written about Open SQL is basically also true for the view building capabilities of the ABAP Dictionary. In line with Advanced Open SQL we also plan to introduce features for advanced view building. These features will ease code pushdown and simplify the consumption of relational data models.
In the future we plan to allow you to create views by means of a new Eclipse-based editor (integrated into the Eclipse-based ABAP development environment). The following screenshot shows how this editor will look like.
And the following two snippets illustrate how you will define views in the new editor. Views can be nested (i.e. a view consumes another view) and they can be linked with associations.
- In the given example the view Z_DEMO_REVENUES reads certain attributes from table SNWD_SO. It summarizes and groups the data.
@AbapCatalog.sqlViewName: 'Z_DEMO_R'
define view z_demo_revenues as select from snwd_so
{
snwd_so.buyer_guid,
sum(snwd_so.gross_amount) as gross_amount,
sum(snwd_so.net_amount) as net_amount,
sum(snwd_so.tax_amount) as tax_amount,
snwd_so.currency_code as currency
} group by snwd_so.buyer_guid, snwd_so.currency_code
- The view Z_DEMO_CUSTOMER reads data from tables SNWD_BPA and SNWD_AD. It also defines an association to the first view.
@AbapCatalog.sqlViewName: 'Z_DEMO_C'
define view z_demo_customer as select from snwd_bpa
inner join snwd_ad on
snwd_ad.node_key = snwd_bpa.address_guid
association[*] to z_demo_revenues as revenues on
revenues.buyer_guid = snwd_bpa.node_key
{
snwd_bpa.node_key, snwd_bpa.bp_id,
snwd_bpa.company_name,
snwd_ad.country,
snwd_ad.postal_code,
snwd_ad.city,
revenues.gross_amount,
revenues.currency
}
ABAP-managed database procedures
The last planned feature are ABAP-managed database procedures. You might have heard about database procedures already. They can be used to implement complex calculations by means of SQLScript (including Calculation Engine Functions). With the next support package of AS ABAP 7.4 we plan to support database procedures which are managed by the ABAP application server.
The following example shows how ABAP methods can be used as container for database procedures (you might notice that the code inside the method body is not ABAP, but SQLScript).
CLASS zcl_demo_amdp DEFINITION
...
"marker interface (e.g. for where-used list)
INTERFACES: if_amdp_marker_hdb.
METHODS: determine_sales_volume
IMPORTING VALUE(iv_client) TYPE mandt
EXPORTING VALUE(et_sales_volume) TYPE tt_sales_volume.
...
ENDCLASS.
CLASS zcl_demo_amdp IMPLEMENTATION.
"additions for implementation
METHOD determine_sales_volume BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT
"forward declaration of used artifacts
USING snwd_so_i snwd_so_sl snwd_pd.
lt_sales_volume = SELECT product_guid, SUM(quantity) AS quantity,
quantity_unit
FROM snwd_so_i AS i
INNER JOIN snwd_so_sl AS sl
ON sl.client = i.client
AND sl.parent_key = i.node_key
WHERE i.client = :iv_client
GROUP BY product_guid, quantity_unit;
et_sales_volume = SELECT product_id, quantity, quantity_unit
FROM snwd_pd AS pd
LEFT OUTER JOIN :lt_sales_volume AS sv
ON sv.product_guid = pd.node_key
WHERE pd.client = :iv_client
ORDER BY product_id;
ENDMETHOD.
ENDCLASS.
Now you know which new features are planned to ease code pushdown and to simplify consumption of relational data models.
The remaining question for my last blog will be: how can optimized infrastructure components help you to benefit from SAP HANA. If you like to learn about fuzzy-enabled value helps or evaluation of business rules in SAP HANA, you will soon be able to read more... but most likely only after Christmas :cool: .
9 Comments
