Skip to Content
avatar image
Former Member

CDS table function - SELECT TOP 1

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

tf.jpg (62.3 kB)
amdp.jpg (196.4 kB)
cds.jpg (103.3 kB)
result.jpg (90.6 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Mar 02 at 08:18 PM

    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
    Add comment
    10|10000 characters needed characters exceeded

  • Mar 02 at 08:46 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 06 at 12:23 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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