Using Aggregate functions in JPA

Question

I have the folowing situation.

I want to retrieve a list of bids between time t1 and time t2. Then from this list i want to retrieve winning bid i.e maximum bid price.

I have written the following JPA query.

SELECT b FROM Bid b WHERE b.bidAmt = (SELECT MAX(b.bidAmt) FROM b WHERE b.lastUpdtTs BETWEEN ?1 AND ?2)

But I am getting the following exception.

Exception Description: Syntax error parsing the query [SELECT b FROM Bid b WHERE b.bidAmt = (SELECT MAX(b.bidAmt) FROM b WHERE b.lastUpdtTs BETWEEN ?1 AND ?2)], line 1, column 64: unexpected token [b].
Internal Exception: NoViableAltException(66!=[1108:1: subselectIdentificationVariableDeclaration[List varDecls] : ( identificationVariableDeclaration[varDecls] | n= associationPathExpression ( AS )? i= IDENT | n= collectionMemberDeclaration );])

Couls someone point out the mistake?

axtavt · Accepted Answer · 2011-01-21 13:16:35Z

1

Since you query references two different Bid instances, they should have different aliases:

SELECT b FROM Bid b WHERE b.bidAmt = 
     (SELECT MAX(bb.bidAmt) FROM Bid bb WHERE bb.lastUpdtTs BETWEEN ?1 AND ?2)

answered Jan 21, 2011 at 13:16

axtavt

243k42 gold badges518 silver badges486 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

darioo · Accepted Answer · 2011-01-21 07:15:30Z

1

Haven't tried it myself, but from what I see, mistake could be in your subquery. You say FROM b, but it should be FROM Bid b. So, the entire query looks like this:

SELECT b FROM Bid b WHERE b.bidAmt = (SELECT MAX(b.bidAmt) FROM Bid b WHERE b.lastUpdtTs BETWEEN ?1 AND ?2)

answered Jan 21, 2011 at 7:15

darioo

47.4k10 gold badges79 silver badges104 bronze badges

1 Comment

Thanos Over a year ago

i tried the above but got the following exception Exception Description: Error compiling the query [SELECT b FROM Bid b WHERE b.bidAmt = (SELECT MAX(b.bidAmt) FROM Bid b WHERE b.lastUpdtTs BETWEEN ?1 AND ?2)], line 1, column 68: multiple declaration of identification variable [b], previously declared as [Bid b]

Sean Patrick Floyd · Accepted Answer · 2011-01-21 13:30:14Z

0

The Error you got the second time is

multiple declaration of identification variable [b], previously declared as [Bid b]

From the above error it seems like b alias declared for multiple times so remove alias b from sub query

try out following query

SELECT b FROM Bid b WHERE b.bidAmt =
    (SELECT MAX(bidAmt) FROM Bid WHERE lastUpdtTs BETWEEN ?1 AND ?2)

edited Jan 21, 2011 at 13:30

Sean Patrick Floyd

301k72 gold badges481 silver badges598 bronze badges

answered Jan 21, 2011 at 12:05

Romani

3,2414 gold badges28 silver badges28 bronze badges

Collectives™ on Stack Overflow

Using Aggregate functions in JPA

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related