Counting by ID in CDS view

[former_member782701]

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

[NaveenKumarC]

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

[former_member688242]

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

[former_member782701]

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?

[former_member688242]

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