NamedQuery with a CONCAT returns error

Question

I am attempting to do a select query like the following:

SELECT *, CONCAT(TNUSERDATE, ' - ', TNUSERTIME) AS datetime 
  FROM TODONOTE WHERE TNTDKEY='TD00019148' ORDER BY datetime DESC;

That query works; however, when I attempt to transfer it into named query:

@NamedQueries({
    @NamedQuery(name = "Todonote.findAll", query = "SELECT c FROM Todonote c"),
    @NamedQuery(name = "Todonote.findByTaskNumber", query = "SELECT c, CONCAT(c.userDate, ' - ', c.userTime) AS datetime FROM Todonote c WHERE c.todoTask = :taskNumber ORDER BY datetime DESC")
})

I get an error

[EL Severe]: 2012-08-23 12:15:39.111--ServerSession(841933)--Local Exception Stack: 
Exception [EclipseLink-6168] (Eclipse Persistence Services - 2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.QueryException
Exception Description: Query failed to prepare, unexpected error occurred: [java.lang.ClassCastException: org.eclipse.persistence.internal.jpa.parsing.ConcatNode cannot be cast to org.eclipse.persistence.internal.jpa.parsing.AliasableNode].
Internal Exception: java.lang.ClassCastException: org.eclipse.persistence.internal.jpa.parsing.ConcatNode cannot be cast to org.eclipse.persistence.internal.jpa.parsing.AliasableNode
Query: JPAQuery(name="Todonote.findByTaskNumber" )

I am unsure as to what the problem is or how I would execute the concat statement. My goal is to run that namedQuery and the ResultList that is returned to me is already sorted based on the values in the concatenated column.

Thank You

Is the alias `datetime` the problem? (`datetime` is usually a SQL keyword.) — EthanB, Aug 23 '12 at 17:16
@EthanB Nope, because the first (original sql) query works. Just in case I tried replacing it with 'banana' - same error. =( — Metal Wing, Aug 23 '12 at 17:50

score 4 · Answer 1 · answered Aug 24 '12 at 06:53

There is a difference in the JPA 1.0 and the JPA 2.0.In JPA 2.0 includes "[AS] result_variable" in select items which is missing from the 1.0 . So you can try like this;

  SELECT c, CONCAT(c.userDate, ' - ', c.userTime) FROM Todonote c WHERE c.todoTask = :taskNumber ORDER BY CONCAT(c.userDate, ' - ', c.userTime) DESC

NamedQuery with a CONCAT returns error

1 Answers1