[h2] Re: Syntax error in SQL statement "SELECT ..."; expected "identifier"

2015-08-25 Thread etyrrill 
Taking out WITH works.  Thanks for your help!

On Tuesday, August 25, 2015 at 5:50:05 AM UTC-7, Steve McLeod wrote:
>
> You'll notice that immediately after the WITH keyword, there is [*]. This 
> indicates where the syntax error was.
>
> Without the WITH it should work as intended, no?
>
> SELECT * 
>   FROM policies 
>   JOIN
>  (SELECT id, MAX(generationId) AS maxgen FROM policies GROUP BY id) AS 
> mg 
>   ON policies.id = mg.id AND policies.generationId = mg.maxgen
>
>
> On Monday, 24 August 2015 19:43:15 UTC+2, etyr...@gmail.com wrote:
>>
>> Hi All,
>>
>> I have a table, policies, that has "id" and "generationId" columns.  I 
>> want to get a row for "id" with the greatest "generationId" for that "id". 
>>  My query is as follows:
>>
>> SELECT * FROM policies JOIN WITH (SELECT id, MAX(generationId) AS maxgen 
>> FROM policies GROUP BY id) AS mg ON policies.id = mg.id AND 
>> policies.generationId = mg.maxgen
>>
>> It seems that the database doesn't recognize "maxgen" as an identifier. 
>> Below is the error I get.  Any suggestions would be appreciated.  I am 
>> using 1.4.188 beta.
>>
>> Syntax error in SQL statement "SELECT * FROM POLICIES JOIN WITH[*] 
>> (SELECT ID, MAX(GENERATIONID) AS MAXGEN FROM POLICIES GROUP BY ID) AS MG ON 
>> POLICIES.ID = MG.ID AND POLICIES.GENERATIONID = MG.MAXGEN "; expected 
>> "identifier"; SQL statement:
>> SELECT * FROM policies JOIN WITH (SELECT id, MAX(generationId) AS maxgen 
>> FROM policies GROUP BY id) AS mg ON policies.id = mg.id AND 
>> policies.generationId = mg.maxgen [42001-188] 
>> 
>>  42001/42001 (Help) 
>> 
>> org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "SELECT * 
>> FROM POLICIES JOIN WITH[*] (SELECT ID, MAX(GENERATIONID) AS MAXGEN FROM 
>> POLICIES GROUP BY ID) AS MG ON POLICIES.ID = MG.ID AND 
>> POLICIES.GENERATIONID = MG.MAXGEN "; expected "identifier"; SQL statement:
>> SELECT * FROM policies JOIN WITH (SELECT id, MAX(generationId) AS maxgen 
>> FROM policies GROUP BY id) AS mg ON policies.id = mg.id AND 
>> policies.generationId = mg.maxgen [42001-188]
>> at 
>> org.h2.message.DbException.getJdbcSQLException(DbException.java:345 
>> 
>> )
>> at org.h2.message.DbException.getSyntaxError(DbException.java:205 
>> 
>> )
>> at org.h2.command.Parser.readIdentifierWithSchema(Parser.java:3060 
>> 
>> )
>> at org.h2.command.Parser.readTableFilter(Parser.java:1191 
>> 
>> )
>> at org.h2.command.Parser.readJoin(Parser.java:1540 
>> 
>> )
>> at org.h2.command.Parser.parseJoinTableFilter(Parser.java:1879 
>> 
>> )
>> at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1874 
>> 
>> )
>> at org.h2.command.Parser.parseSelectSimple(Parser.java:1982 
>> 
>> )
>> at org.h2.command.Parser.parseSelectSub(Parser.java:1867 
>> 
>> )
>> at org.h2.command.Parser.parseSelectUnion(Parser.java:1688 
>> 
>> )
>> at org.h2.command.Parser.parseSelect(Parser.java:1676 
>> 
>> )
>> at org.h2.command.Parser.parsePrepared(Parser.java:432 
>> 
>> )
>> at org.h2.command.Parser.parse(Parser.java:304 
>> 
>> )
>> at org.h2.command.Parser.parse(Parser.java:276 
>> 
>> )
>> at org.h2.command.Parser.prepareCommand(Parser.java:241 
>> 
>> )
>> at org.h2.engine.Session.prepareLocal(Session.java:461 
>> 
>> )
>> at org.h2.server.TcpServerThread.process(TcpServerThread.java:264 
>>

[h2] Re: Syntax error in SQL statement "SELECT ..."; expected "identifier"

2015-08-25 Thread Steve McLeod 
You'll notice that immediately after the WITH keyword, there is [*]. This 
indicates where the syntax error was.

Without the WITH it should work as intended, no?

SELECT * 
  FROM policies 
  JOIN
 (SELECT id, MAX(generationId) AS maxgen FROM policies GROUP BY id) AS 
mg 
  ON policies.id = mg.id AND policies.generationId = mg.maxgen


On Monday, 24 August 2015 19:43:15 UTC+2, etyr...@gmail.com wrote:
>
> Hi All,
>
> I have a table, policies, that has "id" and "generationId" columns.  I 
> want to get a row for "id" with the greatest "generationId" for that "id". 
>  My query is as follows:
>
> SELECT * FROM policies JOIN WITH (SELECT id, MAX(generationId) AS maxgen 
> FROM policies GROUP BY id) AS mg ON policies.id = mg.id AND 
> policies.generationId = mg.maxgen
>
> It seems that the database doesn't recognize "maxgen" as an identifier. 
> Below is the error I get.  Any suggestions would be appreciated.  I am 
> using 1.4.188 beta.
>
> Syntax error in SQL statement "SELECT * FROM POLICIES JOIN WITH[*] (SELECT 
> ID, MAX(GENERATIONID) AS MAXGEN FROM POLICIES GROUP BY ID) AS MG ON 
> POLICIES.ID = MG.ID AND POLICIES.GENERATIONID = MG.MAXGEN "; expected 
> "identifier"; SQL statement:
> SELECT * FROM policies JOIN WITH (SELECT id, MAX(generationId) AS maxgen 
> FROM policies GROUP BY id) AS mg ON policies.id = mg.id AND 
> policies.generationId = mg.maxgen [42001-188] 
> 
>  42001/42001 (Help) 
> 
> org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "SELECT * FROM 
> POLICIES JOIN WITH[*] (SELECT ID, MAX(GENERATIONID) AS MAXGEN FROM POLICIES 
> GROUP BY ID) AS MG ON POLICIES.ID = MG.ID AND POLICIES.GENERATIONID = 
> MG.MAXGEN "; expected "identifier"; SQL statement:
> SELECT * FROM policies JOIN WITH (SELECT id, MAX(generationId) AS maxgen 
> FROM policies GROUP BY id) AS mg ON policies.id = mg.id AND 
> policies.generationId = mg.maxgen [42001-188]
> at org.h2.message.DbException.getJdbcSQLException(DbException.java:345 
> 
> )
> at org.h2.message.DbException.getSyntaxError(DbException.java:205 
> 
> )
> at org.h2.command.Parser.readIdentifierWithSchema(Parser.java:3060 
> 
> )
> at org.h2.command.Parser.readTableFilter(Parser.java:1191 
> 
> )
> at org.h2.command.Parser.readJoin(Parser.java:1540 
> 
> )
> at org.h2.command.Parser.parseJoinTableFilter(Parser.java:1879 
> 
> )
> at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1874 
> 
> )
> at org.h2.command.Parser.parseSelectSimple(Parser.java:1982 
> 
> )
> at org.h2.command.Parser.parseSelectSub(Parser.java:1867 
> 
> )
> at org.h2.command.Parser.parseSelectUnion(Parser.java:1688 
> 
> )
> at org.h2.command.Parser.parseSelect(Parser.java:1676 
> 
> )
> at org.h2.command.Parser.parsePrepared(Parser.java:432 
> 
> )
> at org.h2.command.Parser.parse(Parser.java:304 
> 
> )
> at org.h2.command.Parser.parse(Parser.java:276 
> 
> )
> at org.h2.command.Parser.prepareCommand(Parser.java:241 
> 
> )
> at org.h2.engine.Session.prepareLocal(Session.java:461 
> 
> )
> at org.h2.server.TcpServerThread.process(TcpServerThread.java:264 
> 
> )
> at org.h2.server.TcpServerThread.run(TcpServerThread.java:159 
>