Hello Members,
In my previous blog explained how to optimize the classic LOOP….ENDLOOP with modern ABAP syntax FOR GROUPS and REDUCE # operator, please have a look at it if you not seen before.
In this blog we explore how the ABAP CDS (Core Data Service) simplifies the complex logic that used in SAP GUI and boost up the performance of query.
Below is the code snippet that written in SAP GUI, and the purpose of the code was already explained in previous blog.
SELECT a~mblnr,a~erfmg, a~ebeln, a~ebelp,
b~netpr
FROM mseg AS a
INNER JOIN ekpo AS b ON a~ebeln = b~ebeln
AND a~ebelp = b~ebelp
INTO TABLE @DATA(lt_tab)
WHERE mblnr IN @s_mbl.
lt_final = VALUE #( FOR GROUPS ls_group OF ls_data IN lt_tab
GROUP BY ( mblnr = ls_data-mblnr )
LET lv_total = REDUCE netpr( INIT lv_sum = 0
FOR ls_row IN GROUP ls_group
NEXT lv_sum = lv_sum + ( ls_row-netpr * ls_row-erfmg ) )
IN (
VALUE #(
mblnr = ls_group-mblnr
netpr = lv_total ) ) ).
The output of first internal table ‘lt_tab’ as like,
Output of the second internal table ‘lt_final’,
Let’s simplify the above logic using with ABAP core data services. Pre-requisite, please ensure to use S/4 HANA system, and eclipse with ABAP development tools software.
Here I created two views #Basic and #Composite. The view ‘nsdm_v_mseg’ has used to fetch the fields from MSEG data instead of database table in Basic view and aggregate function performed in composite view with the required fields in composite view that selected from basic view.
@AbapCatalog.sqlViewName: ‘ZVIEW_MSEG’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: ‘Basic View for MatDoc’
@Metadata.ignorePropagatedAnnotations: true
@VDM.viewType: #BASIC
define view ZCAL_MSEG_VIEW
as select from nsdm_v_mseg as _doc_seg
association [1..1] to ekpo as _item on _doc_seg.ebeln = _item.ebeln
and _doc_seg.ebelp = _item.ebelp
{
key _doc_seg.mblnr,
key _doc_seg.mjahr,
key _doc_seg.zeile,
_doc_seg.ebeln,
_doc_seg.ebelp,
_doc_seg.waers,
@Semantics.quantity.unitOfMeasure: ‘meins’
_doc_seg.erfmg,
_doc_seg.meins,
_item.netpr
}
@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: ‘Composite View For MatDoc’
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
serviceQuality: #X,
sizeCategory: #S,
dataClass: #MIXED
}
@VDM.viewType: #COMPOSITE
define view entity ZCAL_VIEW
as select from ZCAL_MSEG_VIEW
{
key mblnr as Material_document,
ebeln as Purchase_Order,
@Semantics.amount.currencyCode: ‘Currency’
sum( netpr ) as Total_Amount,
waers as Currency,
fltp_to_dec( sum( cast(erfmg as abap.fltp) * cast(netpr as abap.fltp) ) as abap.dec(11,2)) as Base_amount
}
where
ebeln <> ”
group by
mblnr,
ebeln,
waers
The output of the composite view as below,
Let’s consume the fields from CDS view in GUI system,
SELECT Material_document, Purchase_order, Total_Amount, Currency, Base_Amount
FROM zcal_view
WHERE Material_document IN @s_mbl
ORDER BY Material_document
INTO TABLE @DATA(lt_cds_view).
Comparing the internal tables between lt_cds _view and lt_final.
Conclusion:
In traditional GUI based programming data is fetched from database layer and processed in the application layer, whereas the CDS view has pushed down the logic in database layer itself and it is known the Code Pushdown Approach. It makes more efficient than the GUI based approaches, as they reduce data transfer and leverage the power of the database processing.
Based on complexity of your logic you can decide whether go with CDS view or GUI as per the business requirement. But the above logic there is one more way to achieve by select query itself if you are using S/4HANA 7.50.
SELECT a~mblnr, a~ebeln, a~waers,
CAST( SUM( a~erfmg * b~netpr ) AS DEC( 11,2 ) ) AS total_amt
FROM mseg AS a
INNER JOIN ekpo AS b ON a~ebeln = b~ebeln
AND a~ebelp = b~ebelp
WHERE mblnr IN @s_mbl
GROUP BY a~mblnr, a~ebeln, a~waers
INTO TABLE @DATA(lt_tab).
Thanks for your time!
Hello Members,In my previous blog explained how to optimize the classic LOOP….ENDLOOP with modern ABAP syntax FOR GROUPS and REDUCE # operator, please have a look at it if you not seen before.In this blog we explore how the ABAP CDS (Core Data Service) simplifies the complex logic that used in SAP GUI and boost up the performance of query. Below is the code snippet that written in SAP GUI, and the purpose of the code was already explained in previous blog.SELECT a~mblnr,a~erfmg, a~ebeln, a~ebelp,
b~netpr
FROM mseg AS a
INNER JOIN ekpo AS b ON a~ebeln = b~ebeln
AND a~ebelp = b~ebelp
INTO TABLE @DATA(lt_tab)
WHERE mblnr IN @s_mbl.
lt_final = VALUE #( FOR GROUPS ls_group OF ls_data IN lt_tab
GROUP BY ( mblnr = ls_data-mblnr )
LET lv_total = REDUCE netpr( INIT lv_sum = 0
FOR ls_row IN GROUP ls_group
NEXT lv_sum = lv_sum + ( ls_row-netpr * ls_row-erfmg ) )
IN (
VALUE #(
mblnr = ls_group-mblnr
netpr = lv_total ) ) ).The output of first internal table ‘lt_tab’ as like,Output of the second internal table ‘lt_final’,Let’s simplify the above logic using with ABAP core data services. Pre-requisite, please ensure to use S/4 HANA system, and eclipse with ABAP development tools software.Here I created two views #Basic and #Composite. The view ‘nsdm_v_mseg’ has used to fetch the fields from MSEG data instead of database table in Basic view and aggregate function performed in composite view with the required fields in composite view that selected from basic view.@AbapCatalog.sqlViewName: ‘ZVIEW_MSEG’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: ‘Basic View for MatDoc’
@Metadata.ignorePropagatedAnnotations: true
@VDM.viewType: #BASIC
define view ZCAL_MSEG_VIEW
as select from nsdm_v_mseg as _doc_seg
association [1..1] to ekpo as _item on _doc_seg.ebeln = _item.ebeln
and _doc_seg.ebelp = _item.ebelp
{
key _doc_seg.mblnr,
key _doc_seg.mjahr,
key _doc_seg.zeile,
_doc_seg.ebeln,
_doc_seg.ebelp,
_doc_seg.waers,
@Semantics.quantity.unitOfMeasure: ‘meins’
_doc_seg.erfmg,
_doc_seg.meins,
_item.netpr
} @AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: ‘Composite View For MatDoc’
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
serviceQuality: #X,
sizeCategory: #S,
dataClass: #MIXED
}
@VDM.viewType: #COMPOSITE
define view entity ZCAL_VIEW
as select from ZCAL_MSEG_VIEW
{
key mblnr as Material_document,
ebeln as Purchase_Order,
@Semantics.amount.currencyCode: ‘Currency’
sum( netpr ) as Total_Amount,
waers as Currency,
fltp_to_dec( sum( cast(erfmg as abap.fltp) * cast(netpr as abap.fltp) ) as abap.dec(11,2)) as Base_amount
}
where
ebeln <> ”
group by
mblnr,
ebeln,
waersThe output of the composite view as below,Let’s consume the fields from CDS view in GUI system,SELECT Material_document, Purchase_order, Total_Amount, Currency, Base_Amount
FROM zcal_view
WHERE Material_document IN @s_mbl
ORDER BY Material_document
INTO TABLE @DATA(lt_cds_view).Comparing the internal tables between lt_cds _view and lt_final.Conclusion:In traditional GUI based programming data is fetched from database layer and processed in the application layer, whereas the CDS view has pushed down the logic in database layer itself and it is known the Code Pushdown Approach. It makes more efficient than the GUI based approaches, as they reduce data transfer and leverage the power of the database processing.Based on complexity of your logic you can decide whether go with CDS view or GUI as per the business requirement. But the above logic there is one more way to achieve by select query itself if you are using S/4HANA 7.50.SELECT a~mblnr, a~ebeln, a~waers,
CAST( SUM( a~erfmg * b~netpr ) AS DEC( 11,2 ) ) AS total_amt
FROM mseg AS a
INNER JOIN ekpo AS b ON a~ebeln = b~ebeln
AND a~ebelp = b~ebelp
WHERE mblnr IN @s_mbl
GROUP BY a~mblnr, a~ebeln, a~waers
INTO TABLE @DATA(lt_tab).Thanks for your time! Read More Technology Blog Posts by Members articles
#SAP
#SAPTechnologyblog
