CDS table function - SELECT TOP 1

[Former Member]

Hello gurus,

I have a requirement to do join in CDS view and I need only first found row from the right table for every record from the left table. As I searched through forum here, I had found I need to use CDS table function and implement SQL script in my method. However, it does not work as planned.

Here is simple example:

Imagine I have EKKO (purchase order header) table. For each purchasing document (EKKO-EBELN) I want to find first found material from EKPO (purchasing document items - EKPO-MATNR).

1) I have created table function:

2) I have created AMDP class implementation:

3) I use my table function in CDS view:

4) The result looks like this:

The result I want is first matnr for each document.

Any help would be highly appreciated.

Thank you.

Lukas

[Former Member]

Hi Dominik, the X and SPACE won't work unfortunately.

Take a look:

1) EKPO table looks like this:

2) I changed the select top 1 to select max(matnr)

3) The result looks like this:

4) But when I need different material, it won't work:

I have also tried different associations, but none of them seems to work as well.

Lukas

[Former Member]

So after trying many different approaches I found out following:

1) I tried SELECT TOP 1 in TABLE FUNCTION but when there are no restrictions in where conditions, it seems that within join condition it takes all of the records from left table and does join with right table in one batch - which mean only one record from right table for all of the records from left table.

2) I tried select max(matnr). It works only until you want restrict matnr in where condition.

3) I tried correlated sub-querry with select top 1 matnr in that sub-querry. HANA does not like correlated sub-query with TOP or GROUP BY clause.

4) I tried window functions like RANK( ) or ROW or FIRST( ). Nothing works when you want to restrict it during SELECT statement.

5) LOOPing throuh data in tabe function will significantly reduce performance.

I decided to do all necessary data adjustments in ABAP layer. If anyone will find out the way how to do it in CDS or table funcion, please feel free to add your comment.

Lukas