[Mifos-users] ORDER BY Clause not considered in Report Query

[Kigred Developer]

Good Morning  Devs,

I am creating a (TABLE) report with the following query:

SELECT
DATE(agje.entry_date) as date,
aga.name as 'Account',
agje.amount as 'Amount' ,
mpd.receipt_number as 'Receipt',
CASE
WHEN msat.transaction_type_enum=1 THEN 'Savings Deposit'
WHEN msat.transaction_type_enum =2 THEN 'Savings Withdrawal'
WHEN mlt.transaction_type_enum =1 THEN 'Loan Disbursement'
END AS 'Txn Type',
CASE
WHEN agje.savings_transaction_id IS NOT NULL THEN mc.display_name
WHEN agje.loan_transaction_id IS NOT NULL THEN mcl.display_name
END AS 'Client',
ml.id as 'Loan',
agje.description,
agje.office_running_balance as 'Running Balance',
ma.username as 'User'
FROM
acc_gl_journal_entry agje LEFT JOIN acc_gl_account aga
ON
agje.account_id = aga.id LEFT JOIN m_savings_account_transaction msat
ON
agje.savings_transaction_id = msat.id LEFT JOIN m_savings_account msa
ON
msa.id = msat.savings_account_id LEFT JOIN m_client mc
ON
msa.client_id = mc.id LEFT JOIN m_loan_transaction mlt
ON
agje.loan_transaction_id = mlt.id LEFT JOIN m_loan ml
ON
mlt.loan_id = ml.id LEFT JOIN m_client mcl
ON
ml.client_id = mcl.id LEFT JOIN m_appuser ma
ON
agje.lastmodifiedby_id = ma.id LEFT JOIN m_payment_detail mpd
ON msat.payment_detail_id = mpd.id

WHERE
 
agje.entry_date BETWEEN '${startDate}' AND '${endDate}' AND agje.account_id=${GLAccountNO} order by date DESC 

The report runs but it does not order the results as desired. I run the same query against the db and the results are ordered as desired but with the Fineract report the results are not ordered. I need the results to be ordered by date. What am I missing ?

Regards.

Wilfred

[JOSE ALBERTO HERNANDEZ MALDONADO]

Please try with this:

Order by 1 instead of Order by date

Some database managers have reserved words and I think date is one of them

Thanks and best regards

Sent from my iPhone

On 21 Sep 2022, at 2:09, Kigred Developer <kigred.d...@gmail.com> wrote:



_______________________________________________
Mifos-users mailing list
Mifos...@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/mifos-users

[Kigred Developer]

Thanks JOSE, but this has also not worked. Like I said in the previous email, the same query works as expected when run directly against the same database (NOT through Fineract's report module) and the results are ordered by date, it is only when I run it through the Fineract Reporting API that the "ORDER BY" clause is ignored. I think the issue is within  Fineract and not the query itself. I am using Fineract 1.7

Regards.

Wilfred

[JOSE ALBERTO HERNANDEZ MALDONADO]

Please try the order by

order by DATE(agje.entry_date)

Sent from my iPhone

On 22 Sep 2022, at 1:18, Kigred Developer <kigred.d...@gmail.com> wrote:



[Kigred Developer]

Thanks again but this also doesn't work. The results are not ordered.