This blog would give basic idea on different concepts which are used in the S4HANA CDS view.
It will be helpful for new beginners on basic concepts which we can use while creating a CDS views
@AbapCatalog.sqlViewName:It is mandatory annotation and which is created in SE11 (16 Characters length) after activating and it will not be same as view name/entity name.
@AbapCatalog.compiler.compareFilter: It defines the evaluation of filter condition in path expression.
True: If they match, the associated join expression is evaluated only once.
@AbapCatalog.preserveKey : It defines the key fields in CDS View.
True: The key fields will be dictated by us in CDS view
False: The key fields will be whatever in ABAP dictionary.
@EndUserText.label: Short text of the CDS view which is description of SE11
table (60 Characters length).
@AccessControl.authorizationCheck:Access control required to retrieve selected
data from database.
Simple example with possible values for annotation :
@AbapCatalog.sqlViewName: ‘ZSQL_BASIC’
@AbapCatalog.compiler.compareFilter: true/false
@AbapCatalog.preserveKey: true/false
@AccessControl.authorizationCheck: #NOT_REQUIRED/#CHECK/#NOT_ALLOWED
@EndUserText.label: ‘Simple program’
define view ZCDS_BASICRA as select from mara
{
key matnr,
matkl,
ntgew
}
->View with Parameters:
Multiple parameters can be given by using Comma separated list. It can be addressed by : or $parameters..By using this concept we can produce result set for a given values which are passed
at execution time. Parameters are optional when system fields are used using
@Environment.systemField: #SYSTEM_DATE/#SYSTEM_TIME/ #SYSTEM_LANGUAGE/#CLIENT
Example:
@AbapCatalog.sqlViewName: ‘ZCDSPARAM’
define view ZCDS_PARAM_RA
with parameters p_vbeln : vbeln
as select from likp
{
key vbeln,
ernam,
vkorg
}where vbeln = :p_vbeln
--or we can declare where vbeln = $parameters.p_vbeln;
-> CASE Expression:
It compares the values and give alternative description based on conditions.
Example :
define view ZCDS_OPERATIONS_RA
as select from vbak as a
{
key a.vbeln as sales,
key a.ernam as name,
key case ( a.vbtyp )
when ‘C’ then ‘Custome’
when ‘D’ then ‘Remain’
else ‘OTHERS’
end as doccat,
key a.erdat,
netwr,
case
when a.netwr is null then ‘error’
when a.netwr = 200 and
a.netwr < 600 then ‘medium’
else ‘high’
end as net_value,
$session.system_language as Language
}
Output:
->JOINS :
To fetch data based on two or more tables we use Join concepts. It returns the data based on condition we specified in the query.
Default join in CDS view is left outer Join.
Example :
@AbapCatalog.sqlViewName: ‘ZCD_JOINRA’
define view ZCD_JOIN_RA as select from likp _SDHeader
inner join lips as _SDItem on _SDHeader.vbeln = _SDItem.vbeln
left outer join kna1 as _customer on _SDHeader.kunnr = _customer.kunnr
{
key _SDHeader.vbeln as delivery,
posnr as delivery_item,
_customer.kunnr as customer
}
->Arithmetic Expression :
Compulsory we have to use alias for every expression .
Example :
define view ZCDS_ArithRA as select from vbak as so
inner join vbap item on so.vbeln = item.vbeln
{
so.kunnr as customer,
item.werks, item.netpr,
ceil( item.netpr ) as ceil1,
floor( item.netpr ) as floor1,
abs( item.netpr ) as absolut,
round( item.netpr,3 ) as roundof,
(item.netpr + item.netpr) as addi,
(item.netpr – item.netpr) as diff
}
->Aggregate Expressions :
Compulsory we have to use alias for every expression. We must specify all
non aggregate fields in Group By Clause.
Example :
define view ZCDS_AGGREGATERA as select from vbak as so
inner join vbap item on so.vbeln = item.vbeln
inner join kna1 cust on cust.kunnr = so.kunnr
{
so.kunnr as customer,
item.werks,
cust.name1,
sum( item.netpr ) as sum_price,
max( item.netpr ) as max_price,
min( item.netpr ) as min_price,
avg( item.netpr) as average,
count( distinct item.netpr ) as cont
} group by so.kunnr,item.werks,cust.name1
->Association :
It is a join on demand/lazy join they will only be triggered when user would access the required data.For example, your CDS view has 3 Associations and user is fetching data for only 2 tables,
the ASSOICATION on other table will not be triggered and the system would return the results quickly.
It is defined with Cardinality[ ]. The association alias is defined with _name as a naming convention. The field specified ON condition must be in Selection list and compare key field of first table with other tables otherwise expose those fields in output.
EXAMPLE :
define view ZCDS_ASSOCIATION_RA as select from vbak as _SHeader
association [1..1] to vbap as _SItem on _SHeader.vbeln = _SItem.vbeln
//associations must be public/exposed
association [1..*] to I_BillingDocumentItem as _billing on
_SHeader.vbeln = _billing.SalesDocument
association [1] to I_Customer as _customer on _SHeader.kunnr = _customer.Customer
//field must be exposed in selection with base
{
key _SHeader.vbeln,
_SHeader.vbtyp,
_SHeader.kunnr as customer,
_SItem, //associations to be public
_billing,
_customer
}
->Extend View :
It is used to extend the custom or standard view without making any changes to the original view. We can reuse the view created in one project to another project with modifying fields based on scenario. A standard/custom view can have multiple extended view.
In SE11 the classical append view is created as .APPEND in ABAP Dictionary.
We can not extend the following views-
Views with aggregate expressions .
Views with a UNION clause.
we can restrict what can be extended in base view using below annotation
@AbapCatalog.viewEnhancementCategory: [ #PROJECTION_LIST/ #GROUP_BY/ #UNION]
Simple Example :
@AbapCatalog.sqlViewAppendName: 'ZCDS_EXTENDR'
@EndUserText.label: 'Extend Mara table basic'
extend view ZCDS_BASICRA with ZCDS_EXTEND_RA
{
mara.mtart,
ernam
}
No comments:
Post a Comment