calling CDS view with input parameters using associations

[former_member292563]

Hi Experts,

I have a requirement to call an CDS view with input parameters using associations.

I understand it is possible to call a CDS view with input paramaters using below syntax :

SELECT FROM TestView(startDate = '20150101')

But how to write association for CDS view with input paramaters?

Thanks,

Puneet Jain

[Former Member]

Hello Punit,

Please check the below code and try to modify as per your requirement.

define view zcds_p_assc

  with parameters p_distance_l:S_DISTANCE,

                  p_distance_o:S_DISTANCE,

                  p_unit:S_DISTID

  as select from spfli

association [1..1] to scarr as spfli_scarr

              on $projection.carrid = spfli_scarr.carrid     

            { key spfli.carrid,

              key spfli.connid,

                  spfli_scarr.carrname,

                  spfli.cityfrom,

                  spfli.cityto,

                  spfli.distance,

                  spfli.distid }

            where spfli.distid = :p_unit and

                           spfli.distance between :p_distance_l

                                        and :p_distance_o;

Regards,

Lokeswar.

[Panneer]

Hi Puneet,

Are you trying to use path expression in Open SQL to access the associated CDS view? If yes, you cannot pass input parameters to the associated CDS view.

No actual parameters can be passed to input parameters of the CDS views of associations. There are no optional input parameters, which means that these views cannot currently be used in Open SQL path expressions.

Regards,

Panneer

[former_member292563]

Hi Panneer,

I got below example on how to use associations on CDS with input parameters.

Example :

CDS with Input Paramater :

define view ZCDSWithInputParams

  with parameters  startDate : abap.dats , endDate : abap.dats

     as select from salesOrder

{

key node as SalesOrderItem,

amount

}

Calling CDS with input parameters using association :

define view ZCDSTest

     as select from ZCDSTest2

association[0..1] to ZCDSWithInputParams as _mySales on $projection.node = _mySales.node

{

_mySales(startDate : '20150101' , endDate : '20160101').SalesOrderItem

}

Thanks,

Puneet Jain

[Panneer]

Hi Puneet,

Thanks for the code example.

Sure. it is possible to call the CDS via association from CDS itself.

The restriction currently is only from Open SQL as I mentioned above.

If you think you have got the answer, please mark appropriately.

Regards,

Panneer

[AbhijeetK]

Hi Puneet,

There is way you can use, As Parameters can’t be directly used within the definition on Associated targets. Instead you can expose fields in the associated target CDS view as shown in below example.

CDS with Parameter (to be used as association target)

@AbapCatalog.sqlViewName:'ZYX_SQL'

@AbapCatalog.compiler.compareFilter:true

@AbapCatalog.preserveKey:true

@AccessControl.authorizationCheck:#CHECK

@EndUserText.label:'Sales Order Item with Parameters'

defineview ZC_ZYX

withparameters p_matkl : matkl

as

selectfrom vbap {

key vbeln,

key posnr,

matnr,

matkl,

zmeng,

meins

}

where matkl =:p_matkl

Then Final CDS view where we want to use in association --

@AbapCatalog.sqlViewName:'ZZZYX'

@AbapCatalog.compiler.compareFilter:true

@AbapCatalog.preserveKey:true

@AccessControl.authorizationCheck:#CHECK

@OData.publish:true

@EndUserText.label:'Sales Order Header with Associations & Parameters'

defineview ZC_ZZYX

withparameters

p_matkl1 : matkl,

p_vbeln1 : vbeln

as

selectfrom vbak

association[0..*]to ZC_ZYX as _item

on$projection.vbeln = _item.vbeln

{

key vbeln,

key _item(p_matkl::p_matkl1).posnr,

_item(p_matkl::p_matkl1).matkl,

_item(p_matkl::p_matkl1).matnr,

_item(p_matkl::p_matkl1).zmeng,

_item(p_matkl::p_matkl1).meins

}

where vbeln =:p_vbeln1

This will work similar to Right outer join. Alternatively, You can use CDS “ZC_ZYX” view as primary data source in Final CDS.

Regards,

Abhijeet Kankani

[AbhijeetK]

Hi Puneet,

There is way you can use, As Parameters can’t be directly used within the definition on Associated targets. Instead you can expose fields in the associated target CDS view as shown in below example.

CDS with Parameter (to be used as association target)

@AbapCatalog.sqlViewName:'ZYX_SQL'

@AbapCatalog.compiler.compareFilter:true

@AbapCatalog.preserveKey:true

@AccessControl.authorizationCheck:#CHECK

@EndUserText.label:'Sales Order Item with Parameters'

defineview ZC_ZYX

withparameters p_matkl : matkl

as

selectfrom vbap {

key vbeln,

key posnr,

matnr,

matkl,

zmeng,

meins

}

where matkl =:p_matkl

Then Final CDS view where we want to use association --

@AbapCatalog.sqlViewName:'ZZZYX'

@AbapCatalog.compiler.compareFilter:true

@AbapCatalog.preserveKey:true

@AccessControl.authorizationCheck:#CHECK

@OData.publish:true

@EndUserText.label:'Sales Order Header with Associations & Parameters'

defineview ZC_ZZYX

withparameters

p_matkl1 : matkl,

p_vbeln1 : vbeln

as

selectfrom vbak

association[0..*]to ZC_ZYXas _item

on$projection.vbeln = _item.vbeln

{

key vbeln,

key _item(p_matkl::p_matkl1).posnr,

_item(p_matkl::p_matkl1).matkl,

_item(p_matkl::p_matkl1).matnr,

_item(p_matkl::p_matkl1).zmeng,

_item(p_matkl::p_matkl1).meins

}

where vbeln =:p_vbeln1

This will work similar to Right outer join. Alternatively, You can use CDS “ZC_ZYX” view as primary data source in Final CDS.

Regards,

Abhijeet Kankani

[jmalla]

HI abhijeet.kankani

Can the input parameters to the CDS be columns from another CDS view? In the example, you show passing parameters from one view to another but no columns.

Regards,

Jay

[AbhijeetK]

Hi Jay,

Vbeln is from parent cds and zmeng is from other cds view columns or field.

Regards,

Abhijeet