SAP CAP error using ROW_NUMBER() in services

Epena — Sun, 08 Oct 2023 15:14:40 GMT

Hello,

As far as I understand, as of Dec 2021 window functions are supported. I can use them just fine in model definition, however, I'm having issues trying to use them in services, as "cds watch" won't start.

Here's my data model:

namespace my.shop;

entity Products {
  key id     : UUID;
      name   : String;
      prices : Association to many PriceHistory
                 on prices.product = $self;
}

entity PriceHistory {
  key product   : Association to one Products;
  key priceDate : Timestamp @cds.on.insert: $now;
      price     : Double;
}


define view Test as
  SELECT from PriceHistory{
    product.id as productId,
    priceDate,
    price,
    ROW_NUMBER() OVER (PARTITION BY product.id ORDER BY priceDate DESC) AS rn
  };

If i deploy this to SQLITE using "cds deploy", it works just fine: tables are created and csv files are loaded, I can even connect to the local db.sqlite, run a query against table "Test" and get back the results:

However, when I expose entity Test in a service, cds watch won't start.

Here's my service:

using my.shop as my from '../db/data-model';

service CatalogService {
    entity Products     as projection on my.Products;
    @cds.redirection.target: true
    entity PriceHistory as projection on my.PriceHistory;
    entity Test         as projection on my.Test;
}

Here's what happens when I run cds watch:

As you can see, it doesn't get to the part where it listens on a port.

If I comment out the column containing the row function, cds watch works again:

namespace my.shop;

entity Products {
  key id     : UUID;
      name   : String;
      prices : Association to many PriceHistory
                 on prices.product = $self;
}

entity PriceHistory {
  key product   : Association to one Products;
  key priceDate : Timestamp @cds.on.insert: $now;
      price     : Double;
}


define view Test as
  SELECT from PriceHistory{
    product.id as productId,
    priceDate,
    price,
    // ROW_NUMBER() OVER (PARTITION BY product.id ORDER BY priceDate DESC) AS rn
  };

Here's a small repo with my model and service in case anyone wants to try it out:

https://github.com/edg1506/CAP_TEST_PRODUCTS

And here's my "cds -v":

Regards.

Re: SAP CAP error using ROW_NUMBER() in services

Willem_Pardaens — Mon, 09 Oct 2023 05:07:09 GMT

I noticed as well that `cds watch` doesn't return full error descriptions but just stops execution. To know the error you can use `cds run` which will display it correctly. As you can see, it complains about a missing key and a missing data type, so a working version of your view would be:

define view Test as
  select from PriceHistory {
    key product.id as productId,
        priceDate,
        price,
        ROW_NUMBER() over(
          partition by product.id order by priceDate desc
        ) as rn : Integer
  };

Thanks for providing the git, that makes it easy to have a look and resolve.

Re: SAP CAP error using ROW_NUMBER() in services

Epena — Mon, 09 Oct 2023 11:23:55 GMT

Thanks, didn't realize that 'cds run' displays the error!

Question Re: SAP CAP error using ROW_NUMBER() in services in Technology Q&A

SAP CAP error using ROW_NUMBER() in services

Re: SAP CAP error using ROW_NUMBER() in services

Re: SAP CAP error using ROW_NUMBER() in services