(NOTE: I've edited my original post to simplify it)
I'm unable to get past syntax errors in JPQL queries that involve the SUBSTR function. I am working with eclipselink 2.3.2.
Specifically, I'd like to come up with a JPQL working equivalent of this sort of sql query:
select * from Table1 t where SUBSTR(t.someStringField, 91, 5) like 'abc'
And I have written the following JPQL query (which looks very much like the original sql counterpart):
select t from Table1 t where SUBSTR(t.someStringField, 91,5) like 'abc'
(note: I'm writing it in pseudo code form for clarity; there are more clauses present in the query but they do not seem to be related to the problem at hand, so I've stripped it down to the bare minimum)
I get the following syntax error when the query is created:
Caused by: Exception [EclipseLink-8025] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.JPQLException Exception Description: Syntax error parsing the query [], line 1, column 172: unexpected token [(]. Internal Exception: NoViableAltException(83@[()* loopback of 383:9: (d= DOT right= attribute )*]) at org.eclipse.persistence.exceptions.JPQLException.unexpectedToken(JPQLException.java:372) at org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.handleRecognitionException(JPQLParser.java:320) at org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.addError(JPQLParser.java:246) at org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.reportError(JPQLParser.java:363) at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.pathExprOrVariableAccess(JPQLParser.java:1436) at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.arithmeticPrimary(JPQLParser.java:4720) at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.arithmeticFactor(JPQLParser.java:4660) at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.arithmeticTerm(JPQLParser.java:4546) at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.simpleArithmeticExpression(JPQLParser.java:4462) at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.arithmeticExpression(JPQLParser.java:4402)
etc...
Since I'm testing the use of SUBSTR in the simplest form I can think of, I am starting to believe that the use of SUBSTR is not supported by eclipselink, although it is part of JPA 2.0 according to the official documentation.
Can anybody confirm the above assumption, or otherwise, reject it, show me what is wrong with the syntax of these queries, and hopefully suggest some alternative? Thanks in advance.
