|Named Query error [message #646809]
||Fri, 31 December 2010 08:27
Registered: December 2010
I have a problem here with my NamedQuery that I'm struggling for hours and still didn't made it work
this works fine:
SELECT COUNT(m.id) from MyClass m WHERE LOWER(m.name) =: name
but if I try this query instead:
SELECT COUNT(m.id) from MyClass m WHERE REGEXP_REPLACE(m.name,'[^a-zA-Z0-9]',NULL) =: name
It throws this exception during deploy:
Caused by: Exception [EclipseLink-8025] (Eclipse Persistence Services - 2.0.1.v20100213-r6600): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Syntax error parsing the query [checkExistsProfileByName: SELECT COUNT(m.id) from MyClass m WHERE REGEXP_REPLACE(m.name,'[^a-zA-Z0-9]',NULL) =: name], line 1, column 61: unexpected token [(].
Internal Exception: NoViableAltException(81!=[652:1: simpleConditionalExpressionRemainder[Object left] returns [Object node] : (n= comparisonExpression[left] | (n1= NOT )? n= conditionWithNotExpression[(n1!=null), left] | IS (n2= NOT )? n= isExpression[(n2!=null), left] );])
I just want to strip every character that isn't alpha-numeric from this column in my query, if I'm doing something wrong please tell me, because the query works just fine in SQL.