FINERACT-2054: BadSqlGrammar Error in Loans Awaiting Disbursal Summary Report
This error is thrown because of Office/Branch( ounder.name) is not mentioned in the Group by clause
diff --git a/fineract-provider/src/main/resources/db/changelog/tenant/parts/0002_initial_data.xml b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0002_initial_data.xml
index d30f543..69cb275 100644
--- a/fineract-provider/src/main/resources/db/changelog/tenant/parts/0002_initial_data.xml
+++ b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0002_initial_data.xml
@@ -12734,7 +12734,7 @@
<column name="report_subtype"/>
<column name="report_category" value="Loan"/>
<column name="report_sql"
- value="SELECT concat(repeat("..", ((LENGTH(ounder.`hierarchy`) - LENGTH(REPLACE(ounder.`hierarchy`, '.', '')) - 1))), ounder.`name`) as "Office/Branch", pl.`name` as "Product", ifnull(cur.display_symbol, l.currency_code) as Currency, f.`name` as Fund, sum(l.principal_amount) as Principal from m_office o join m_office ounder on ounder.hierarchy like concat(o.hierarchy, '%') and ounder.hierarchy like concat('${currentUserHierarchy}', '%') join m_client c on c.office_id = ounder.id join m_loan l on l.client_id = c.id join m_product_loan pl on pl.id = l.product_id left join m_staff lo on lo.id = l.loan_officer_id left join m_currency cur on cur.code = l.currency_code left join m_fund f on f.id = l.fund_id left join m_code_value purp on purp.id = l.loanpurpose_cv_id where o.id = ${officeId} and (l.currency_code = "${currencyId}" or "-1" = "${currencyId}") and (l.product_id = "${loanProductId}" or "-1" = "${loanProductId}") and (ifnull(l.loan_officer_id, -10) = "${loanOfficerId}" or "-1" = "${loanOfficerId}") and (ifnull(l.fund_id, -10) = ${fundId} or -1 = ${fundId}) and (ifnull(l.loanpurpose_cv_id, -10) = ${loanPurposeId} or -1 = ${loanPurposeId}) and l.loan_status_id = 200 group by ounder.hierarchy, pl.`name`, l.currency_code, f.`name` order by ounder.hierarchy, pl.`name`, l.currency_code, f.`name`"/>
+ value="SELECT concat(repeat("..", ((LENGTH(ounder.`hierarchy`) - LENGTH(REPLACE(ounder.`hierarchy`, '.', '')) - 1))), ounder.`name`) as "Office/Branch", pl.`name` as "Product", ifnull(cur.display_symbol, l.currency_code) as Currency, f.`name` as Fund, sum(l.principal_amount) as Principal from m_office o join m_office ounder on ounder.hierarchy like concat(o.hierarchy, '%') and ounder.hierarchy like concat('${currentUserHierarchy}', '%') join m_client c on c.office_id = ounder.id join m_loan l on l.client_id = c.id join m_product_loan pl on pl.id = l.product_id left join m_staff lo on lo.id = l.loan_officer_id left join m_currency cur on cur.code = l.currency_code left join m_fund f on f.id = l.fund_id left join m_code_value purp on purp.id = l.loanpurpose_cv_id where o.id = ${officeId} and (l.currency_code = "${currencyId}" or "-1" = "${currencyId}") and (l.product_id = "${loanProductId}" or "-1" = "${loanProductId}") and (ifnull(l.loan_officer_id, -10) = "${loanOfficerId}" or "-1" = "${loanOfficerId}") and (ifnull(l.fund_id, -10) = ${fundId} or -1 = ${fundId}) and (ifnull(l.loanpurpose_cv_id, -10) = ${loanPurposeId} or -1 = ${loanPurposeId}) and l.loan_status_id = 200 group by ounder.hierarchy, ounder.name, pl.`name`, l.currency_code, f.`name` order by ounder.hierarchy, ounder.name, pl.`name`, l.currency_code, f.`name`"/>
<column name="description" value="Individual Client Report"/>
<column name="core_report" valueBoolean="true"/>
<column name="use_report" valueBoolean="true"/>
