Re: [eclipselink-users] JPQL + LIKE + Subquery = MismatchedTokenExceptio

[James Sutherland <jamesssss@xxxxxxxxx>]

You should also be able to define the query using an Expression criteria
query, as I believe this is only a limitation of our JPQL support.

http://wiki.eclipse.org/EclipseLink/Examples/JPA/EMAPI#EntityManager_createQuery.28Expression.29

You may also wish to vote for the bug,
https://bugs.eclipse.org/bugs/show_bug.cgi?id=219814


tware wrote:
> 
> Hi Jed,
> 
>    The reason you are seeing an issue is that JPQL does not allow a
> subquery as 
> the argument to a LIKE expression.
> 
>    You may have to rewrite this JPQL expression as two queries, the first
> one 
> containing you inner query and the second one using the results of your
> inner 
> query to form your outer query.
> 
> -Tom
> 
> Jed Smallwood wrote:
>> Hi,
>> 
>> I'm trying to execute the following JPQL against a Derby database using 
>> eclipse link 1.0.2
>> 
>> SELECT COUNT(j.jobName) FROM Job j WHERE (((j.lineage LIKE (SELECT 
>> j.filterLineage FROM Job j WHERE j.jobName = '100_box' AND j.server = 
>> 'server-nc0')) AND j.server = 'server-nc0')) AND (j.jobType<>'BOX')
>> 
>> <skip if you are not interested in the rational of the query>
>> 
>> The idea here is to get a set of jobs that reside underneath the lineage 
>> path that the parent is located in.  For example, a containing node, a 
>> box, may have a lineage of '/' meaning that it is at the top.  This box 
>> has an ID of 100, and sub nodes of this box will have a lineage of 
>> '/100/'.  At the time that this query is constructed all that is known 
>> is the box name.  Therefore all sub nodes of this box need to be 
>> gathered in terms of the box name.  To assist this query, a second field 
>> filterLineage is defined that has the form '/100/%'.  This "AND j.server 
>> =..." you see twice in the query is necessary to scope certain portions 
>> of the query to only operate on one server.  More than likely this query 
>> will be operation against multiple servers at the same time.
>> 
>> </skip>
>> 
>> Executing this query causes a MismatchedTokenExcep tion in eclipse link.
>> 
>> Exception Description: Syntax error parsing the query [SELECT 
>> COUNT(j.jobName) FROM Job j WHERE (((j.lineage LIKE (SELECT 
>> j.filterLineage FROM Job j WHERE j.jobName = '100_box' AND j.server = 
>> 'server-nc0')) AND j.server = 'server-nc0')) AND (j.jobType<>'BOX')], 
>> line 1, column 61: syntax error at [LIKE].
>> Internal Exception: MismatchedTokenException(37!=70)
> 


-----
---
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland 
http://www.eclipse.org/eclipselink/
 EclipseLink ,  http://www.oracle.com/technology/products/ias/toplink/
TopLink 
Wiki:  http://wiki.eclipse.org/EclipseLink EclipseLink , 
http://wiki.oracle.com/page/TopLink TopLink 
Forums:  http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink , 
http://www.nabble.com/EclipseLink-f26430.html EclipseLink 
Book:  http://en.wikibooks.org/wiki/Java_Persistence Java Persistence 
-- 
View this message in context: http://www.nabble.com/JPQL-%2B-LIKE-%2B-Subquery-%3D-MismatchedTokenException-tp22469422p22477732.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.