You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@fineract.apache.org by Kigred Developer <ki...@gmail.com> on 2022/09/21 07:09:03 UTC
ORDER BY Clause not considered in Report Query
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
Re: [Mifos-users] ORDER BY Clause not considered in Report Query
Posted by Kigred Developer <ki...@gmail.com>.
Thanks again but this also doesn't work. The results are not ordered.
On Thu, 22 Sept 2022, 09:41 JOSE ALBERTO HERNANDEZ MALDONADO, <
jose.hernandez@fintecheando.mx> wrote:
> Please try the order by
>
> order by DATE(agje.entry_date)
>
>
> Sent from my iPhone
>
> On 22 Sep 2022, at 1:18, Kigred Developer <ki...@gmail.com>
> wrote:
>
>
> 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.hernandez@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 <ki...@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-users@lists.sourceforge.net
>> https://lists.sourceforge.net/lists/listinfo/mifos-users
>>
>> _______________________________________________
>> Mifos-users mailing list
>> Mifos-users@lists.sourceforge.net
>> https://lists.sourceforge.net/lists/listinfo/mifos-users
>>
> _______________________________________________
> Mifos-users mailing list
> Mifos-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/mifos-users
>
> _______________________________________________
> Mifos-users mailing list
> Mifos-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/mifos-users
>
Re: [Mifos-users] ORDER BY Clause not considered in Report Query
Posted by Kigred Developer <ki...@gmail.com>.
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.hernandez@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 <ki...@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-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/mifos-users
>
> _______________________________________________
> Mifos-users mailing list
> Mifos-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/mifos-users
>