2023 Feb 08 3:22 PM
Hi everyone,
I have the next table
| KEY | FIELD1 | FIELD2 | CHANGED |
| 1 | 001 | TEXT1 | 01/02/2023 |
| 2 | 001 | TEXT2 | 02/02/2023 |
| 3 | 002 | TEXT3 | 03/02/2023 |
I need to build a CDS that returns me the last changed record comparing FIELD2, so the result should be
| KEY | FIELD1 | FIELD2 | CHANGED |
| 2 | 001 | TEXT2 | 02/02/2023 |
| 3 | 002 | TEXT3 | 03/02/2023 |
Is it possible?
Thanks in advance!
2023 Feb 09 5:43 AM
The sensible way of doing this is to write a CDS table function, and do the heavy lifting in AMDP / SqlScript.
To make this with pure CDS, you can split the query into multiple CDS views; which (in my opinion) won't make sense in terms of simplicity and performance. CDS1 would return the MAX( CHANGED ) (grouped by FIELD2). CDS2 would join CDS1 with the table, matching FIELD2 and MAX( CHANGED ). But, you may get multiple results for each FIELD2.
2023 Feb 09 7:45 AM
Hi Kerum,
I can't do this way, because the records were initially loaded from a file, so some of them have the same changed date.
I build a table function, but it takes 3.5 seconds for 3200 entries.
2023 Feb 09 8:10 AM
Depending on your table you could to a group by with MAX( CHANGED ). Something like:
Select KEY, FIELD1, FIELD2, MAX(CHANGED) as lastChangedAt from table
As alternative you could do the grouping in a seperate CDS View and Join the grouped table on KEY, FIELD1 and FIELD2. Like
define root view entity ZI_MR_SONABL_BP
as select distinct from table
join table_grouped on table.key = table_grouped.key and table.field1= table_grouped.field1 and table.changed = table_grouped.changed