Re: [eclipselink-users] JPQL + JOIN + SELECT IN

[AnaTatavu <ana.tatavu@xxxxxxxx>]

A combination of all your adivice works!
This jpql query executes fine:
SELECT job.jobName, job.server FROM EnityJob job,Dependency dep  WHERE  
(job.nameId=dep.targetNameId) and (dep.sourceNameId IN(SELECT j.nameId FROM
EnityJob j where j.jobName = 'job3'))

Thank you for all the help.

tware wrote:
> 
> A colleague just point me to the problem.
> 
> JPQL does not allow IN with an Entity.
> 
> You can do IN with a primitive....
> 
> i.e. job.name IN .....
> 
> But you cannot do IN with an Entity
> 
> i.e. job IN
> 
> AnaTatavu wrote:
>> Assuming I have a->b->c,  I want to find all the jobs that either depend
>> on
>> b(in this case c) or release b(in this case a)
>> IN the job table I will have 
>> a
>> b
>> c
>> In the dependency table I will have 
>> a b
>> b c
>> 
>> The IN select is the filter for the set of jobs I want to solve the
>> dependencies for.
>> I tried both suggestions , to narrow down, it didn't work.
>> 
>> 
>> tware wrote:
>>> In this expression:
>>>
>>> (job.nameId=dep.targetNameId)
>>>
>>> What is the goal.  Are you trying to limit the results to jobs that
>>> successfully 
>>> join to the table for dep?
>>>
>>> There may be a bug here:
>>>
>>> Some things you could try to narrow it down:
>>>
>>> - see the the query runs without the last part:  and
>>> (job.nameId=dep.targetNameId)
>>> - reorder to put (job.nameId=dep.targetNameId) before the subquery
>>>
>>>
>>> Assume there is a bug, you may be able to work around it by Mapping a 
>>> relationship from job to dep and making it LAZY?  That way it will not
>>> be 
>>> retrieved unless you want it to be and it will be easy to build queries?
>>>
>>> AnaTatavu wrote:
>>>> Job and Dependency tables don't have the foreign key set.
>>>> The dependeby table has 2 fields I am intrested in source_job_id and
>>>> target_job_id
>>>> I don't want to set the foreign key constraints for performance
>>>> reasons,
>>>> this is failry rare operation.
>>>>
>>>> tware wrote:
>>>>> What are you trying to actually do?  I see "dep" only referenced at
>>>>> the
>>>>> end - 
>>>>> which seems to me to be trying to do some kind of cartesian product. 
>>>>> Do
>>>>> you 
>>>>> really mean something like:
>>>>>
>>>>> Select Object(job) FROM EntityJob job JOIN job.dependency dep.......
>>>>>
>>>>> -Tom
>>>>>
>>>>> AnaTatavu wrote:
>>>>>>  
>>>>>> I am trying to issue the next JPQL query:
>>>>>> SELECT Object(job) FROM EntityJob job, Dependency dep WHERE (job
>>>>>> IN(SELECT
>>>>>> Object(j) FROM EntityJob j where j.jobName = 'TG_HTTP_JOB1')) and
>>>>>> (job.nameId=dep.targetNameId)
>>>>>>
>>>>>>
>>>>>> Exception Description: Syntax error parsing the query [
>>>>>> SELECT Object(job) FROM EntityJob job, Dependency dep WHERE (job
>>>>>> IN(SELECT
>>>>>> Object(j) FROM EntityJob j where j.jobName = 'TG_HTTP_JOB1')) and
>>>>>> (job.nameId=dep.targetNameId)], line 1, column 62: unexpected token
>>>>>> [(].
>>>>>> Internal Exception: NoViableAltException(69!=[593:1:
>>>>>> conditionalPrimary
>>>>>> returns [Object node] : ( ( LEFT_ROUND_BRACKET conditionalExpression
>>>>>> )=>
>>>>>> LEFT_ROUND_BRACKET n= conditionalExpression RIGHT_ROUND_BRACKET | n=
>>>>>> simpleConditionalExpression );])
>>>>>> at
>>>>>> org.eclipse.persistence.exceptions.JPQLException.unexpectedToken(JPQLException.java:365)
>>>>>>
>>>>>>
>>>>>> I tried to remove the ( and I got a different exception.
>>>>>>
>>>>> _______________________________________________
>>>>> eclipselink-users mailing list
>>>>> eclipselink-users@xxxxxxxxxxx
>>>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>>>>>
>>>>>
>>> _______________________________________________
>>> eclipselink-users mailing list
>>> eclipselink-users@xxxxxxxxxxx
>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>>>
>>>
>> 
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
> 
> 

-- 
View this message in context: http://www.nabble.com/JPQL-%2B-JOIN-%2B-SELECT-IN-tp22355819p22372295.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.