मेरे पास स्प्रिंग एमवीसी प्रोजेक्ट में यह जेपीए क्वेरी है

  @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;")

लेकिन जब मैं एक परीक्षण में क्वेरी चलाता हूं तो मुझे यह त्रुटि होती है, लेकिन कोई संदेश नहीं:

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
-1
Nunyet de Can Calçada 17 अक्टूबर 2020, 21:33

1 उत्तर

सबसे बढ़िया उत्तर

आपके पास एक टाइपो है

+ "and dct.code in ('EMAIL', 'EMAIL2)\n"

यह होना चाहिए

+ "and dct.code in ('EMAIL', 'EMAIL2')\n"

यह संभव है कि अन्य समस्याएं हों, लेकिन आपको एक पूर्ण त्रुटि संदेश पोस्ट करने की आवश्यकता है, आपके प्रश्न में यह छोटा और लगभग बेकार है। उदाहरण के लिए, आप सीधे JDBC का उपयोग करके इस क्वेरी (निश्चित टाइपो के साथ) को निष्पादित करने का प्रयास कर सकते हैं।


संपादित प्रश्न में आपको एक और त्रुटि है। from microfusa from microfusa p from microfusa p होना चाहिए।

4
Evgenij Ryazanov 19 अक्टूबर 2020, 17:52