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.

Answer 1

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.