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