CDS Delete adjacent records

[bharath_padmanabhan]

Experts,

I am creating a CDS view. Input: Material, Plant and Storage Location. I am filtering data from MSEG for Matl, plant and Stor.loc combination. My requirement is to fetch the latest Posting date(MSEG-BUDAT_MKPF) against Material and Plant only(Ignoring Stor.loc).

In a normal ABAP report, I will do the below:

I am not sure how I can do the same in CDS. NOTE: I need stor. loc for rest of my logic. That is why its part of my input values. Just when it comes to getting the latest posting date, I want to ignore stor. loc. One though was to create a CDS view which has Matnr, werks and lgort. Get the data in hand and then call another CDS view with matnr and werks alone. Get the data and then join the views. This will not work out as the 1st view will give me the data against matnr, plant and stor loc(which the user has entered in sel. screen). I need to find all the MSEG records for these stor. locations and then from that list, get the recent posting date for Matl and plant. Another thought was to use table functions. I see that I can't use a CDS view inside table func. If that were possible, I could pass my 1st CDS view inside a table func and then simply select max(BUDAT_MKPF), matnr and werks which will give me the latest posting date. Table func only allows using DB tables inside it.

If we think, we can simply do select max(budat_mkpf), matnr, werks, lgort -> This will give me the latest posting date for each Matl, plant and stor loc which will be several records. I want a single record against Matl and plant for the latest posting date.

Is there a way we can achieve this in CDS?

Thanks

Bharath

[bharath_padmanabhan]

I see that SAP allows consuming a CDS view inside a table func from 7.55 and above.
Blog: https://blogs.sap.com/2022/12/03/consume-cds-view-inside-cds-table-function-by-using-amdp/

I am on 7.54. So I can't use define view entity yet. I am still creating DDIC based CDS view which is Define view <<view name>>.

[bharath_padmanabhan]

Experts,

I was able to solve my problem using Window expression.

My Table function code is as below:

My CDS view that calls the table func is as below:

I learnt about Window expressions from this LINK

If you followed my question, the challenge that I had is that I need to fetch the records against Matl, plant as well as Stor loc first. From the result set, I need to get the max(budat_mkpf) posting date value against Matl and Plant. Window expression uses a keyword called PARTITION where I have mentioned matnr and werks. FIRST_VALUE is the keyword that picks that maximum posting date value. So using these 2 keywords, I was able to mimic sort and delete adjacent duplicates in normal ABAP report.

My table func returns multiple records as I am also returning Stor loc as one of the fields. So I created 1 more CDS view that will filter out multiple records and will only give me 1 record in hand.

I am pretty sure there will be a lot of useful blogs in our community about Window expressions. I am simply sharing the link that I got from Google which helped with the resolution.

I am stunned to see the way we can play with data using Window expressions. To achieve the same kind of output, we have to use gazillion temporary internal tables in traditional ABAP programming.

Hope this helps.

Thanks

Bharath

[bharath_padmanabhan]

Experts,

I realized that my earlier solution is still returning multiple records for a Matl and Plant. I celebrated too early. I was able to add 1 more Window expression to solve the problem. My table func code has been changed slightly as below,

When I passed Stor location as 1001, 1002 and 1003, my earlier code returned 3 records with latest posting date for each of the Stor location. I used another Window expression called ROW_NUMBER. You can refer to the link I posted in my initial reply to understand about ROW_NUMBER. What it does is that it simply assigns a row number for the PARTITION you create. In my case, I am creation a partition based on Mandt, Matl and Plant and I was getting 3 records. Row Number column will have values as 1,2 and 3 for each record. In my table func, I have written where clause to return row_number = 1. This way, only 1 record will be returned instead of 3. This solved my problem.

Thanks

Bharath

[bharath_padmanabhan]

LINK

This thread is similar to the issue that I faced which got solved using ROW_NUMBER function.

[Sandra_Rossi]

I know that it's not your question, but the way it's written is weird:

SORT itab BY a b ascending c descending.

which means a with default sorting, b ascending, c descending.

Here you didn't define a default sorting, so it's ascending by default, which means is also a ascending.

So, why not explicitly mentioning it, as you did for b? Or considering all is ascending by default except c.

SORT itab BY a ASCENDING
             b ASCENDING
             c DESCENDING.

which is the same as:

SORT itab BY a b c DESCENDING.

NB: below the default sorting order is indicated explicitly, it doesn't make sense as ABAP developers know that SORT itab BY a is in ascending order:

SORT itab ASCENDING BY a b c DESCENDING.

[bharath_padmanabhan]

Hi Sandra,

Honest answer - I have been tasked with converting an existing ABAP program to CDS view. This is needed as we are planning to expose this CDS as Odata and let BW team consume the data. A different developer had written sort in this fashion and I ended up copying it.

I totally agree with your suggestion. I personally prefer to call out the sort order for every element as it makes the code more readable in my humble opinion even though ASC is the default sort order.

Thanks

Bharath