CDS Table function example to determine number of records

[dschiener]

Hi there,

currently I am on an example for a CDS table function to determine the number of records of a specific attribute.
Note: This is just an example for me to unterstand the functionality of such a CDS table function.
Please do not suggest different solutions like "for your use case you don't need a table function etc."
My use case is simplified!

Unfortunately, I don't get the solution on my own at the moment. Some parts I don't understand yet... 

This is my use case:

Given database table:

ID

id_1

id_1

id_1

id_2

id_2

id_3

Using the table function I want to get a result of the following structure:

ID	number
id_1	3
id_2	2
id_3	1

So the number column shall only contain the count of occurrences of each ID.

This is what I got in coding yet:

CDS Table function:

@EndUserText.label: 'Test'
@ClientDependent: false
define table function MY_TABLE_FUNCTION
returns {
id : z_id;
number : z_number;
}
implemented by method zcl_table_function=>read;

AMDB class:

CLASS zcl_table_function DEFINITION
PUBLIC
FINAL
CREATE PUBLIC.

PUBLIC SECTION.
  INTERFACES if_amdp_marker_hdb.
  
  CLASS-METHODS read FOR TABLE FUNCTION my_table_function.
ENDCLASS.

CLASS zcl_table_function IMPLEMENTATION.
  METHOD read BY DATABASE FUNCTION
              FOR HDB
              LANGUAGE SQLSCRIPT
              OPTIONS READ-ONLY
              USING my_table.

  return select my_table.id from my_table as my_table;

ENDMETHOD.
ENDCLASS.

Class to call table function:

CLASS zcl_table_function_call DEFINITION
PUBLIC
FINAL
CREATE PUBLIC.

PUBLIC SECTION.
  METHODS call.
ENDCLASS.

CLASS zcl_table_function_call IMPLEMENTATION.
  METHOD call.
    SELECT * FROM my_table_function( )
      INTO TABLE (result).
  ENDMETHOD.
ENDCLASS.

So. What now is still missing is the completion of the table function implementation itself.
How do I count the records there and build up the result table?
A prefered solution for me would be with the usage of temporary table variable in the table function to store the numbers.

Can anyone give me some hints?

BR
Daniel

[SachinArtani]

Why are you returning only id in select query in read method if you need count of IDs as well.

METHOD read BY DATABASE FUNCTION
              FOR HDB
              LANGUAGE SQLSCRIPT
              OPTIONS READ-ONLY
              USING my_table.

    return select my_table.id,
                  count(*) as number
            from my_table
            group by my_table.id;

ENDMETHOD.

It's simple.

[dschiener]

@SachinArtani thank you for your reply.

But my prefered solution would be to separate the return and the select(s) ifself.

I am looking for a solution that stores the select result(s) in a temporariy table variable that I return at the end.

Remember again. My use case is simplified!

I would like to know how to stores several select calls in different local table variables to merge them in the end to one result table.

[SachinArtani]

I believe you are talking about keeping results in temporary table/variable and use them further to make a final result set for the table function. 
Check this out -
Handling SELECT OPTIONS in CDS using Table Functio... - SAP Community

In examples of above blog, you could see him keeping result in table and making use of them to prepare a final result set - 

[dschiener]

---

@SachinArtani wrote:

I believe you are talking about keeping results in temporary table/variable and use them further to make a final result set for the table function. 

---

Yes. That's my use case.

Let's say I want to read data from different database tables, e.g. info types and restructure this data to a final result of my table function. Is this possible?

In ABAP I would implement something like this:

TYPES BEGIN OF record_result.
TYPES record_db_tab_1 TYPE db_tab_1.
TYPES record_db_tab_2 TYPE db_tab_2.
TYPES END OF record_result.

TYPES records_result TYPE STANDARD TABLE OF record_result WITH EMPTY KEY.

DATA db_tab_1 TYPE db_tab_1.
DATA db_tab_2 TYPE db_tab_2.

SELECT
  FIELDS *
  FROM db_tab_1
  INTO TABLE db_tab_1.

SELECT
  FIELDS *
  FROM db_tab_2
  INTO TABLE db_tab_2.

DATA result TYPE records_result.
DATA record TYPE record_result.
record-db_tab_1 = db_tab_1.
record-db_tab_2 = db_tab_2.

INSERT record INTO TABLE result.

How could I do this using a table function?
I want to do the data reading and restructuring in the table function.

BR

[chengalarayulu]

Hi, you can use RANK() db function.