When querying H2 I get a SQL Syntax Error 42001-197

Question

I am by no means a SQL expert, nor H2, so feel free to drag me through the coals. I have two tables, one called ORDERS and one called PRODUCTS, and I'm trying to show the top three selling categories for each year available in the data.

When running the following SQL:

WITH year_categories AS ( 
    SELECT EXTRACT(YEAR FROM ORDERS.CREATED_AT) AS year, 
    PRODUCTS.CATEGORY AS "Category", 
    SUM(ORDERS.TOTAL) AS total_sales 
    FROM ORDERS JOIN PRODUCTS 
    ON ORDERS.PRODUCT_ID = PRODUCTS.ID 
    GROUP BY year, PRODUCTS.CATEGORY 
) 
SELECT year, "Category", total_sales 
FROM ( 
    SELECT year, "Category", total_sales, 
    ROW_NUMBER() OVER (PARTITION BY year ORDER BY total_sales DESC) as row_num
    FROM year_categories 
) sub 
WHERE sub.row_num <= 3 
ORDER BY year, total_sales DESC;

I get the following error:

Blockquote Syntax error in SQL statement " WITH YEAR_CATEGORIES AS ( SELECT EXTRACT(YEAR FROM ORDERS.CREATED_AT) AS YEAR, PRODUCTS.CATEGORY AS ""Category"", SUM(ORDERS.TOTAL) AS TOTAL_SALES FROM ORDERS JOIN PRODUCTS ON ORDERS.PRODUCT_ID = PRODUCTS.ID GROUP BY YEAR, PRODUCTS.CATEGORY ) SELECT YEAR, ""Category"", TOTAL_SALES FROM ( SELECT YEAR, ""Category"", TOTAL_SALES, ROW_NUMBER() OVER (PARTITION[*] BY YEAR ORDER BY TOTAL_SALES DESC) AS ROW_NUM FROM YEAR_CATEGORIES ) SUB WHERE SUB.ROW_NUM <= 3 ORDER BY YEAR, TOTAL_SALES DESC; "; expected ")"; SQL statement: WITH year_categories AS ( SELECT EXTRACT(YEAR FROM ORDERS.CREATED_AT) AS year, PRODUCTS.CATEGORY AS "Category", SUM(ORDERS.TOTAL) AS total_sales FROM ORDERS JOIN PRODUCTS ON ORDERS.PRODUCT_ID = PRODUCTS.ID GROUP BY year, PRODUCTS.CATEGORY ) SELECT year, "Category", total_sales FROM ( SELECT year, "Category", total_sales, ROW_NUMBER() OVER (PARTITION BY year ORDER BY total_sales DESC) as row_num FROM year_categories ) sub WHERE sub.row_num <= 3 ORDER BY year, total_sales DESC; [42001-197]

What am I doing wrong? I've tried inserting ")" in a couple of different locations after ROW_NUM, with no different result. Alternatively, is there an easier or better way to structure this query?

Thank you for any help in advance!

score 1 · Answer 1 · answered Jan 12 '23 at 13:19

1

You're using an entirely outdated and unsupported H2 1.4.197. This version doesn't support window functions.

You need to upgrade to some modern version of H2.

Please note that YEAR is a keyword in them, so you need to quote it. Alternatively you can add ;NON_KEYWORDS=YEAR to JDBC URL as a temporary workaround.

answered Jan 12 '23 at 13:19

Evgenij Ryazanov

6,960
2
10
18

I suppose you mean _reserved_ keyword. – jarlh Jan 12 '23 at 13:57
Are all H2 keywords also reserved? – jarlh Jan 12 '23 at 14:00
Is there a workaround for this without needing to update H2? – Doctor DIRE Jan 12 '23 at 15:29
You can try to rewrite your query without window functions: https://stackoverflow.com/questions/1442527/how-to-select-the-newest-four-items-per-category – Evgenij Ryazanov Jan 12 '23 at 15:41
1

@jarlh The SQL Standard has reserved words and `YEAR` is one of them. H2 has own keywords / reserved words (both terms have the same meaning) and they cannot be used as unquoted identifiers: https://h2database.com/html/advanced.html#keywords – Evgenij Ryazanov Jan 12 '23 at 15:45
I see. (The SQL standard distinguishes between reserved words and non-reserved words.) – jarlh Jan 12 '23 at 16:25

When querying H2 I get a SQL Syntax Error 42001-197

1 Answers1