"; expected "., ("; running JPA query

Question

I have this jpa query

@Query(nativeQuery = true, value = "with\n"
            + "    validBonansasAssignation as (\n"
            + "        select aga.autorisation_bonansas_id, aga.autorisation_bonansa_assign_id as iBonansaAffectation_id,\n"
            + "               s.site_id as ISiteNoTypeBaseData_id, s.desc_court as iSiteNoTypeBaseData_nom,\n"
            + "               aga.dt_debut as iBonansaAffectation_dt_debut, aga.dt_fin as iBonansaAffectation_dt_fin\n"
            + "        from AUTORISATION_BONANSA_ASSIGNATION aga\n"
            + "        inner join site s on aga.site_id = s.site_id\n"
            + "        where ?1 between aga.dt_debut and aga.dt_fin\n"
            + "    )\n"
            + "select ag.AUTORISATION_BONANSAS_ID, ag.NOM, ag.PRENOM, ag.EULOGIN, ag.dt_Debut, ag.dt_Fin\n"
            + "    , aga.iBonansaAffectation_id, aga.iSiteNoTypeBaseData_id, aga.iSiteNoTypeBaseData_nom\n"
            + "    , aga.iBonansaAffectation_dt_debut, aga.iBonansaAffectation_dt_fin\n"
            + "from autorisation_bonansa ag\n"
            + "left join validBonansasAssignation aga on ag.autorisation_bonansas_id = aga.autorisation_bonansas_id\n"
            + "where ?1 between ag.dt_debut and ag.dt_fin\n"
            + "and ag.organisation_id = ?2")
List<AutoUsers> find(Date date, Long id);

but when I run the test I have this error:

org.springframework.orm.jpa.JpaSystemException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.2.v20180622-f627448): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "WITH
    VALIDBONANSASASSIGNATION AS[*] (
        SELECT AGA.AUTORISATION_BONANSAS_ID, AGA.AUTORISATION_BONANSA_ASSIGN_ID AS IBONANSAAFFECTATION_ID,
                  S.SITE_ID AS ISITENOTYPEBASEDATA_ID, S.DESC_COURT AS ISITENOTYPEBASEDATA_NOM,
               AGA.DT_DEBUT AS IBONANSAAFFECTATION_DT_DEBUT, AGA.DT_FIN AS IBONANSAAFFECTATION_DT_FIN
        FROM AUTORISATION_BONANSA_ASSIGNATION AGA
        INNER JOIN SITE S ON AGA.SITE_ID = S.SITE_ID
        WHERE ? BETWEEN AGA.DT_DEBUT AND AGA.DT_FIN
    )
SELECT AG.AUTORISATION_BONANSAS_ID, AG.NOM, AG.PRENOM, AG.EULOGIN, AG.DT_DEBUT, AG.DT_FIN
    , AGA.IBONANSAAFFECTATION_ID, AGA.ISITENOTYPEBASEDATA_ID, AGA.ISITENOTYPEBASEDATA_NOM
    , AGA.IBONANSAAFFECTATION_DT_DEBUT, AGA.IBONANSAAFFECTATION_DT_FIN
FROM AUTORISATION_BONANSA AG
LEFT JOIN VALIDBONANSASASSIGNATION AGA ON AG.AUTORISATION_BONANSAS_ID = AGA.AUTORISATION_BONANSAS_ID
WHERE ? BETWEEN AG.DT_DEBUT AND AG.DT_FIN
AND AG.ORGANISATION_ID = ? "; expected "., ("; SQL statement:

Answer 1

The way the original query is generated by JPA form the error stack , I would suggest the to change like below and try,

I don't have right now the IDE to actual modify the query in proper format so provided the query in SQL format but the intention is to define the columns into the WITH clause because as you see in the error stack it got generated like WITH VALIDBONANSASASSIGNATION AS[*] ( and this [*] after AS is something Oracle will not recognise and throw syntax error.

WITH VALIDBONANSASASSIGNATION 
(
  AUTORISATION_BONANSAS_ID
, IBONANSAAFFECTATION_ID
, ISITENOTYPEBASEDATA_ID
, ISITENOTYPEBASEDATA_NOM
, IBONANSAAFFECTATION_DT_DEBUT
, IBONANSAAFFECTATION_DT_FIN
)
AS
(
  SELECT AGA.AUTORISATION_BONANSAS_ID
       , AGA.AUTORISATION_BONANSA_ASSIGN_ID AS IBONANSAAFFECTATION_ID
       , S.SITE_ID AS ISITENOTYPEBASEDATA_ID
       , S.DESC_COURT AS ISITENOTYPEBASEDATA_NOM
       , AGA.DT_DEBUT AS IBONANSAAFFECTATION_DT_DEBUT
       , AGA.DT_FIN AS IBONANSAAFFECTATION_DT_FIN
    FROM AUTORISATION_BONANSA_ASSIGNATION AGA
  INNER JOIN SITE S 
     ON AGA.SITE_ID = S.SITE_ID
  WHERE ? BETWEEN AGA.DT_DEBUT 
    AND AGA.DT_FIN
)
SELECT AG.AUTORISATION_BONANSAS_ID
     , AG.NOM
     , AG.PRENOM
     , AG.EULOGIN
     , AG.DT_DEBUT
     , AG.DT_FIN
     , AGA.IBONANSAAFFECTATION_ID
     , AGA.ISITENOTYPEBASEDATA_ID
     , AGA.ISITENOTYPEBASEDATA_NOM
     , AGA.IBONANSAAFFECTATION_DT_DEBUT
     , AGA.IBONANSAAFFECTATION_DT_FIN
FROM AUTORISATION_BONANSA AG
LEFT JOIN VALIDBONANSASASSIGNATION AGA 
  ON AG.AUTORISATION_BONANSAS_ID 
   = AGA.AUTORISATION_BONANSAS_ID
WHERE ? BETWEEN AG.DT_DEBUT AND AG.DT_FIN
  AND AG.ORGANISATION_ID = ?

I gave it a try and see if it works.

Answer 2

Can you set nativeQuery = true like below and see if this works.

@Query(
  value = "SELECT * FROM USERS u WHERE u.status = 1", 
  nativeQuery = true)