Home:ALL Converter>parentesis missing in @query Java JPA

parentesis missing in @query Java JPA

Ask Time:2021-09-22T05:55:33         Author:Diego Izquierdo Dussan

Json Formatter

I am doing a query in a @repository class

It is the next:

@Query(value="(SELECT * FROM cor_documento WHERE ENVIADA_RECIBIDA = 'R' AND NVL(CANAL, ' ') != 'SDQS' AND fecha_Rad > :fechaRad order by fecha_Ra desc)   " ,nativeQuery=true )
public Iterable<CorDocumento> findByFechaRadicacion(Date fechaRadicacion);

and I get the next error:

2021-09-21 16:28:15.326 DEBUG 28164 --- [   scheduling-1] org.hibernate.SQL     : (SELECT * FROM cor_documento WHERE ENVIADA_RECIBIDA = 'R' AND NVL(CANAL, ' ') != 'SDQS' AND fecha_Radicacion > ? order by fecha_Radicacion desc)
Hibernate: (SELECT * FROM cor_documento WHERE ENVIADA_RECIBIDA = 'R' AND NVL(CANAL, ' ') != 'SDQS' AND fecha_Radicacion > ? order by fecha_Radicacion desc)
2021-09-21 16:28:15.361  WARN 28164 --- [   scheduling-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 907, SQLState: 42000
2021-09-21 16:28:15.361 ERROR 28164 --- [   scheduling-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : ORA-00907: falta el paréntesis derecho (the right parenthesis is missing)

2021-09-21 16:28:15.363 ERROR 28164 --- [   scheduling-1] o.s.s.s.TaskUtils$LoggingErrorHandler    : Unexpected error occurred in scheduled task

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
   at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:259) ~[spring-orm-5.3.8.jar:5.3.8]
   at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233) ~[spring-orm-5.3.8.jar:5.3.8]
   at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:551) ~[spring-orm-5.3.8.jar:5.3.8] 

When I delete the parentesis before "SELECT" ans after "desc" it works:

@Query(value="SELECT * FROM cor_documento WHERE ENVIADA_RECIBIDA = 'R' AND NVL(CANAL, ' ') != 'SDQS' AND fecha_Rad > :fechaRad order by fecha_Rad desc" ,nativeQuery=true )

But I dont see sense in the error cus I have other @Query whit that parenthesis and work correctly:

@Query(value="(SELECT * FROM (SELECT * FROM cor_documento WHERE to_char(fecha_Rad, 'dd-mm-yyyy') = to_char(sysdate, 'dd-mm-yyyy') AND ENVIADA_RECIBIDA = 'R' AND NVL(CANAL, ' ') != 'SDQS' order by fecha_Rad desc )  WHERE rownum <= 15)" ,nativeQuery=true )
public Iterable<CorDocumento> findLast15();

Someone can explain me why this happened?

thenks...

Author:Diego Izquierdo Dussan,eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/69275954/parentesis-missing-in-query-java-jpa
yy