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_member284740]

You could try it with simple select in your ABAP (so no CDS)

SELECT ... UP TO 1 ROWS 

and don't forget ORDER BY PRIMARY KEY

[Former Member]

Hi Guus,

sorry, it won't help me. I need to use CDS.

Regards,

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

[Domi]

Hi Lukas

You need to select the "TOP 1" for each EBELN

    RETURN SELECT ebeln,
                  max( matnr ) AS matnr FROM ekpo GROUP BY ebeln;

regards

Domi

[Former Member]

Hi Dominik,

thank you for your answer. This is going to work fine as long as I don't need to filter according to some specific column. Let's say I have different scenario. Instead of field MATNR I want to select LOEKZ - Deletion indicator in purchasing document. In the Fiory app, I will have filter on LOEKZ = 'X' or EMPTY. In this case, I can not apply max function on that field because max(LOEKZ) will be always looking for 'X' if I am not mistaken.

Lukas

[Domi]

Hi Lukas

X and SPACE can be achieved with MAX and MIN -

but I think what you are looking for are associations - especially the part

association [1] to

https://help.sap.com/doc/abapdocu_750_index_htm/7.50/de-DE/abencds_f1_association.htm

regards

Domi

[Former Member]

I have post here another answer. Direct reply with screenshot was not possible.

Lukas