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
On Wed, Sep 21, 2022 at 5:12 PM JOSE ALBERTO HERNANDEZ MALDONADO <
jose.hernan...@fintecheando.mx> wrote:
> 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.develo...@gmail.com>
> wrote:
>
>
> 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
> _______________________________________________
> Mifos-users mailing list
> mifos-us...@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/mifos-users
>
> _______________________________________________
> Mifos-users mailing list
> mifos-us...@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/mifos-users
>
