CAP - PostgreSQL - returns all lowercase fields

[kevin_balboni1]

Dear SAP Community,

while managing a CAP project with PostgreSQL I noticed an operating anomaly.

In a select with left join all fields are returned lowercase even though they have been defined differently in the database.

DB statement:

OUTPUT Select:

Here I leave you the github repository with which to test this anomaly:

https://github.com/kevinbalboni/cap-postgres-select-error

I ask for your support in this problem.

Thank you!

[gregorw]

I suggest you file an issue in https://github.com/cap-js/cds-dbs.

[kevin_balboni1]

Thank you gregor.wolf ,

I opened an issue here: https://github.com/cap-js/cds-dbs/issues/389

[kevin_balboni1 · Accepted solution]

Hi everyone,

I bring you what emerged from the discussion with the CAP team:

CAP do not perform post processing on query with join conditions.

They suggest to use path expressions instead. With this, the query is easier to read and CAP can optimise it for you.

This would be the query with path expression:

 await cds.tx(req)
.run(
  SELECT.from('sap.capire.bookshop.UIAggregations as A')
  .where({
   'uiElements.uiElement_name': 'JSONView', //> use path expression
   'uiElements.uiElement_version': '1.96.0', //> use path expression
   'A.isEnabled': true
  })
);<br>

SAP is currently working on @cap-js/hana, it's already available as early alpha (not production-ready yet). With the new hana service, you will get the same results for the query. As of now, the hana service you are using is still part of the @sap/cds package, which is a completely different stack.

With all @cap-js databases, SAP provide a richer feature set, with unified results across all databases. The most powerful feature being the path expressions.

Many advanced features supported by the new database services, like path expressions or deep expands, rely on the ability to infer queries from CDS models. This task gets extremely complex when adding UNIONs and JOINs to the equation — at least the effort and overhead is hardly matched by generated value. Therefore, we dropped support of UNIONs and JOINs in CQN queries.

If someone really needs UNION or join expressions, the best approach is to define a static view and select from them at runtime.