How to create ABAP CDS views with Input Parameters on SAP HANA?
This tutorial explains how to input parameter and consume ABAP Core Data Services View on SAP HANA
The new enhanced version of ABAP CDS is delivered with SP 8 of ABAP application server 7.4.
Features of Enhanced ABAP Core Data Service(CDS)
- Various expression and function including CUREENCY_CONVERSION, UNIT_CONVERSION and DECIMAL_SHIFT are provided
- Its support Parameterization and Modification-free enhancement of CDS view
- Arrays such as CDS Annotations, Join Type for Associations and Path expression have been improved.
Input Parameter in CDS View
The parameterized Core Data Service views enable you to define general views which can produce context specific result sets by using parameter values which are passed at the time of execution. For example, filtering records for a view without creating a separate view for each filter value.
Defining Input Parameters in CDS view
@AbapCatalog.sqlViewName: 'Example_V_IPARAM1'
@EnduserTect.label: 'Demo: CDS View with Input Parameters'
define view demo_iparameter_01
with parameters p_langu:abap.Lang,
p_saving_rate:abap.dec(7,2),
p_lc_status:sbwd_so_lc_status_code
as
select from snwd_so as header
//joins//
inner join snwd_so_i as item
on header.node_key = item.parent_key
inner join snwd_pd as product
on item.product_guid = prodyct.node_key
//association//
association [1..*] to snwd_text as product_description
om product.desc_guid = product_description.parent_key
{
key header.so_id as order_id,
key item.so_item_pos as item_position,
//product description with EN as fallback language
coalesce(product_description[1: language = :p_langu ].text,
(production_description[1: language = 'E' ].text) as product_description,
@Semantics.currencyCode
item.currency_code as currency_code,
@Semantics.amount.currencyCode: 'currency_code'
item.gross_amount as gross_amount,
cast(item.gross_amount as abap.fltp) * cast( $parameters.p_saving_rate as abap.fltp)
as overall_savings,
$parameters.p_saving_rate as saving_rate,
header.lifecycle_status
}
where header.lifecycle_status = $parameters.p_lc_status
Now activate this CDS view definition and go to data preview
A dialogue window popup which allows us to enter text values for each input parameters
In the data preview, we can see the product definition,calculated overall savings and new saving rate column.
Using Input Parameter in New CDS View
A parametrized view can be used in other view either as data source or in path expressions. Now we will create a new Core Data Service view which will display all the fields of the above CDS view
@AbapCatalog.sqlViewName: 'Example_V_IPARAM2'
@EnduserTect.label: 'Demo: CDS View with Input Parameters (2)'
define view demo_iparameter_02
with parameters p_langu:abap.Lang,
p_saving_rate:abap.dec(7,2),
p_lc_status:sbwd_so_lc_status_code
as select * from demo_iprameter_01( p_langu: $parameter.p_langu
p_saving_rate: $parameter.p_saving_rate ,
p_lc_status: $parameter.p_lc_status)
Calling Parameterized view with Open SQL
Now we will call our parameter view in a another program using SQL.But before executing the parameters we must make sure whether the accessed database system supports this feature.
REPORT ze_demo_cds_iparameters.
PARAMETERS: p_status TYPE snwd_so-lifecycle_status DEFAULT 'p'.
IF abap_true = cl_abap_dbfeatures=>use_fatures(
requested_features = VALUE #( (cl_abap_dbfeatures=>views_with_parameters) ) ).
//Call of CDS view with input parameter//
SELECT * FROM demo_iparameter_02( p_langu = @sy-langu,
p_saving_rate = '0.04',
p_lc_status = @p_status )
INTO TABLE @DATA(it_data(
cl_demo_output=>display_data(
EXPORTING
value = It_data
name = 'Demo: CDS View with Input Parameters' ).
ELSE.
//alternative implementation
WRITE: 'Feature not supported on DB. Provide an alternative implementation ...' COLOR COL_NEGATIVE.
ENDIF.