topic Counting by ID in CDS view in Application Development and Automation Discussions

Counting by ID in CDS view

former_member782701 — Thu, 23 Dec 2021 16:43:08 GMT

Hey,

I would like to count the records that are associated to the same ID.

For example:

ID | DOCUMENT_NUMBER | COUNT 
1000       200             1 
1000       300             2 
2000       400             1 
3000       500             1 
3000       600             2

In SQL this was possible using the below statement.

ROW_NUMBER( ) OVER( PARTITION BY ID ) AS COUNT

However, is this also possible using CDS views?

Regards,

Dhruv

Re: Counting by ID in CDS view

NaveenKumarC — Fri, 24 Dec 2021 03:55:41 GMT

Hello Dhruv,

I think it is not possible to use the ROW_NUMBER in the CDS. But this can be achieved either by using Table Function or create a virtual element in the CDS and fill the value.

Regards,

Naveen Kumar C

Re: Counting by ID in CDS view

former_member688242 — Sat, 25 Dec 2021 06:57:07 GMT

It is not possible to use the ROW_NUMBER in the CDS.but altrnatively you can achieve through join

Left table: the original table you have

right table: using group by statement to get number of times id is repeated

perfom a left outer join to achieve the required output

Re: Counting by ID in CDS view

former_member782701 — Mon, 27 Dec 2021 10:03:33 GMT

In my case I generate the original table using the following snippet:

define view entity <view_name>
as select from <view_1>
inner join   <view_2> on  <view_2>.field= <view_1>.field {

...

}
group by
...

The above view would provide the following output:

ID | DOCUMENT_NUMBER
1000       200
1000       300
2000       400
3000       500
3000       600

Now, I am not sure how to integrate a left outer join into that scenario. My left table would be the above view. However, what would be the right table?

Re: Counting by ID in CDS view

former_member688242 — Wed, 29 Dec 2021 10:50:25 GMT

can you please try consume one cds view in another and link from sap: link