You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@fineract.apache.org by "Arnav Patil (Jira)" <ji...@apache.org> on 2026/01/18 05:59:00 UTC
[jira] [Commented] (FINERACT-2436) Client Trends by Week or Month don't work on Postgresql
[ https://issues.apache.org/jira/browse/FINERACT-2436?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18052601#comment-18052601 ]
Arnav Patil commented on FINERACT-2436:
---------------------------------------
Hi, I’d like to work on this issue and propose a fix to make the Client Trends
reports compatible with PostgreSQL. Please let me know if this approach is fine.
Thanks!
> Client Trends by Week or Month don'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
> Priority: Trivial
>
> Client Trends by Week or Month reports don't work on Postgresql
> Report By Week:
> GET on [https://\|https://]{BASE_URL}/fineract-provider/api/v1/runreports/ClientTrendsByWeek?R_officeId=1&genericResultSet=false
> Report By Month:
> GET 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)