"historic" view via max(dateField) and group by errors on hdbview creation in HANA

[vobu]

if `$children` (association to) of a `$parent` are `SELECT`ed via an the `max()` aggregation function, and then "`group by`" a property, HANA errors out on view creation and wants _all_ properties of a `$children` referenced in the `SELECT`.

Funny enough, the exact same view/cds statement works on SQLite.

taking Gregor's infamous beershop as a "pidgin code" example (yeah, cds-pg!)

schema:

// parent
entity Brewery : cuid {
  // ... other props here
  beers : Association to many Beers on beers.brewery = $self;
};
// child
entity Beers : managed, cuid {
  prop1 : String;
  prop2 : String;
  prop3 : String;
  brewery : Association to one Brewery;
};

service/view:

service BeershopService {
  entity Breweries as projection on my.Brewery;

  entity Beers as
    select from my.Beers {
      prop1,
      prop2,
      max(modifiedAt) as latestModifiedAt : DateTime
   }
   group by 
     prop2;
};

Expected behaviour: Given several Beers are inserted with _the same `prop2`_, only the one with the latest `modifiedAt` is displayed when an $expand from Breweries is called:

`http://.../Breweries?$expand=beers`

Actual behaviour:

- works well in SQLite

- doesn't work in HANA, b/c already view creation errors out with:
Error: (dberror) [xxx]: not a GROUP BY expression: 'BEERS_0.BREWERY_PROP1' must be in group by clause

So, the question is: how to do the above SELECT in HANA-compatible cds?

[steffen_weinstock · Accepted solution]

On HANA you could use the function "last_value":

create table B (prop1 int, prop2 int, modified datetime);
insert into B values (7, 1, '2022-08-09 10:00');
insert into B values (8, 1, '2022-08-10 11:00');
insert into B values (9, 1, '2022-08-11 12:00'); -- <-
insert into B values (4, 2, '2022-08-08 20:00'); -- <-
insert into B values (5, 2, '2022-08-08 19:00');
insert into B values (6, 2, '2022-08-08 18:00');
SELECT last_value(prop1 order by modified), prop2, max(modified) FROM B group by prop2;
==>
4; 2; 08.08.2022 20:00:00.0
9; 1; 11.08.2022 12:00:00.0

[vobu · Accepted solution]

this is the way on HANA, thanks!

However, within CAP and cds, SQLite demands the window function to be:

last_value(pos2) over (
  order by modified
) as _alias: <type>

And somehow within the dark magic of the cds framework ;), the corresponding "WITH" association beers → brewery in combination with above window function still doesn't pan out for my use case.

So, overall I couldn't achieve my use case with the desired design.

But accepting Steffens answer, given it's the way this works on HANA.