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

[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