SAP CP - CAPM - CDS Entity/OData based on HANA Calculation View with Input Parameter

[chathia]

Hello Experts,

In our project, we are trying to access an existing HANA DB - Calculation View with Input Parameters) and would like to expose it as OData service. This is triggering an error:

Not enough parameters for "ZTEST:ZTEST1", unspecified parameters "IP_1" [1310483]

2nd Approach - Tried to create an CDS SQL Select on the same view, however unsure on how to link the CDS Parameter to HANA Input Parameter:

view CV_TEST_VIEW with parameters IP_1_CDS: Date 
   as select from ZTEST1 { 
      COL1,
      COL2
   } where 'PLACEHOLDER' = ('$$IP_1$$', :IP_1_CDS));

3rd Approach - Tried to create an HANA Table Function and CDS SQL Select on the same, however still it fails.

view CV_TEST_VIEW2 with parameters IP_1_CDS: Date 
   as select from ZTEST_TABLE_FN_1(:IP_1_CDS) { 
      COL1,
      COL2
   };

All the above approach works fine when the HANA Calculation View does not have any Input Parameters.

Is there an way to access such views by passing Input Parameters? Any help to solve this scenario will be appreciated.

Please note the HANA Calculation View contains complex logics, which is not easily Re-implementable in CDS views.

Regards,

Chathia.

[chathia · Accepted solution]

Posting the working solution for the benefit of all. (Thanks 079695 for your inputs..)

1. Synonym is defined as required (In our scenario, Cross container access is required)

2. Define the Calculation View with the same signature and needed fields (as below)

data-model.cds

@cds.persistence.exists
entity Bookinfo_CalcView (IP_KPI_DATE: Date) {
  key id : Integer;
      book_author_info : String(100);
  }

3. Define the Service as below:

cat-service.cds

using Bookinfo_CalcView from '../db/data-model';

service ip_test {
  @readonly entity Bookinfo_CalcView_SRV (INPUT_DATE: Date) as 
     select from Bookinfo_CalcView(IP_KPI_DATE: :INPUT_DATE) {*};
}

4. The above concept should work ideally. However there is a bug identified in the CDS compiler for which SAP has provided an workaround temporarily. Add the below lines in the package.json (in the srv folder) - Localization option will not work (which we don't use it right now)

"features": {
   "localized": false
 }

package.json (in the srv folder)

"cds": {
   "requires": { 
     "db": {
       "kind": "hana", 
       "model": "gen/csn.json" 
      }
  }, 
"features": {
   "localized": false
 }

Regards,

Chathia.

[079695]

Probably a solution will be to try to declare in CAP the so-called facade entity for the existing view, annotated with @cds.persistence.exists annotation.

An example will be the following - if we have the existing view:

VIEW DATA_MODEL_BOOKSHOP_BOOKINFO (in AUTHOR nvarchar(50)) AS SELECT
  ID,
  'The book: ' || THE_TITLE || ' and the author ' || :AUTHOR AS BOOK_AUTHOR_INFO
FROM DATA_MODEL_BOOKSHOP_BOOKS;

In the cds model a facade entity for this view will look like this (it should match the signature of the existing database object):

namespace data.model;
context Bookshop {
  @cds.persistence.exists
  entity Bookinfo (AUTHOR : String(50)) {
    key id : Integer;
    book_author_info : String(100);
  }
}