Skip to Content
Jul 04, 2019 at 03:21 AM

CD&S count of child elements in main table

275 Views Last edit Jul 04, 2019 at 03:22 AM 2 rev


I have main and child entities

    entity Main{
        key id            : TKey;
            description   : TDescription;
            items: Association to many Item on Item.parent_id = $self;

   entity Item{
     key parent_id : TKey;
     key pos       : Integer

    define entity VMain AS SELECT FROM Main{
         *, count(r.parent_id) as ch_cnt

the statement above generates this SQL code

COUNT(Item.parent_id) AS ch_cnt,

FROM (Main LEFT JOIN Item ON Item.parent_id =

but I want

(SELECT COUNT (*) FROM Item WHERE Item.parent_id = AS ch_cnt

without any join (because right now I have 1 item in VMain)

Is it possible to create it via CDS? (not calculation view)