CODEWITHSUNDEEP
javasqlpostgresql
MsJorge
MsJorge

Reputation: 155

postgresql error near as in java

I have a PostgreSQL 9.2.2 server running for a GWT project.

One query is giving trouble, as it works fine, when I try it in the sql tab in phpPgAdmin:

 (select distinct max(enddate) as max,meterid 
  from usermeasurements 
  where locationid=127025 
    AND usage>-1 
  GROUP BY meterid ) as t1 
  left outer join usermeasurements as t2 
            ON (t1.meterid=t2.meterid 
            AND t1.max=t2.enddate)

But when I try using the query in my gwt project, I get the following error:

 [btpool0-5] ERROR com.example.DataGetter  - unable to get usermeasurement: org.postgresql.util.PSQLException: ERROR: syntax error at or near "as" Position: 112

It's the first time I have experienced a difference between what works in phpPgAdmin and an app.

As can be seen, I use BoneCP to handle my connection pool, but that shouldn't have any effect as far a I know.

Upvotes: 3

Views: 2731

Answers (2)

Ihor Romanchenko
Ihor Romanchenko

Reputation: 28511

Maybe the query should look like:

SELECT * 
FROM
(SELECT DISTINCT max(enddate) as max, meterid 
 FROM usermeasurements 
 WHERE locationid = 127025 
   AND usage > -1 
 GROUP BY meterid ) as t1 
LEFT JOIN usermeasurements as t2 
     ON (t1.meterid=t2.meterid 
         AND t1.max=t2.enddate)

The query in the question isn't correct (it looks like a FROM part of the bigger query).

Upvotes: 0

someone
someone

Reputation: 6572

Since max is a keyword use something else after as

select distinct max(enddate) as mymax, ......

UPDATED :

Another thing. In your exception it says

unable to get usermeasurement:

but in your sql you have used usermeasurements (You have a s in the end). Check your source code again there may be you have missed letter s

Upvotes: 3

Related Questions