HDBView cannot be deployed on SAP HANA Cloud

[M_Zimmermann]

Hello everybody,

I'm using the SAP CAP framework and thereby I'm having a problem with a generated .hdbview which cannot be deployed onto SAP HANA Cloud. Locally the application can be run using SQLite without any problems.

Here's a simple example:

I'm having following db/schema.cds:

namespace sample;

entity Tasks {
    key ID          : UUID;
        assignments : Association to many TaskAssignments
                          on assignments.task = $self;
        title       : String(50);
}

entity TaskAssignments {
    key ID      : UUID;
        task    : Association to Tasks;
        validTo : Date default null;
}

And that's my srv/task-service.cds. The purpose of this view is to read the latest assignment where validTo is set.

using {sample as db} from '../db/schema';

service TaskService {
    entity PrecedingAssignments as
        select from db.TaskAssignments {
            ID,
            task,
            validTo,
        }
        where
            validTo is not null
        group by
            task.ID
        having
            max(
                validTo
            );

    entity Tasks                as projection on db.Tasks;
}

I added the features hana and mta to the CAP project using cds add. I'm just using the default mta.yaml file.

Now the problem seems to be the generated TaskService.PrecedingAssignments.hdbview which is generated by cds build --production. The @sap/hdi-deploy module which is also generated into gen/ is not able to deploy the view during deployment of the MTA archive using cf deploy. The TaskService.PrecedingAssignments.hdbview looks like this:

-- generated by cds-compiler version 2.10.4
VIEW TaskService_PrecedingAssignments AS SELECT
  TaskAssignments_0.ID,
  TaskAssignments_0.task_ID,
  TaskAssignments_0.validTo
FROM sample_TaskAssignments AS TaskAssignments_0
WHERE TaskAssignments_0.validTo IS NOT NULL
GROUP BY TaskAssignments_0.task_ID
HAVING max(TaskAssignments_0.validTo)
WITH ASSOCIATIONS (
  MANY TO ONE JOIN TaskService_Tasks AS task ON (task.ID = task_ID)
)

I added the full log to the attachments. The interesting part in my opinion is this:

...
2021-11-08T12:23:18.950+0000 [APP/TASK/deploy/0] OUT      Precompiling "src/gen/TaskService.Tasks.hdbview$TASKSERVICE_TASKS.validate"... ok  (0s 8ms)
2021-11-08T12:23:18.950+0000 [APP/TASK/deploy/0] OUT       Error: com.sap.hana.di.view: Syntax error: "incorrect syntax near "WITH"" [8250009]
2021-11-08T12:23:18.950+0000 [APP/TASK/deploy/0] OUT         at "src/gen/TaskService.PrecedingAssignments.hdbview" (10:1)
...
2021-11-08T12:23:18.950+0000 [APP/TASK/deploy/0] OUT     Error: Precompiling... failed [8212133]
2021-11-08T12:23:18.951+0000 [APP/TASK/deploy/0] OUT    Error: Calculating dependencies... failed [8212108]
2021-11-08T12:23:18.951+0000 [APP/TASK/deploy/0] OUT    Make failed (5 errors, 2 warnings): tried to deploy 11 files, undeploy 0 files, redeploy 0 dependent files 
2021-11-08T12:23:18.951+0000 [APP/TASK/deploy/0] OUT   Error: Making... failed [8211605]
2021-11-08T12:23:18.951+0000 [APP/TASK/deploy/0] OUT  Error: Starting make in the container "B5AFE7C2D5814885BC7F6932DAF4823D" with 11 files to deploy, 0 files to undeploy... failed [8214168]
2021-11-08T12:23:18.952+0000 [APP/TASK/deploy/0] ERR Deployment to container B5AFE7C2D5814885BC7F6932DAF4823D failed - error: HDI make failed [Deployment ID: none].
2021-11-08T12:23:18.952+0000 [APP/TASK/deploy/0] OUT Deployment ended at 2021-11-08 12:23:18
2021-11-08T12:23:18.952+0000 [APP/TASK/deploy/0] ERR Error: HDI make failed

When removing the GROUP BY and HAVING statements from the view then the view can be deployed onto SAP HANA Cloud...

Any help is very much appreciated.

[hjb · Accepted solution]

Hi Manuel,

having max(something)

is not a sufficient condition.

Try with sth like:

having max(something) < 42

[M_Zimmermann · Accepted solution]

Thank you very much. I was a bit confused because in SQLite this is working, but according to the SAP HANA documentation HAVING expects a boolean expression so you're correct. I've rewritten my query without using HAVING.