Victor Romero created FINERACT-2436:
---------------------------------------
Summary: Client Trends by Week or Month doesn't work on Postgresql
Key: FINERACT-2436
URL: https://issues.apache.org/jira/browse/FINERACT-2436
Project: Apache Fineract
Issue Type: Improvement
Reporter: Victor Romero
Client Trends by Week or Month doesn't work on Postgresql
https://\{BASE_URL}/fineract-provider/api/v1/runreports/ClientTrendsByWeek?R_officeId=1&genericResultSet=false
https://\{BASE_URL}/fineract-provider/api/v1/runreports/ClientTrendsByMonth?R_officeId=1&genericResultSet=false
Exception (By Week):
fineract-server-1 | 00:20:50.894 [https-jsse-nio-8443-exec-10] ERROR
o.a.f.i.d.s.GenericDataServiceImpl - Reporting error: StatementCallback; bad
SQL grammar [select x.* from (SELECT COUNT(cl.id) AS count,
WEEK(cl.activation_date) AS Weeks FROM m_office o LEFT JOIN
m_client cl on o.id = cl.office_id WHERE o.hierarchy like
concat((select ino.hierarchy from m_office ino where ino.id = 1),'%' )
AND (cl.activation_date BETWEEN (DATE '2026-01-17' - INTERVAL '12 WEEK')
AND DATE(TIMESTAMP '2026-01-18 00:20:50')) GROUP BY Weeks
) x]
fineract-server-1 | 00:20:50.894 [https-jsse-nio-8443-exec-7] ERROR
o.a.f.i.d.s.GenericDataServiceImpl - Reporting error: StatementCallback; bad
SQL grammar [select x.* from (SELECT COUNT(ln.id) AS lcount,
WEEK(ln.disbursedon_date) AS Weeks FROM m_office o
LEFT JOIN m_client cl on o.id = cl.office_id LEFT JOIN m_loan
ln on cl.id = ln.client_id WHERE o.hierarchy like concat((select
ino.hierarchy from m_office ino where ino.id = 1),'%' ) AND
(ln.disbursedon_date BETWEEN (DATE '2026-01-17' - INTERVAL '12 WEEK') AND
DATE(TIMESTAMP '2026-01-18 00:20:50')) GROUP BY Weeks )
x]
fineract-server-1 | 00:20:50.894 [https-jsse-nio-8443-exec-10] WARN
o.a.f.i.core.exception.ErrorHandler - Handled exception is
fineract-server-1 | org.springframework.jdbc.BadSqlGrammarException:
StatementCallback; bad SQL grammar [select x.* from (SELECT COUNT(cl.id) AS
count, WEEK(cl.activation_date) AS Weeks FROM m_office o LEFT JOIN
m_client cl on o.id = cl.office_id WHERE o.hierarchy like
concat((select ino.hierarchy from m_office ino where ino.id = 1),'%' )
AND (cl.activation_date BETWEEN (DATE '2026-01-17' - INTERVAL '12 WEEK')
AND DATE(TIMESTAMP '2026-01-18 00:20:50')) GROUP BY Weeks
) x]
fineract-server-1 | at
org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:114)
fineract-server-1 | at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:107)
fineract-server-1 | at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:116)
fineract-server-1 | at
org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1556)
fineract-server-1 | at
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:408)
fineract-server-1 | at
org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:476)
fineract-server-1 | at
org.springframework.jdbc.core.JdbcTemplate.queryForRowSet(JdbcTemplate.java:541)
fineract-server-1 | at
org.apache.fineract.infrastructure.dataqueries.service.GenericDataServiceImpl.fillGenericResultSet(GenericDataServiceImpl.java:72)
fineract-server-1 | at
org.apache.fineract.infrastructure.dataqueries.service.ReadReportingServiceImpl.retrieveGenericResultset(ReadReportingServiceImpl.java:121)
fineract-server-1 | at
org.apache.fineract.infrastructure.dataqueries.service.export.JsonDatatableReportExportService.export(JsonDatatableReportExportService.java:47)
fineract-server-1 | at
org.apache.fineract.infrastructure.dataqueries.service.DatatableReportingProcessService.processRequest(DatatableReportingProcessService.java:65)
fineract-server-1 | at
org.apache.fineract.infrastructure.dataqueries.api.RunreportsApiResource.processReportRequest(RunreportsApiResource.java:153)
fineract-server-1 | at
org.apache.fineract.infrastructure.dataqueries.api.RunreportsApiResource.runReport(RunreportsApiResource.java:128)
fineract-server-1 | at
java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
fineract-server-1 | at
java.base/java.lang.reflect.Method.invoke(Method.java:580)
Exception (By month):
postgresql-1 | 2026-01-18 00:30:44.924 UTC [20190] ERROR: function
monthname(date) does not exist at character 49
postgresql-1 | 2026-01-18 00:30:44.924 UTC [20190] HINT: No function
matches the given name and argument types. You might need to add explicit type
casts.
postgresql-1 | 2026-01-18 00:30:44.924 UTC [20190] STATEMENT: select x.*
from (SELECT COUNT(ln.id) AS lcount, MONTHNAME(ln.disbursedon_date) AS Months
FROM m_office o LEFT JOIN m_client cl on o.id =
cl.office_id LEFT JOIN m_loan ln on cl.id = ln.client_id
WHERE o.hierarchy like concat((select ino.hierarchy from m_office ino
where ino.id = 1),'%' ) AND (ln.disbursedon_date BETWEEN (DATE
'2026-01-17' - INTERVAL '12 MONTH') AND DATE(TIMESTAMP '2026-01-18 00:30:44'))
GROUP BY Months ) x
fineract-server-1 | 00:30:44.924 [https-jsse-nio-8443-exec-1] ERROR
o.a.f.i.d.s.GenericDataServiceImpl - Reporting error: StatementCallback; bad
SQL grammar [select x.* from (SELECT COUNT(ln.id) AS lcount,
MONTHNAME(ln.disbursedon_date) AS Months FROM m_office o
LEFT JOIN m_client cl on o.id = cl.office_id LEFT JOIN
m_loan ln on cl.id = ln.client_id WHERE o.hierarchy like
concat((select ino.hierarchy from m_office ino where ino.id = 1),'%' )
AND (ln.disbursedon_date BETWEEN (DATE '2026-01-17' - INTERVAL '12
MONTH') AND DATE(TIMESTAMP '2026-01-18 00:30:44')) GROUP BY Months
) x]
fineract-server-1 | 00:30:44.925 [https-jsse-nio-8443-exec-1] WARN
o.a.f.i.core.exception.ErrorHandler - Handled exception is
fineract-server-1 | org.springframework.jdbc.BadSqlGrammarException:
StatementCallback; bad SQL grammar [select x.* from (SELECT COUNT(ln.id) AS
lcount, MONTHNAME(ln.disbursedon_date) AS Months FROM m_office o
LEFT JOIN m_client cl on o.id = cl.office_id LEFT
JOIN m_loan ln on cl.id = ln.client_id WHERE o.hierarchy like
concat((select ino.hierarchy from m_office ino where ino.id = 1),'%' )
AND (ln.disbursedon_date BETWEEN (DATE '2026-01-17' - INTERVAL '12
MONTH') AND DATE(TIMESTAMP '2026-01-18 00:30:44')) GROUP BY Months
) x]
fineract-server-1 | at
org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:114)
fineract-server-1 | at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:107)
fineract-server-1 | at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:116)
fineract-server-1 | at
org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1556)
fineract-server-1 | at
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:408)
fineract-server-1 | at
org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:476)
fineract-server-1 | at
org.springframework.jdbc.core.JdbcTemplate.queryForRowSet(JdbcTemplate.java:541)
fineract-server-1 | at
org.apache.fineract.infrastructure.dataqueries.service.GenericDataServiceImpl.fillGenericResultSet(GenericDataServiceImpl.java:72)
fineract-server-1 | at
org.apache.fineract.infrastructure.dataqueries.service.ReadReportingServiceImpl.retrieveGenericResultset(ReadReportingServiceImpl.java:121)
fineract-server-1 | at
org.apache.fineract.infrastructure.dataqueries.service.export.JsonDatatableReportExportService.export(JsonDatatableReportExportService.java:47)
fineract-server-1 | at
org.apache.fineract.infrastructure.dataqueries.service.DatatableReportingProcessService.processRequest(DatatableReportingProcessService.java:65)
fineract-server-1 | at
org.apache.fineract.infrastructure.dataqueries.api.RunreportsApiResource.processReportRequest(RunreportsApiResource.java:153)
fineract-server-1 | at
org.apache.fineract.infrastructure.dataqueries.api.RunreportsApiResource.runReport(RunreportsApiResource.java:128)
fineract-server-1 | at
java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
fineract-server-1 | at
java.base/java.lang.reflect.Method.invoke(Method.java:580)
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
