Having Hi,
The Oracle PL/SQL Native SUM function (calcaulated field) is not working as expected, it is converting float to integer values, the next is a following code example :
{ code:java noformat }@Override
public ArrayList<Object[]> getEncMorososNotificados testSUMError ( String pTipo, String pFechaInicio, String pFechaFin, String pNotificador ) {
Session session = sessionFactory.getCurrentSession();
StringBuilder consulta
Query objQuery = new StringBuilder();
String vNotificador = "";
if (pTipo session . equals createNativeQuery (" 3 " )) {
vNotificador = pNotificador;
}
try {
consulta.append( " ""
SELECT C
19 . NUM_NOTIFICACION 80 COLUMN_OK , C.NUM_AVISO, C.GIS, C.CEDULA, C.RAZON_SOCIAL, C.TELEFONO, C.FEC_NOTIFICACION, C.ENTREGADO, C.FEC_ENTREGA, C.COD_NOTIFICADOR, C.OBSERVACION, C.USUA_REGIST, C.NO_ENTREGADO, C.FEC_NO_ENTREGADO, \
C.FEC_DESBLOQUEO, SUM( SERV_TRIBUTARIOS 39 . FNC_NOTIFICACION_COBRO (C.GIS, C.CEDULA, C.NUM_AVISO, TO_NUMBER (C.NUM_NOTIFICACION 74 ) , NULL, 3)) NOTIFICADO, COLUMN_FAIL \
SUM(SERV_TRIBUTARIOS.FNC_NOTIFICACION_COBRO (C.GIS, C.CEDULA, C.NUM_AVISO, TO_NUMBER (C.NUM_NOTIFICACION), NULL, 1)) PENDIENTE, \
SUM(SERV_TRIBUTARIOS.FNC_NOTIFICACION_COBRO (C.GIS, C.CEDULA, C.NUM_AVISO, TO_NUMBER (C.NUM_NOTIFICACION), C.NUM_PAGO, 4)) CANCELADO, \
SUM(SERV_TRIBUTARIOS.FNC_NOTIFICACION_COBRO (C.GIS, C.CEDULA, C.NUM_AVISO, TO_NUMBER (C.NUM_NOTIFICACION), C.NUM_PAGO, 5)) ELIMINADO \
FROM (SELECT DISTINCT A.NUM_NOTIFICACION, A.NUM_AVISO, D.GIS, D.CEDULA, A.RAZON_SOCIAL, A.TELEFONO, TO_CHAR (A.FEC_NOTIFICACION, 'YYYY-MM-DD') FEC_NOTIFICACION, A.ENTREGADO, TO_CHAR (A.FEC_ENTREGA, 'YYYY-MM-DD') FEC_ENTREGA, \
A.COD_NOTIFICADOR, A.OBSERVACION, A.USUA_REGIST, A.NO_ENTREGADO, TO_CHAR (A.FEC_NO_ENTREGADO, 'YYYY-MM-DD') FEC_NO_ENTREGADO, TO_CHAR (A.FEC_DESBLOQUEO, 'YYYY-MM-DD') FEC_DESBLOQUEO, D.NUM_PAGO \
FROM SERV_TRIBUTARIOS.NOTIF_AVISO_COBRO_ENC A, SERV_TRIBUTARIOS.NOTIF_AVISO_COBRO_DET D \
WHERE A.COD_CIA = '01' \
AND D.CEDULA = A.CEDULA \
AND A.NUM_NOTIFICACION = D.NUM_NOTIFICACION \
AND A.NUM_AVISO = D.NUM_AVISO \
AND D.CEDULA NOT IN (SELECT DISTINCT CEDULA FROM MUNI.EXCLU_CED_LISTADOS WHERE TIPO_PROCESO = '01') \
AND A.COD_NOTIFICADOR = NVL(:pNotificador, A.COD_NOTIFICADOR)\
DUAL
""" );
switch (pTipo) {
case "1":
consulta.append("""
AND A.FEC_ENTREGA BETWEEN TO_DATE(:pFechaInicio , 'YYYY-MM-DD') AND TO_DATE(:pFechaFin, 'YYYY-MM-DD') \
AND NVL(A.ENTREGADO, 'N') = 'S' \
ORDER BY A.NUM_NOTIFICACION) C \
""");
break;
case "2":
consulta.append("""
AND A.FEC_NOTIFICACION BETWEEN TO_DATE(:pFechaInicio, 'YYYY-MM-DD') AND TO_DATE(:pFechaFin, 'YYYY-MM-DD') \
AND NVL(A.NO_ENTREGADO,'N') = 'S' \
ORDER BY A.NUM_NOTIFICACION) C \
""");
break;
case "3":
consulta.append("""
AND A.FEC_NOTIFICACION BETWEEN TO_DATE(:pFechaInicio, 'YYYY-MM-DD') AND TO_DATE(:pFechaFin, 'YYYY-MM-DD') \
ORDER BY D.CEDULA, A.NUM_AVISO) C \
""");
break;
default:
break;
}
consulta.append("""
GROUP BY C.NUM_NOTIFICACION, C.NUM_AVISO, C.GIS, C.CEDULA, C.RAZON_SOCIAL, C.TELEFONO, C.FEC_NOTIFICACION, C.ENTREGADO, C.FEC_ENTREGA, C.COD_NOTIFICADOR, C.OBSERVACION, C.USUA_REGIST, \
C.NO_ENTREGADO, C.FEC_NO_ENTREGADO, C.FEC_DESBLOQUEO\
""");
Query objQuery = session.createNativeQuery(consulta.toString(), Object.class);
objQuery.setParameter("pFechaInicio", pFechaInicio);
objQuery.setParameter("pFechaFin", pFechaFin);
objQuery.setParameter("pNotificador", vNotificador);
objQuery.setCacheable(true);
return (ArrayList<Object[]>) objQuery.list();
} catch (HibernateException ex) {
throw LoggerManager.throwException(ex);
noformat }
}{code}
All If we test the calculated fields, ex.
SELECT the return object is:
{noformat}SUM(SERV_TRIBUTARIOS
!imagen-20230531-171622 . FNC_NOTIFICACION_COBRO (C.GIS png|width=1254 , C.CEDULA, C.NUM_AVISO, TO_NUMBER (C.NUM_NOTIFICACION), NULL, 1)) PENDIENTE{noformat} height=329!
Are returning Integer data, but the expected field have decimals.
Debugging a little the excecution, what I
as you can see is that , the “doExtract“ 39 is calling the IntegerJdbcType for some reason ({{The trace below was generated with the intention of seeing what was going on not even rounding , it only deletes the actual execution is error free}})
{noformat} at oracle decimals . jdbc.driver.GeneratedScrollableResultSet.getInt(GeneratedScrollableResultSet.java:246)
at org.hibernate.type.descriptor.jdbc.IntegerJdbcType$2.doExtract(IntegerJdbcType.java:88)
at org.hibernate.type.descriptor.jdbc.BasicExtractor.extract(BasicExtractor.java:44)
at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.readCurrentRowValues(JdbcValuesResultSetImpl.java:262){noformat} |
|
