Skip to Content
0
Jul 07, 2021 at 06:10 PM

Error in accessing SQL View when exposed via CAP (SAP HANA Cloud)

440 Views Last edit Jul 08, 2021 at 04:27 AM 2 rev

Hi All,

I am trying to expose a HANA view in my oData service in CAP by following the steps as mentioned in Create Calculation View and Expose via CAP

The view definition is like this and I have kept it in db module:

VIEW "RateView"
AS  select
            c.status as Status,
            c.unit   as Unit,
            c.region as Region,
            count(
                c.leadID
            )  as count 
        from DetailsTable AS c
        group by
            c.status ,
            c.unit,
            c.region; 

When I deploy this view, I was able to see the data in Explorer. Therefore, in order to expose this view in CAP, I created a proxy entity in CDS data Model and added a matching entity definition for the created view

// namespace my.xxxxxxx;
using {
    cuid,
    managed
} from '@sap/cds/common';

context my.xxxxxxxx {
 entity HostNameSet {
    key hostNameID   : String(15);
        hostNameText : String(50);
      }
}

@cds.persistence.exists
ENTITY ![RateView] {
     ![STATUS]: (15)  :  ; 
keyString@title'STATUS'     ![UNIT]: (10)  :  ; 
keyString@title'UNIT'     ![REGION]: (10)  :  ; 
keyString@title'REGION'     ![COUNT]:   :  ; 
keyInteger@title'COUNT'}

Then added this new View-based entity to the CAP service as read-only.

using my.xxxxxx as my from '../db/data-model';
using RateView  from '../db/data-model'

service test{
@readonly
    entity RateViewSet as projection on RateView;
}

Either I run the application locally or deployed the app in cloud foundry, I am facing "table not found" issue :

Locally

<error xmlns="http://docs.oasis-open.org/odata/ns/metadata">
<code>500</code>
<message>SQLITE_ERROR: no such table: main.RateView</message>
</error>

CF:

[cds] - { Error: invalid table name: Could not find table/view xxxxx_RATEVIEWSET in schema

Also, DB deployer is failing with the error "Error: The file requires "db://RATEVIEW" which is not provided by any file"

Can someone please provide their suggestions/clarifications if I am missing anything in any of the steps?

Regards,

Navneet Kaur.