CODEWITHSUNDEEP
javasqlh2
Anteneh
Anteneh

Reputation: 19

Syntax error in SQL statement "WITH" keyword

jdbc:h2:mem:request_no;MODE=Oracle

We are using the h2 version 1.3.171 with windows 7 (64 bit) and jdk 1.7.0_25.

When we try to run the SQL statement: 

WITH TMP1 AS (SELECT col1 FROM table1) SELECT TMP1.col1 FROM TMP1

We are getting the below exception:

org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement 
"WITH TMP1 AS[*] (SELECT REQUEST_NO FROM QUOTE) 
  SELECT TMP1.REQUEST_NO FROM TMP1";
expected "., ("; SQL statement:
WITH TMP1 AS (SELECT REQUEST_NO FROM QUOTE) 
  SELECT TMP1.REQUEST_NO FROM TMP1 [42001-171]

SQL statement is fine as we are able to execute it successfully via SQL Developer.

Any help is appreciated.

Thanks.

Upvotes: 1

Views: 1555

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270331

Try defining the columns as part of the CTE definition:

WITH TMP1(REQUEST_NO) AS
      (SELECT REQUEST_NO FROM QUOTE
      )
SELECT TMP1.REQUEST_NO
FROM TMP1;

I think some databases require explicit column lists when using with.

Upvotes: 1

Related Questions