Creating an entity with unique values from another

[tiago_almeida2]

Hey guys,

I'm wondering if the CDS syntax is expressive enough to be able to project unique values of one entity into another. I can't find examples on this that work with the sap/cds js module.

Suppose we have the Books entity used in the many examples, enhanced with a countryCode which represents where the book was written. I know, I'm assuming a book is only written in one country and no book was written in space. Bear with me for this example please 🙂

entity Books {  
key ID : Integer;  
title  : String;  
stock  : Integer;
countryCode : String; // Example GB,DE, etc.
}

What we want is a unique list of countries as a "Country" entity without having to explicitly model that as another entity with association.

I understand we can use the $apply=groupby() on the Books EntitySet to get the unique values of that column, but can we build a cds view with the unique values of books.countryCode?

Below is something I've tried but is not completely correct. It works locally (on sqlite3) but fails deploying to SCP (Error: com.sap.hana.di.cds: Path is missing in GROUP BY clause [1310257] )

view Countries as select from Books group by countryCode;

Any ideas on how to achieve this in way that works anywhere?

Thank you very much.

Tiago

[hjb · Accepted solution]

Hi Tiago,

this is no limitation of CDS but a requirement of HANA SQL. Please read the documentation on

GROUP BY Clause

NoteEvery expression in the GROUP BY clause must also be in at least one SELECT column expression. Note that the GROUP BY clause must reference input columns directly. It cannot use aliases defined in the local SELECT clause.You are selecting all elements from Book but apply ORDER BY only on countryCode.Regards, Hans-Joachim

[tiago_almeida2 · Accepted solution]

Thanks for the clarification Hans-Joachim