How to use a CDS view with parameters with a CDS join or association?

[jmalla]

I have a CDS view with parameters that takes the material, quantity, from unit of measure, and to unit of measure.

define view ZOTC_I_MATERIAL_UNIT_CONVR with parameters p_matnr:matnr,
                                                       p_menge_from:kwmeng,   
                                                       p_uom_from:meinh,
                                                       p_uom_to:meinh
as select distinct from marm as data_source
        left outer join marm as marm_from on marm_from.matnr = $parameters.p_matnr and
                                             marm_from.meinh = $parameters.p_uom_from
        left outer join marm as marm_to   on marm_to.matnr   = $parameters.p_matnr and
                                             marm_to.meinh   = $parameters.p_uom_to {
    
 .........
   
}

I need to join this with I_PurchaseOrderItem to pass the material, order quantity, quantity UoM along with the MARM and MVKE table for the sales unit.

How can I join regular CDS views with CDS views with parameters and pass columns? Is this possible? I saw posts that this was not possible a few years back - but hoping that this has changed. CDS views with parameters is powerful - but it's not very useful if we cannot use this with CDS joins and have to fall back to using ABAP or a table function with HANA SQL to do the logic.

Any help is appreciated.

Thanks,

Jay

[fabianfellhauer]

Hi Jay,

in CDS it is possible to add a parameter binding to the definition of a join. For your concrete scenario the join definition should look like this:

define view entity ZOTC_JOIN_WITH_PURCHASEORDERITEM
  with parameters  p_matnr:matnr,
                   p_menge_from:kwmeng,   
                   p_uom_from:meinh,
                   p_uom_to:meinh
as select from ZOTC_I_MATERIAL_UNIT_CONVR( p_matnr: $parameters.p_matnr, p_menge_from: $parameters.p_menge_from, p_uom_from: $parameters.p_uom_from, p_uom_to: $parameters.p_uom_to) 
  left outer join I_PurchaseOrderItem
    on I_PurchaseOrderItem.PurchaseOrder = ...
{
  ...  
}

Does this help you solving this issue? If not, please let me know exactly, which fields / entities need to be joined.

Best regards,

Fabian Fellhauer

[jmalla]

HI Fabian,

Thanks for your reply. I understand this approach of defining the parameters of a CDS view and then passing it to an association.

However, the issue I have is different. I want all of the columns of the join to be passed to the parameterized CDS view and the results from the table function as another column

I want something like this:

define view entity ZOTC_JOIN_WITH_PURCHASEORDERITEM
as select from I_PurchaseOrderItem inner join ZOTC_I_MATERIAL_SALES_UNIT
on I_PurchaseOrderItem.matnr = ZOTC_I_MATERIAL_SALES_UNIT.matnr
{
// Fields from the join

key I_PurchaseOrderItem.PurchaseOrder
key I_PurchaseOrderItem.PurchaseOrderItem
    I_PurchaseOrderItem.matnr,
    I_PurchaseOrderItem.menge,
    I_PurchaseOrderItem.menee,
// And here is where I want to call the association to get the converted unit....
    ZOTC_I_MATERIAL_UNIT_CONVR( p_matnr: I_PurchaseOrderItem.matnr, p_menge_from: I_PurchaseOrderItem.menge, p_uom_from: I_PurchaseOrderItem.p_uom_from, p_uom_to: ZOTC_I_MATERIAL_SALES_UNIT.convert_to_unit).converted_quantity
....
}

That's where I cannot find any way to do this without using ABAP. The parameters to the CDS cannot take columns of a join.

Hope I am able to understand the problem I am facing. Appreciate your help.

Thanks,

Jay

[former_member758319]

Hi Jay,

For this you've to follow 2 steps :

1. create 2 CDS views. In first view CDS1, get required table which you need to use as column.

2. In second view CDS2, Consume the first view CDS1. Also, create CDS2 as table function CDS.

Hope it helps !!

[jmalla]

Hi Shruti - thanks for your answer. I have CDS1 which has all of the columns I need - material, quantity, source unit of measure and target unit of measure. But I do not know how to pass these columns to CDS2 which has the parameters that takes these as input and returns the converted quantity to the target unit of measure. The join to CDS2 does not accept columns from CDS1 as input.

Do you have a work example?

Regards,

Jay

[jmalla]

This works with hardcoded values - but I need to pass the input of the parameters from the columns of the CDS:

This works - I can pass the hardcoded parameters to the parameterized CDS view - but I cannot pass the columns from the join:
@AbapCatalog.sqlViewName: 'ZOTCSTOVOL'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'STO Volume CDS'
define view ZOTC_STO_VOLUME as select from I_PurchaseOrderItem as _Item
  association[0..1] to ZOTC_I_MATERIAL_UNIT_CONVR as _MaterialUnitConversion  
    on $projection.Material  = _MaterialUnitConversion.MaterialNumber
    
{

//I_PurchaseOrderItem 
    key _Item.PurchaseOrder,
    key _Item.PurchaseOrderItem,
    _Item.Material,
    _Item.OrderQuantity,
    _Item.PurchaseOrderQuantityUnit,
//  Sending scalar (hardcoded values) here works - but how to pass columns from this table?
    _MaterialUnitConversion(p_matnr:'000000000101353802',p_menge_from:7,p_uom_from:'CS',p_uom_to:'DSP').QuantityTo

//    ItemVolumeUnit, 
//    ItemVolume,
    

/* Associations */
//I_PurchaseOrderItem 

    
}

[jmalla]

fabianfellhauer - Any suggestions?

shrutiguptacse1566 - Do you have an example?

I have not seen a way to pass columns from a join to parameters to a CDS view. I think this is a feature that is really needed for CDS and I'm sure others have run into this issue.

Thanks,

Jay

[fabianfellhauer]

Hi Jay,

from SQL perspective this is unfortunately not possible today. You are trying to JOIN a parameterized CDS view, which result set is calculated based on the given parameter input. I assume, that you expect a target cardinality of one for your parameterized CDS view as you have used the distinct addition in the header (of course, this depends on the projection list).

The SQL processor of the HANA database usually calculates the join target result set once. If you would hand over parameter values, which are not fix, but depend on the single data sets of your join source, the SQL processor would need to calculate the result set of the parameterized view for each data instance over and over again. This would lead to dramatic performance issues in your application. Therefore this kind of usage is neither recommended nor supported.

Could you please share how you have modeled your unit conversion (ZOTC_I_MATERIAL_UNIT_CONVR)? It's currently not 100% clear for me, which fields of I_PurchaseOrderItem should be mapped to which fields of ZOTC_I_MATERIAL_UNIT_CONVR.

Thank you and best regards,

Fabian

[jmalla]

Hi Fabian,

Thanks for your answer. I was trying all kinds of approaches but came to the conclusion that it is not possible. We found another work around which was layering several CDS views and creating a matrix table of MARM and MVKE on the fly to convert all of the sales units to base units.

Here is the CDS parameterized code that we wanted to use on the join with EKPO and LIPS to convert the volumes.

define view ZOTC_I_MATERIAL_UNIT_CONVR with parameters p_matnr:matnr,

                                                       p_menge_from:kwmeng,   

                                                       p_uom_from:meinh,

                                                       p_uom_to:meinh

as select distinct from marm as data_source

        left outer join marm as marm_from on marm_from.matnr = $parameters.p_matnr and

                                             marm_from.meinh = $parameters.p_uom_from

        left outer join marm as marm_to   on marm_to.matnr   = $parameters.p_matnr and

                                             marm_to.meinh   = $parameters.p_uom_to {

    

        key $parameters.p_matnr as MaterialNumber,

        key $parameters.p_uom_from as UoMfrom,

        key $parameters.p_uom_to as UoMto,

            $parameters.p_menge_from as QuantityFrom,

        

        case when marm_to.umren   = 0 or marm_to.umren   is null then 0

                    when marm_from.umren = 0 or marm_from.umren is null 

                        then 0

                    when marm_to.umren is not null and marm_from.umren is not null 

                        then round (cast(

                              $parameters.p_menge_from * (  division ( division ( marm_from.umrez, marm_from.umren, 3 ), division ( marm_to.umrez, marm_to.umren, 3), 1 ))

                              as abap.quan( 15, 3 )),2 )  

               end as QuantityTo

   

}

I appreciate your help.

Thanks,

Jay

[fabianfellhauer]

Hi Jay,

did you try to model the input parameters of the CDS View as simple fields? As soon as you select from the CDS View, those fields could be bound in the where-clause instead of the parameter binding clause.

Maybe this is worth to try it (also measuring the performance impact)

Best regards

Fabian

[roberto_forti]

Hi, see this association example: "A" = Sales Order data, "B" = Sales Order Item data, "_Item" = association

define view entity ZI_A
as select from ...
association [0..*] to ZI_B_Item as _Item
on $projection.A = _Item.B
{
key A,
_Item,
...
}