CODEWITHSUNDEEP
oracle-databasejpaeclipselinkjpql
filemonczyk
filemonczyk

Reputation: 1703

JPQL query with having oracle db

I am using jpql jpa eclipselink Following query wont work : 

SELECT c FROM TableA c WHERE c.forumId = :forumId AND c.isDefault = true HAVING MAX (c.validFrom)

The error im getting "The expression is not a valid conditional expression"

Upvotes: 1

Views: 126

Answers (1)

Dherik
Dherik

Reputation: 19110

The HAVING clause only works with a GROUP BY expression.

The HAVING clause allows for the results of a GROUP BY to be filtered.

Your question is:

i want o get max validFrom how can i make expression ot of this

But you can make a query without GROUP BY and HAVING to do what you want:

select c from TableA c WHERE c.validFrom = (
    SELECT MAX(c2.validFrom) 
    FROM TableA c2  
    WHERE c2.Id = c.Id AND c.forumId = :forumId AND c.isDefault = true
)

If you would like to use GROUP BY and HAVING, you can do:

SELECT c FROM TableA c WHERE c.validFROM = (
    SELECT MAX(validFROM) 
        FROM TableA 
        WHERE forumId = :forumId AND isDefault = true
        GROUP BY validFROM
        HAVING validFROM=c.validFROM
)

Upvotes: 2

Related Questions