Syntax error in SQL statement “WITH” keyword throw

Question

I have also added another TMP2 and was not able to run the query... Could you please help me on this query? I am using Oracle 11g.

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

WITH TMP1(REQUEST_NO) AS
  (SELECT REQUEST_NO FROM QUOTE),
  TMP2(AGENT) AS (SELECT AGENT FROM AGENT_TAB)
SELECT TMP2.AGENT FROM TMP2;

The exception I got is :

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

The query is fine in sql developer but not working in the Junit tests. 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.

Answer 1

Oracle supports the WITH clause, but it is looks like H2 does not support it: H2 SQL grammar

I would transform the query in the with part to the main query.