@query Java JPA中缺少括号

Question

我正在做一个查询@repository类

是下一个:

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

，我得到下一个错误:

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] 

当我删除"SELECT"前的括号时在"desc"之后的Ans工作原理:

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

但是我在错误中看不到意义，因为我有其他的@Query，括号和正常工作:

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

谁能给我解释一下为什么会这样?thenks…

Answer 1

显然代码

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

可以工作，因为它是子查询，所以右边是额外的括号。