Hello,
I'm having trouble with the request below and I think the problem is in link with the date field. Here is my request :
WITH max_sales AS (SELECT "product", MAX(CAST("order_date" AS DATE)) AS "max_order_date" FROM Product_sales_docstore GROUP BY "product"),
prod2 AS (SELECT "product", CAST("order_date" AS DATE) AS "order_date", "sale" FROM Product_sales_docstore)
SELECT prod2.* FROM prod2 INNER JOIN max_sales
ON max_sales."product" = prod2."product"
AND max_sales."max_order_date" = prod2."order_date";<br>
And here is my collection of documents (the order_date is just a json string) :

My goal was to obtain a table with only the most recent records (based on the "order_date" so) knowing my primary key is "product". I expected a result like this :

But I've this error :
Could not execute 'WITH max_sales AS (SELECT "product", MAX(CAST("order_date" AS TIMESTAMP)) AS "max_order_date" FROM ...'
SAP DBTech JDBC: [7]: feature not supported: unsupported expressions in views or WITH clauses with COLLECTION tables<br>
The problem seems to be the max(date field) but I can't find how to solve this. Thank you a lot for answers or ideas !