jpa eclipselink subquery in from clause

Question

I read here https://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL#Sub-selects_in_FROM_clause

that eclipselink support subquery in from clause

but when i'm using this query

queryString2="SELECT NEW dz.com.naftal.erp.domain.view.MouvementProduitView('VAR',t.cds,SUM(t.mntttc)) " +
             "FROM (SELECT DISTINCT m.mouvementProduitViewPK.cds as cds,m.mouvementProduitViewPK.referenceDocument,m.mouvementProduitViewPK.typeDocument " +
             "m.mntttc as mntttc FROM MouvementProduitView m WHERE m.mouvementProduitViewPK.cds IN :cdss " +
             "AND m.mouvementProduitViewPK.typeDocument IN :typeDocuments " +
             "AND m.dateOperation BETWEEN :dateDu AND :dateAu GROUP BY m.mouvementProduitViewPK.cds ORDER BY m.mouvementProduitViewPK.cds) AS t GROUP BY t.cds"

I'm getting this error

SEVERE [global]
Local Exception Stack: 
Exception [EclipseLink-0] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Syntax error parsing [SELECT NEW.............. 
[388, 388] The right parenthesis is missing from the sub-expression.
[389, 389] An identification variable must be provided for a range variable declaration.
[426, 447] The query contains a malformed ending.

does any buddy knows if the subquery in from clause is actually working, and if No is there any other way to do this unless using native query.

PS: I m using eclipselink 2.5.0.v20130507

an error that says "SELECT NEW ..." and a query that doesn't have that text? so you aren't presenting the same query — Neil Stockton
the message is nothing to do with the subquery and everything to do with "select new". So how are you invoking it? you also don't have a candidate entity in the FROM, and the EclipseLink page example does have a candidate. — Neil Stockton
I have a constructor in the MouvementProduitView with the 3 parameters, so the select new work if i use select new .... from MouvementProduitView m it works, also i tried to use a entity condidate with the select subquery and i got the same error — Billydan
@NeilStockton, it was the candidate entity, it doesn't work the first time i tried it because i had a syntaxe error ( the GROUP BY clause in the sub query), so you had the right answer — Billydan

Dazak Dazak · Accepted Answer · 2015-12-30T18:13:48

Your error is simple: Your query is malformed, is missing a comma in the select distinct... you have:

SELECT DISTINCT 
    m.mouvementProduitViewPK.cds as cds,
    m.mouvementProduitViewPK.referenceDocument,
    m.mouvementProduitViewPK.typeDocument //Here is missing the comma
    m.mntttc as mntttc 
FROM MouvementProduitView m

Between the third and fourth line is missing the comma, it should be:

SELECT DISTINCT 
    m.mouvementProduitViewPK.cds as cds,
    m.mouvementProduitViewPK.referenceDocument,
    m.mouvementProduitViewPK.typeDocument, //put at the end of this line the comma
    m.mntttc as mntttc 
FROM MouvementProduitView m

jpa eclipselink subquery in from clause

2 Answers