Hi,
I use jpql queries using Hibernate

I have an OR clause in jpql query that doen't give me a good result


the following query

SELECT aud.oID FROM eu.eclinica.cdisc.odm.ODMAuditRecord aud WHERE (aud.subjectData is not null and aud.locationRef.location.oID = 1063 and aud.subjectData.clinicalData.study.oID = 1069 and aud.operation = 'INSERT')

gives me the following result ( 1 row)


the following query


SELECT aud.oID FROM eu.eclinica.cdisc.odm.ODMAuditRecord aud WHERE (aud.studyForDeleted.oID= 1069 and aud.locationRef.location.oID= 1063)

give me the following result (2 rows)




When I use both conditions with a OR clause

SELECT aud.oID FROM eu.eclinica.cdisc.odm.ODMAuditRecord aud WHERE (aud.subjectData is not null and aud.locationRef.location.oID = 1063 and aud.subjectData.clinicalData.study.oID = 1069 and aud.operation = 'INSERT') OR (aud.studyForDeleted.oID= 1069 and aud.locationRef.location.oID= 1063)


the result is only one row, that is


I removed parts of the first subQuery to find what expression cancels the second subquery and curiously when I remove

and aud.subjectData.clinicalData.study.oID = 1069

I obtain


I realy don't see why this part of subquery cancels the second subQuery as there is a OR clause.

Thank you in advance for your answer

I know HQL, SQLQuery and Query by Criteria.
Can you please explain what you understand with 'jpql'?

N.B.: I suggest you to log the jdbc-statements with p6spy
so you can see how hibernate effectively propagates the query to your database.

Hi pb00067 and thank you for your answer. Sorry to answer so late

I meant HQL query

I succeeded in reproducing the same behavior on another query

The complete query QUERY1 is
query = "SELECT a " +
"FROM eu.eclinica.cdisc.odm.ODMAnnotation a " +
"WHERE ((a.subjectData.clinicalData.study.oID = 1069 and a.seqNum = 1) or (a.studyEventData.subjectData.clinicalData.study.oID = 1069 and a.seqNum = 1))"

it gives me 0 result

The query QUERY2 with the first subQuery is
query = "SELECT a " +
"FROM eu.eclinica.cdisc.odm.ODMAnnotation a " +
"WHERE ((a.subjectData.clinicalData.study.oID = 1069 and a.seqNum = 1) "

it gives me 1 result Result1

The query QUERY3 with the second subQuery is
query = "SELECT a " +
"FROM eu.eclinica.cdisc.odm.ODMAnnotation a " +
"WHERE (a.studyEventData.subjectData.clinicalData.study.oID = 1069 and a.seqNum = 1))"

it gives me 1 other result Result2

I remind you that the problem is that the first query QUERY1 should give me 2 results (the result of QUERY2 PLUS the result of QUERY3) as there is a OR clause

I debuged the real request made on the database



To summarize, the request made on the database is the following (I use there my proper coding)

query = "SELECT a " +
"FROM eu.eclinica.cdisc.odm.ODMAnnotation a " +
"WHERE a.subjectData.clinicalData c1 AND a.studyEventData.subjectData.clinicalData c2 and (c1.study.oID = 1069 and a.seqNum = 1or c2.study.oID = 1069 and a.seqNum = 1)"


I really don't see why the first part is "a.subjectData.clinicalData c1 AND a.studyEventData.subjectData.clinicalData c2"

as these two clause should be used with an "OR". Obviously, seeing the request that is sent to the database it is normal that there is no Results

So how should I implement QUERY1 ?

Hi Fabala,

I guess your query is to complicate for the parser and I don't know if there is a way to make the thing work with HQL as unique query.
What surely works is splitting up the query into 2 queries and make a kind of 'union' programmatically:

Hi pb00067 and thank you for your answer,

yes indeed I can split up the query into 2 queries

But I am very concerned about this issue and it would really help me if I can see what is wrong in my request.

To your mind, where can I get a precise answer

Thank you again