Get count of distinct key field values from CDS

Question

I would like to ask if it is possible to get dynamically Count of distinct fields using ABAP.

Key in our CDS has 9 fields which is quite a lot but it is not possible to split because of historical decisions. What I need is code like below:

select count(distinct (lv_requested_elements)) from CDS_VIEW;

or

select count(*) from (select distinct lv_requested_elements from CDS_VIEW);

I know that it is possible to read the select into memory and get sy-dbcnt but I want to be sure that there is no other option.

What SAP_BASIS release do you have? When you already got 7.51 you could do this with a WITH...SELECT. — Philipp
We have the newest release so 7.51 is OK. Could you please send any page or documentation how to provide such a statement using WITH...SELECT ? I was not able to provide that without reading values into memory. — bagere

Suncatcher Suncatcher · Accepted Answer · 2020-12-17T16:01:57

I assume that most simple and straightforward way is to read the smallest field into memory and then count by grouped (distinctified) rows:

DATA(fields) = ` BLART, BLDAT, BUDAT`.

DATA: lt_count TYPE TABLE OF string.
SELECT (fields(6))
  INTO TABLE @lt_count
  FROM ('BKPF')
 GROUP BY (fields).

DATA(count) = sy-dbcnt.

CTE, that was mentioned, uses the same memory read, so you'll receive no performance gain:

A common table expression creates a temporary tabular results set, which can be accessed during execution of the WITH statement

If you going to count this key combination frequently, I propose to create consumption or nested CDS view which will do this on-the-fly.

Get count of distinct key field values from CDS

1 Answers