1

Using H2 database (version 1.4.200) I've encountered really strange error with usage of window functions inside CTE. When I include window function field into CTE and OVER() clause is empty it works correctly, but when I try to add ORDER BY/PARTITION BY inside the OVER() clause I'm encountering following error : '[42000][42000] Syntax error in SQL statement "WITH statement supports only SELECT, TABLE, VALUES, CREATE TABLE, INSERT, UPDATE, MERGE or DELETE statements" ' Non-working code sample:

WITH cte AS( 
  SELECT ROW_NUMBER() OVER (PARTITION BY CUST_NAME ORDER BY ORDER_DATE) AS rn 
  FROM TEST.HOLDING
)    
SELECT * 
FROM cte

One clarification: when I'm running statements using windows functions with PARTITION BY\ORDER BY parts not inside CTE it works well. Working code sample:

SELECT ROW_NUMBER() OVER (PARTITION BY CUST_NAME ORDER BY ORDER_DATE) AS rn 
FROM TEST.HOLDING

Test case for the issue:

CREATE TABLE PUBLIC.HOLDING(
CUST_NAME VARCHAR(50),
ORDER_DATE DATE
);

INSERT INTO PUBLIC.HOLDING(CUST_NAME, ORDER_DATE)
VALUES('Customer1',TO_DATE('20200201','YYYYMMDD')),
      ('Customer1',TO_DATE('20200202','YYYYMMDD')),
      ('Customer2',TO_DATE('20200201','YYYYMMDD')),
      ('Customer2',TO_DATE('20200202','YYYYMMDD'));

WITH cte AS(
SELECT CUST_NAME,
       ORDER_DATE,
       ROW_NUMBER() OVER (PARTITION BY CUST_NAME ORDER BY ORDER_DATE) AS rn
FROM PUBLIC.HOLDING
)

SELECT *
FROM cte;

SELECT CUST_NAME,
       ORDER_DATE,
       ROW_NUMBER() OVER (PARTITION BY CUST_NAME ORDER BY ORDER_DATE) AS rn
FROM PUBLIC.HOLDING;
Andrew M
  • 11
  • 3
  • Please provide a simplified version of your query (one that demonstrates the error). A working version would also help. – Gordon Linoff Aug 13 '20 at 12:02
  • Both samples incorrectly use `ORDER` as identifier without double quotes, and both work if it is specified correctly (`TEST."ORDER"`) in latest H2. Please, provide some code that really throws such error. – Evgenij Ryazanov Aug 13 '20 at 12:28
  • Thank you for the clarifications, I've provided real table name – Andrew M Aug 13 '20 at 12:32
  • Both queries from your updated question work in 1.4.200 without any exceptions if such table is created with random data. You need to provide some test case that really doesn't work (it would be better to include `CREATE TABLE` statement and some `INSERT INTO` statements to fill this table with sample data). – Evgenij Ryazanov Aug 13 '20 at 12:39
  • I am actually using version 1.4.200 – Andrew M Aug 14 '20 at 13:09

1 Answers1

-2

you should tell us which database you uesd.if you use sql server,please check you code ,i gusse in front of 'with',whether not have ';'.

;WITH cte AS( 
  SELECT ROW_NUMBER() OVER (PARTITION BY CUST_NAME ORDER BY ORDER_DATE) AS rn 
  FROM TEST.HOLDING
)    
SELECT * 
FROM cte;
funicany
  • 12
  • 1