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;
}

[david_kunz2 · Accepted solution]

Dear Simon Perstorfer,

Thanks a lot for giving us this detailled analysis. Indeed, a virtual field must not appear in the WHERE clause of a SQL statement.

We will work on a fix to prevent this from happening in the future.

However, it is strongly recommended to not apply filter conditions on virtual fields since the performance will be drastically reduced (all items have to be fetched from the database, for every entry the virtual field must be calculated and only then the result set is filtered).

Thanks again and best regards

David

[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