1

I have a query which works fine with SQL Developer but when I change it to Named query, it shows me exception. The join association path is not a valid expression. I have googled it a bit and found out that JPQL/JPA does not support subquery in from clause. I tried to modify my query also but all in vain. I have the following table:

-------------------------------------------
TYPE    TYPE1    TYPE2    TYPEORDER
-------------------------------------------
BL       AML    AML1       1
BL       AML    AML2       2
BL       AML    AML3       3
BL       COMP   COMP1      1
BL       COMP   COMP2      2
BL       COMP   COMP3      3
BL       FINAL  APP        1

I wanted to fetch the highest order TYPE2 value along with TYPE1 and TYPEORDER. Result should be like this:

-------------------------------
TYPE1    TYPE2    TYPEORDER
-------------------------------
AML      AML3     3
COMP     COMP3    3
FINAL    APP      1

I have written SQL query and it is returning me correct result but when i convert it into NamedQuery it shows me error. The query is :

SELECT T1.TYPE1, T2.TYPE2, T1.TYPEORDER FROM (SELECT MAX(TYPEORDER) AS TYPEORDER, TYPE1 FROM TEST WHERE TYPE = 'BL' GROUP BY TYPE1) T1 JOIN TEST T2 ON T1.TYPE1=T2.TYPE1 AND T1.TYPEORDER = T2.TYPEORDER;

Can anyone help ? I am using Oracle 11g.

Deepak Gangore
  • 343
  • 1
  • 13
  • 2
    What about using a Named NATIVE Query? https://www.thoughts-on-java.org/jpa-native-queries/ – RubioRic Mar 01 '18 at 11:43
  • Cannot use native query as it will bind code to particular DB. I don't want it to be DB specific. – Deepak Gangore May 03 '18 at 06:55
  • Well, that's your option. Do you know how often a DB engine is changed during the live of an application? If your ORM do not allow you to implement the query, I guess that you have to program it yourself using two or three individual queries and some iteration. – RubioRic May 03 '18 at 07:21

0 Answers0