topic Re: calling CDS view with input parameters using associations in Application Development and Automation Discussions

calling CDS view with input parameters using associations

former_member292563 — Mon, 27 Jun 2016 07:47:32 GMT

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

Re: calling CDS view with input parameters using associations

Former Member — Thu, 30 Jun 2016 12:13:28 GMT

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.

Re: calling CDS view with input parameters using associations

Panneer — Thu, 30 Jun 2016 12:50:27 GMT

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

Re: calling CDS view with input parameters using associations

former_member292563 — Fri, 01 Jul 2016 07:25:08 GMT

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

Re: calling CDS view with input parameters using associations

Panneer — Fri, 01 Jul 2016 11:03:21 GMT

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

Re: calling CDS view with input parameters using associations

AbhijeetK — Sat, 05 Oct 2019 07:09:13 GMT

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

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

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

Re: calling CDS view with input parameters using associations

AbhijeetK — Sat, 05 Oct 2019 07:15:08 GMT

Hi Puneet,

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

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

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

Re: calling CDS view with input parameters using associations

jmalla — Wed, 11 Aug 2021 05:11:30 GMT

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

Re: calling CDS view with input parameters using associations

AbhijeetK — Sat, 04 Sep 2021 14:16:22 GMT

Hi Jay,

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

Regards,

Abhijeet