Skip to Content
1
Dec 02, 2022 at 03:21 PM

How to handle date field in HANA docstore for group by sql statement

271 Views

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) :

image.png

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 :

image.png

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 !

Attachments

image.png (5.5 kB)
image.png (4.2 kB)