0
String query = "select t from myTable t where t.idOne = " + myId
+ " AND t.idTwo = " + myIdTwo
+ " AND t.idThree = " + myIdThree
+  "AND t.idFour = (select max(t.idFour) from t)";

So myTable has a column where I need to get the MAX id from and use it in the query.

The last line gives me an error:

Error while running query: An exception occurred while creating a query in EntityManager:
java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: Exception Description: Syntax error parsing [query from above]. [214, 214] An identification variable must be provided for a range variable declaration.

john cs
  • 2,220
  • 5
  • 32
  • 50
  • 1
    Is this JPQL or SQL? Because `myTable` should actually be `MyEntity` (the name of the entity class) in JPQL. – SJuan76 Jan 17 '17 at 15:16
  • Sorry its jpql, you're correct it's the name of the entity class, i just named it that way – john cs Jan 17 '17 at 15:26
  • This is not exactly what you ask in your question but (with the proper `ORDER BY` clause) will give you the result that you want: http://stackoverflow.com/questions/6708085/select-top-1-result-using-jpa – SJuan76 Jan 17 '17 at 15:31
  • And maybe you could try (it has been a long time since I did JPQL) with `AND t.idFour = (select MAX(t.idFour))` – SJuan76 Jan 17 '17 at 15:34

1 Answers1

3

It's look like as you make mistake. Do you have table t in your schema? If not you should specify table name instead of alias.

String query = "select t from myTable t where t.idOne = " + myId
+ " AND t.idTwo = " + myIdTwo
+ " AND t.idThree = " + myIdThree
+  "AND t.idFour = (select max(st.idFour) from myTable st)";
Michael Piankov
  • 1,989
  • 1
  • 8
  • 19
  • thanks this did it. i was trying to do select from the same table but i guess i had to change the alias anyways. – john cs Jan 17 '17 at 15:35