JPA+Hibernate查询触发ORA-00979错误：日期参数传递求解

问题根源 #

ORA-00979错误的核心是SELECT子句中的非聚合列未完全匹配GROUP BY子句中的表达式。在你的场景中，JPA参数绑定机制导致Oracle无法识别多次出现的TO_DATE(:fechaParametro, 'yyyy-MM-dd')是同一表达式——即便参数值相同，Oracle仍会将其视为不同的计算项，从而判定SELECT中的列未包含在GROUP BY中。

解决方案 #

方案1：直接传递日期类型参数，避免重复转换

将参数类型从String改为LocalDate（或java.util.Date），省去查询中的TO_DATE转换，让Oracle明确识别GROUP BY和SELECT中的表达式一致性。

修改后的方法定义：

List<EstadisticasProjection> obtenerDatosConsulta(@Param("fechaParametro") LocalDate fechaParametro);

修改后的查询语句：

@Query(value = "SELECT " +
        " Arrendador.id AS arrendadorId, " +
        " Tipo_Actividad.id AS tipoComunicacionId, " +
        " TO_CHAR(:fechaParametro, 'yyyy-MM-dd') AS fecha, " +
        " COUNT(CASE WHEN TRUNC(Com.fecha_contrato) = TRUNC(:fechaParametro) THEN 1 END) AS cantidad, " +
        " NVL(UltimaComunicacion.UltimaFechaContrato, TO_DATE('1900-01-01', 'yyyy-MM-dd')) AS fechaUltimoEnvio, " +
        " TRUNC(:fechaParametro) - TRUNC(NVL(UltimaComunicacion.UltimaFechaContrato, Arrendador.fecha_alta)) AS diasSinEnviar " +
        "FROM Arrendador " +
        "LEFT JOIN (SELECT arrendador_id, MAX(fecha_contrato) AS ultimaFechaContrato FROM Comunicacion GROUP BY arrendador_id) UltimaComunicacion ON Arrendador.id = UltimaComunicacion.arrendador_id " +
        "LEFT JOIN Comunicacion Com ON Arrendador.id = Com.arrendador_id " +
        "LEFT JOIN Tipo_Comunicacion ON Com.tipo_comunicacion_id = Tipo_Comunicacion.id " +
        "JOIN Tipo_Arrendador ON Arrendador.tipo_arrendador_id = Tipo_Arrendador.id " +
        "JOIN Tipo_Actividad ON Arrendador.tipo_actividad_id = Tipo_Actividad.id " +
        "GROUP BY Arrendador.id, " +
        " Tipo_Actividad.id, " +
        " TO_CHAR(:fechaParametro, 'yyyy-MM-dd'), " +
        " NVL(UltimaComunicacion.UltimaFechaContrato, TO_DATE('1900-01-01', 'yyyy-MM-dd')), " +
        " TRUNC(:fechaParametro) - TRUNC(NVL(UltimaComunicacion.UltimaFechaContrato, Arrendador.fecha_alta))",
        nativeQuery = true)

方案2：使用CTE统一参数引用

通过WITH子句将日期参数定义为一个临时变量，在查询中统一引用该变量，确保所有使用日期的地方都是同一表达式。

修改后的查询语句：

@Query(value = "WITH fecha_consulta AS (SELECT TRUNC(:fechaParametro) AS fecha FROM DUAL) " +
        "SELECT " +
        " Arrendador.id AS arrendadorId, " +
        " Tipo_Actividad.id AS tipoComunicacionId, " +
        " TO_CHAR(fc.fecha, 'yyyy-MM-dd') AS fecha, " +
        " COUNT(CASE WHEN TRUNC(Com.fecha_contrato) = fc.fecha THEN 1 END) AS cantidad, " +
        " NVL(UltimaComunicacion.UltimaFechaContrato, TO_DATE('1900-01-01', 'yyyy-MM-dd')) AS fechaUltimoEnvio, " +
        " fc.fecha - TRUNC(NVL(UltimaComunicacion.UltimaFechaContrato, Arrendador.fecha_alta)) AS diasSinEnviar " +
        "FROM Arrendador " +
        "CROSS JOIN fecha_consulta fc " +
        "LEFT JOIN (SELECT arrendador_id, MAX(fecha_contrato) AS ultimaFechaContrato FROM Comunicacion GROUP BY arrendador_id) UltimaComunicacion ON Arrendador.id = UltimaComunicacion.arrendador_id " +
        "LEFT JOIN Comunicacion Com ON Arrendador.id = Com.arrendador_id " +
        "LEFT JOIN Tipo_Comunicacion ON Com.tipo_comunicacion_id = Tipo_Comunicacion.id " +
        "JOIN Tipo_Arrendador ON Arrendador.tipo_arrendador_id = Tipo_Arrendador.id " +
        "JOIN Tipo_Actividad ON Arrendador.tipo_actividad_id = Tipo_Actividad.id " +
        "GROUP BY Arrendador.id, " +
        " Tipo_Actividad.id, " +
        " TO_CHAR(fc.fecha, 'yyyy-MM-dd'), " +
        " NVL(UltimaComunicacion.UltimaFechaContrato, TO_DATE('1900-01-01', 'yyyy-MM-dd')), " +
        " fc.fecha - TRUNC(NVL(UltimaComunicacion.UltimaFechaContrato, Arrendador.fecha_alta))",
        nativeQuery = true)

为什么SQL Developer中运行正常？ #

在SQL Developer中手动替换参数后，所有日期转换表达式都会变成相同的字面量（比如TO_DATE('2024-05-20', 'yyyy-MM-dd')），Oracle能直接识别这些是同一表达式，因此不会触发ORA-00979错误。而JPA的参数绑定会将每个:fechaParametro作为独立的绑定变量，Oracle默认不会优化这些重复的参数引用，导致表达式匹配失败。

内容的提问来源于stack exchange，提问作者jeffrey sanchez