I have this JPA query in a Spring MvC project
@Query(value = "with\n"
+ " jupiter_courante ( \n"
+ " jupiter_id\n"
+ ")"
+ "as (\n"
+ " select pa.jupiter_id from\n"
+ "jupiter_microfusa pa\n"
+ "inner join jupiter_composition pc on pa.JUPITER_ID = pc.JUPITER_ID\n"
+ "inner join jupiter_composition_type pct on pc.TYPEJUPITERCOMPOSITION_ID = pct.TYPEJUPITERCOMPOSITION_ID\n"
+ "inner join donnees_contact dc on pc.microfusa_id = dc.microfusa_Id\n"
+ "inner join donnees_contact_type dct on dc.TYPE_DONNEE_CONTACT_ID = dct.TYPE_DONNEE_CONTACT_ID\n"
+ "where pct.code = 'kimi'\n"
+ "),\n"
+ "aquarius_du_menage_courant as (\n"
+ "select microfusa_id from jupiter_composition ac\n"
+ "inner join jupiter_composition_type act on ac.TYPEJUPITERCOMPOSITION_ID = act.TYPEJUPITERCOMPOSITION_ID\n"
+ "where jupiter_id = (select jupiter_id from jupiter_courante)\n"
+ "),\n"
+ "parents_du_menage_courant as (\n"
+ "select microfusa_id from jupiter_composition ac\n"
+ "inner join jupiter_composition_type act on ac.TYPEJUPITERCOMPOSITION_ID = act.TYPEJUPITERCOMPOSITION_ID\n"
+ "where jupiter_id = (select jupiter_id from jupiter_courante)\n"
+ "and act.code in ('oko', 'aqa')\n"
+ "),\n"
+ "toutes_autorisations as\n"
+ "(\n"
+ "select aut.dt_debut, aut.dt_Fin, aut.AQUARIUS_ID, aut.autorisation_id, aut_p.*, decode(aut_ty.desc_long, 'interdiction', 1, 0) as interdiction from autorisation aut\n"
+ "inner join autorisation_microfusa aut_p on aut.AUTORISATION_PERSONNE_ID = aut_p.AUTORISATION_PERSONNE_ID\n"
+ "inner join autorisation_type aut_ty on aut.autorisation_type_id = aut_ty.autorisation_type_id\n"
+ "),\n"
+ "phone as (\n"
+ "select microfusa_id, contenu as telephone from (\n"
+ "select dc.microfusa_id, dc.contenu, row_number() over (partition by microfusa_id order by (case when code = 'W' then 1 when code = 'ER' then 2 when code = 'FG' then 3 when code = 'TES' then 4 else 10 end) ) rown from donnees_contact dc\n"
+ "inner join donnees_contact_type dct on dc.TYPE_DONNEE_CONTACT_ID = dct.TYPE_DONNEE_CONTACT_ID\n"
+ "where microfusa_id in (select microfusa_id from parents_du_menage_courant)\n"
+ "and dct.code in ('AW', 'GSSM','TMNB', 'TPOL')\n"
+ ") where rown = 1\n"
+ "),\n"
+ "email as (\n"
+ "select microfusa_id, contenu as email from (\n"
+ "select dc.microfusa_id, dc.CONTENU, dct.CODE, row_number() over (partition by microfusa_id order by (case when code = 'EMAIL' then 1 when code = 'EMAIL PRIVE' then 2 else 10 end) ) rown from donnees_contact dc\n"
+ "inner join donnees_contact_type dct on dc.TYPE_DONNEE_CONTACT_ID = dct.TYPE_DONNEE_CONTACT_ID\n"
+ "where microfusa_id in (select microfusa_id from parents_du_menage_courant)\n"
+ "and dct.code in ('EMAIL', 'EMAIL2')\n"
+ ") where rown = 1\n"
+ ")\n"
+ " \n"
+ "select * from\n"
+ "(\n"
+ "select distinct p.microfusa_Id as aquarius_id, p.nom as aquarius_nom, p.prenom as aquarius_prenom , tas.nom as authorised_nom\n"
+ ", tas.prenom as authorised_prenom , tas.interdiction as IPerAut_interdiction, tas.telephone, tas.email, tas.AUTORISATION_PERSONNE_ID as microfusaautoriseeID, 1 as modifiable\n"
+ ", tas.interdiction as IPerAutEx_interdiction, tas.dt_debut, tas.dt_Fin, tas.autorisation_id as autorisationID\n"
+ "from aquarius_du_menage_courant emc\n"
+ "inner join microfusa p on emc.microfusa_id = p.microfusa_id\n"
+ "inner join toutes_autorisations tas on p.microfusa_Id = tas.aquarius_id\n"
+ " \n"
+ "union\n"
+ " \n"
+ "select distinct p.microfusa_Id as aquarius_id, p.nom as aquarius_nom, p.prenom as aquarius_prenom , par.nom as authorised_nom\n"
+ ", par.prenom as authorised_prenom , 0 as IPerAut_interdiction, par.telephone, par.email, null as microfusaautoriseeID, 0 as modifiable\n"
+ ", 0 as IPerAutEx_interdiction, null as dt_debut, null as dt_Fin, null as autorisationID\n"
+ "from aquarius_du_menage_courant emc\n"
+ "inner join microfusa p on emc.microfusa_id = p.microfusa_id\n"
+ "inner join (\n"
+ "select p.NOM, p.PRENOM, ph.telephone, e.* from microfusa from microfusa p\n"
+ "left outer join phone ph on p.microfusa_id = ph.microfusa_id\n"
+ "left outer join email e on p.microfusa_id = e.microfusa_id\n"
+ "where p.microfusa_id in (select * from parents_du_menage_courant)\n"
+ ") par on 1 = 1\n"
+ ")\n"
+ "order by aquarius_id;")
but when I run the query in a test I have this error, but no message:
EL Warning]: 2020-10-17 20:16:38.297--UnitOfWork(522173599)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.7.v20200504-69f2c2b80d): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "with....
at org.h2.message.DbException.getJdbcSQLException(DbException.java:453)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
at org.h2.message.DbException.getSyntaxError(DbException.java:243)
at org.h2.command.Parser.getSyntaxError(Parser.java:1053)
at org.h2.command.Parser.read(Parser.java:4995)
at org.h2.command.Parser.readTableFilter(Parser.java:1893)
at org.h2.command.Parser.readJoin(Parser.java:2412)
at org.h2.command.Parser.parseJoinTableFilter(Parser.java:2839)
at org.h2.command.Parser.parseSelectFromPart(Parser.java:2828)
at org.h2.command.Parser.parseSelect(Parser.java:2959)
at org.h2.command.Parser.parseQuerySub(Parser.java:2817)
at org.h2.command.Parser.parseSelectUnion(Parser.java:2666)
at org.h2.command.Parser.readTableFilter(Parser.java:1892)
at org.h2.command.Parser.parseSelectFromPart(Parser.java:2827)
at org.h2.command.Parser.parseSelect(Parser.java:2959)
at org.h2.command.Parser.parseQuerySub(Parser.java:2817)
at org.h2.command.Parser.parseSelectUnion(Parser.java:2649)
at org.h2.command.Parser.parseWithQuery(Parser.java:6800)
at org.h2.command.Parser.parseWith1(Parser.java:6752)
at org.h2.command.Parser.parseWith(Parser.java:6722)
at org.h2.command.Parser.parseWithStatementOrQuery(Parser.java:2633)
at org.h2.command.Parser.parsePrepared(Parser.java:872)
at org.h2.command.Parser.parse(Parser.java:843)
at org.h2.command.Parser.parse(Parser.java:819)
at org.h2.command.Parser.prepareCommand(Parser.java:738)
at org.h2.engine.Session.prepareLocal(Session.java:657)
at org.h2.engine.Session.prepareCommand(Session.java:595)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1235)
at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:76)
at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:352)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1595)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1544)
at org.eclipse.persistence.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:806)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:628)
... 85 more
You have a typo in
+ "and dct.code in ('EMAIL', 'EMAIL2)\n"
it should be
+ "and dct.code in ('EMAIL', 'EMAIL2')\n"
It is possible that there are other problems, but you need to post a complete error message, in your question it is truncated and almost useless. For example, you can try to execute this query (with fixed typo) by using the JDBC directly.
In the edited question you have another error. from microfusa from microfusa p should be from microfusa p.