2

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

Billydan
  • 395
  • 1
  • 7
  • 25
  • 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 Dec 30 '15 at 14:05
  • pasted the wrong query, check the edited one. – Billydan Dec 30 '15 at 14:12
  • 1
    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 Dec 30 '15 at 14:44
  • 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 Dec 30 '15 at 14:57
  • @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 Dec 31 '15 at 06:27
  • Have you solved this? – Cesar Leonardo Ochoa Contreras Jan 31 '19 at 22:29

2 Answers2

2

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
Dazak
  • 1,011
  • 2
  • 9
  • 17
  • that was not that, but it still has a syntax error, I forgot to remove the GROUP BY clause from the sub query – Billydan Dec 31 '15 at 06:23
0

Try removing the 'AS' string after the subquery. The article you presented does not use this construction.

Szarpul
  • 1,531
  • 11
  • 21