Skip to Content
0
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

Hello

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 = Main.id)

but I want

(SELECT COUNT (*) FROM Item WHERE Item.parent_id = Main.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)