Cloud Application Programming (CAPM) virtual fields DB

[former_member194549]

Hello everybody,

If a field of an entity is flagged as virtual in the cds model, no field is created on the corresponding database table. So far everything is fine.

If this entity is now called with a filter on the virtual field using a GET request, the following error is returned:

<error xmlns="http://docs.oasis-open.org/odata/ns/metadata">
	<code>500</code>
	<message>
		Cannot set parameter at row: 1. Argument must be a string
	</message>
</error>

During debugging I also came across the following exception text: (Property VHierarchyNodeLevel is marked as virtual)

SELECT a.modifiedAt AS "a_modifiedAt", a.createdAt AS "a_createdAt", a.createdBy AS "a_createdBy", a.modifiedBy AS "a_modifiedBy", a.VId AS "a_VId", a.VName AS "a_VName", a.VHierarchyNodeLevel AS "a_VHierarchyNodeLevel" FROM Service_PlanningUnits a WHERE a.VHierarchyNodeLevel <= ? LIMIT 128 OFFSET 0

Is it possible that although a field has been flagged as virtual, a virtual field is added to the WHERE clause of the DB query if a filter exists for it in the request?

Following the CDS entity, the virtual fields are filled in on.after("READ", ...).

entity PlanningUnit : managed {
  key VId                            : UUID;
      VName                          : String;
      virtual VHierarchyNodeLevel    : Integer;
}

[Daniel7]

Might be related...

Hello, As promised, I just added a sample how to add calculated fields to a branch in our public bookshop sample at https://github.com/SAP-samples/cloud-cap-samples/blob/a6ae7cfc8db22daf007138f6f206eba602345b39/packa...

service AdminService { ...
  entity OrderItems as select from my.OrderItems {
    *,
    amount * book.price as netAmount @(Core.Computed) : Decimal(9,2)
  };
}

The mechanisms are:

• Projections can contain any/most allowed SQL expressions, including uses of Association Path Expressions like book.price above
• You need to add a CDS-style type cast (? the : Decimal(9,2) in this case)
• A common pattern is to use *, <… specific other columns > where * in CQL is handled a bit smarter than in standard SQL

As these calculated fields are turned into SQL views you can arbitrarily filter and sort by them.

You don't have to add the @Core.Computed annotation explicitly as shown in the sample, as we do that automatically whenever we expose calculated fields to OData.

You can also check what that CDS definitions get turned into when mapped to SQL databases by doing this in the samples root: cds packages/bookshop/srv/admin-service.cds -2 sql > t.sql

E.g. the exposed entity above is turned into this CREATE VIEW statement:

CREATE VIEW AdminService_OrderItems AS SELECT
  OrderItems_0.ID,
  OrderItems_0.amount,
  OrderItems_0.amount * book_1.price AS netAmount,
  OrderItems_0.parent_ID,
  OrderItems_0.book_ID
FROM (sap_capire_bookshop_OrderItems AS OrderItems_0 LEFT JOIN sap_capire_bookshop_Books AS book_1 ON (OrderItems_0.book_ID = book_1.ID));

Regards,
Daniel