1

I have formulated an SQL query that I need to translate into JPQL. The query contains a subquery in the SELECT clause, and also selects all rows from the same table that is contained in the subquery.

The following is an example of the SQL query that I am trying to translate:

SELECT table.* FROM TABLE table, (SELECT * FROM TABLE table1 WHERE ... ) table1 WHERE table.id >= table1.id

The part that I am having difficulties with is the table that I am creating using the subquery SELECT table.* FROM TABLE table, (SELECT * FROM TABLE table1 WHERE ... ) table1. The WHERE clause in the subquery is clear how to translate.

The way I currently have tried to translate the query is:

SELECT t FROM Table t, t1 FROM Table t1 WHERE ... WHERE t.id >= t1.id

The error I am getting when trying this is:

org.eclipse.persistence.jpa.jpql.parser.NullExpression cannot be cast to org.eclipse.persistence.jpa.jpql.parser.IdentificationVariable

Any help or suggestions would be appreciated :D

Felix
  • 99
  • 2
  • 10
  • Why don't just `SELECT t FROM Table t, Table t1 WHERE ... AND t.id >= t1.id `? – talex Apr 30 '19 at 11:17
  • @talex Doing that gives me the error "The expression is not a valid conditional expression." – Felix Apr 30 '19 at 11:23
  • Any details? Which expression exactly is not valid? – talex Apr 30 '19 at 11:24
  • @talex Nope. It just spits out the error while compiling without any further details – Felix Apr 30 '19 at 11:26
  • While compiling? It shouldn't check it while compiling. It is just a string. – talex Apr 30 '19 at 11:28
  • Just found this: "JPA does not support sub-selects in the FROM clause. Some JPA providers may support this.". Can be found here: https://en.wikibooks.org/wiki/Java_Persistence/JPQL#Sub-selects_in_FROM_clause. I guess I'll just use a native query then, instead of using a NamedQuery – Felix Apr 30 '19 at 11:29

1 Answers1

0

Well.... I guess I found the answer. It's not possible to implement such named queries as explained in this post: JPA/hibernate subquery in from clause

I will stick to a native query instead.

Felix
  • 99
  • 2
  • 10