CDS Associations and Path Expressions – ABAP on HANA
This Article demonstrates concept of CDS associations and coding path expression in SQL statements and in CDS view. I will also explain how we can specify Inner join instead of left outer join which is default for CDS associations.
SAP wants us to use associations instead of joins because they are closer to “conceptual Thinking”. Association is basically not a join as such, it is just metadata about possible join connection. It’s Join on Demand. Actual join is created when the association is used in a path expression.
Example for simple CDS association which looks no different from left outer join:
@AbapCatalog.sqlViewName: 'ZCDS_ASSOC11'
define view zcds_assoc1 as select from scarr as sca
association [0..1] to spfli as _spfli
on sca.carrid = _spfli.carrid
{ * }
Examples for Exposed CDS associations:
@AbapCatalog.sqlViewName: 'ZCDS_ASSOC41'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view zcds_assoc4 as select from sairport as sair
association [1..*] to spfli as _spfli on
$projection.airportfrom = _spfli.airpfrom
{
sair.id as airportfrom,
sair.name,
sair.time_zone,
-- exposing association
_spfli
}
In the below example, you can see association with SPFLI table to SFLIGHT and SAIRPORT tables. All fields of SFLIGHT and SAIRPORT are exposed in the projection list using alias _sfli and _sair. When path expression used to invoke association, join condition will be created on spfli.carrid = sflight.carrid and spfli.connid = sflight.connid and on spfli.airport = sairport.id based on selection fields.
@AbapCatalog.sqlViewName: 'ZCDS_ASSOC21'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view zcds_assoc2 as select from spfli
association to sflight as _sfli on
spfli.carrid = _sfli.carrid and
spfli.connid = _sfli.connid
association [1..1] to sairport as _sair on
$projection.airportfrom = _sair.id
{
spfli.carrid,
spfli.connid,
spfli.airpfrom as airportfrom,
-- exposing association
_sfli,
_sair
}
In the below example, association defined using association above which is ZCDS_ASSOC2 CDS entity
@AbapCatalog.sqlViewName: 'ZCDS_ASSOC31'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view zcds_assoc3 as select from scarr
association [1..1] to zcds_assoc2 as _cdsassoc on
$projection.carrierid = _cdsassoc.carrid
{
scarr.carrid as carrierid,
-- Exposing Association
_cdsassoc
}
Path Expression in SQL statement: To call CDS association in SQL statement we need to use path expression shown below. In the above CDS association, entire projection list of ZCDS_ASSOC2 is exposed using _cdsassoc.
DATA(w_dbfeature) = cl_abap_dbfeatures=>use_features( requested_features = VALUE #( ( cl_abap_dbfeatures=>views_with_parameters ) ) ).
IF w_dbfeature IS NOT INITIAL.
SELECT carrierid ,
\_cdsassoc\_sfli-fldate AS flightdate,
\_cdsassoc\_sair-name AS flightname
FROM zcds_assoc3
WHERE carrierid = @carrid
INTO TABLE @DATA(t_data1).
ENDIF.
Path expression in CDS view:
@AbapCatalog.sqlViewName: 'ZCDS_ON_ASSOC1'
define view zcds_on_assoc with parameters airport: S_FROMAIRP as select from zcds_assoc2 as cds2
{
cds2.carrid,
cds2.connid,
cds2.airportfrom,
cds2._sair.name, -- use inner join...by default association uses left outer join
cds2._sfli.planetype
}
where cds2.airportfrom = :airport
As I have mentioned, CDS associations creates left outer join when invoked using path expression.
In SPFLI table data, there is no flights from airport RTM
Result of below CDS view will be an entry for RTM airport without carrid when we enter parameter value for airport = RTM.
@AbapCatalog.sqlViewName: 'ZCDS_ON_ASSOC41'
define view zcds_on_assoc4 with parameters airport: S_FROMAIRP as select from zcds_assoc4 as cds_assoc
{
cds_assoc.airportfrom,
cds_assoc.name,
cds_assoc.time_zone,
cds_assoc._spfli.carrid -- use inner join...by default association uses left outer join
}
where cds_assoc.airportfrom = :airport
Executed above CDS view:
Resulted in data preview:
To change left outer join which is default to inner join in CDS view calling CDS association zcds_assoc4 using path expression, we need to use [inner] as shown below:
@AbapCatalog.sqlViewName: 'ZCDS_ON_ASSOC41'
define view zcds_on_assoc4 with parameters airport: S_FROMAIRP as select from zcds_assoc4 as cds_assoc
{
cds_assoc.airportfrom,
cds_assoc.name,
cds_assoc.time_zone,
cds_assoc._spfli[inner].carrid -- use inner join...by default association uses left outer join
}
where cds_assoc.airportfrom = :airport
If we executed this, will result in empty data for parameter value: RTM
Right outer join is not available for CDS association yet.
Filters on CDS associations shown in below example:
@AbapCatalog.sqlViewName: 'ZCDS_ASSOC_FILT'
define view ZCDS_ASSOC_FILTER as select from zcds_assoc2 as cds2
{
cds2._sair[ id = 'TYO' ].name,
cds2._sfli.planetype
}
Output for above CDS view:
Hope this article helpful.
Hi Santhosh,
Thank you very much for the explanation!!!
Can you please explain association [1..1] briefly?
Hi Santhosh,
Thank you very much for knowledge sharing.
I am very fresh about association.
Could you kindly explain more about the difference between association[0..*], association[0..1], association[1.1] and association[1..*]?
how to determine to use which of these associations?
Wesley
It defines the cardinality of the target source, [ min..max ] with possible values 0,1 and * .
For an example, let's say we have sales order and sales order item table, for 1 sales order there must be 1 sales order item and and maximum could be any number, in such case our cardinality will be [ 1..* ] . It says that Sales order should have minimum 1 sales order item and maximum could be any number..
Let's extend this example further, let's say 1 sales item can not have multiple material, so the cardinality of association in between sales order item and item material would be [ 1..1 ] .
Hope I made it clear!!
Hi Rohit,
Let's assume the material number is not a key field so there may be “1” or “o” or “n” entries in the item table. In that case, which cardinality would be used?
Thanks,
Thulasi
Hi Santosh,
I am trying to extend CPURORDITEMENH. I created a new view Z*DISC to get the item discount. When i applied it in the extension, i encountered the error CPURORDITEMENH is inconsistent with the active version.
I attached the image for the associations.
Do you have any ideas on how to fix the inconsistency error?
Thanks,
Sam
Hi Isaac,
I am actually starting the standard cds view extension. when i am adding fields to it using a database table in fact any table, it is giving the below error
Thanks,
Sudarshan
C_OUTGOINGCHECK doesn't look like CDS view
don’t use qmel.erdat instead use erdat try to see if it works.
Thank you.
Hi Santhosh,
I was actually searching for a similar information. thanks for the blog.
I am starting on extending the standard cds view and actually facing issues. It will be really helpful if you give any info .
Thanks
Sudarshan
I am trying to extend the MSEG table with one custom field via append structure YYNAME1 and for the same need to extend the proxy object of NSDM_E_MSEG getting below error. inside { yy_name }
NSDM_E_MSEG needs to be CDS view, not proxy object
don't use mseg.YY_NAME instead use YY_NAME try to see if it works.
Thank you.
V
Nice tutorial. One comment, the ABAP CDS views works on other DB as well and it’s not just for HDB. Currently I am using Netweaver 7.51 SP2 on SYBASE.