Column 'XYZ' is invalid in the ORDER BY clause error

(1) Share

Report

Posted on by D365 beginner

592

Hi Experts,

I am getting below error while moving one of my report code from AX 2012 to Visual studio to build the same for D365 F&O. can someone help me to rewrite the code and clarify the issue with it. It works fine with AX 2012.

LedgerJournalTrans _ledgerJournalTrans, _ledgerJournalTrans1;

select sum(AmountCurDebit), avg(ExchRate) from _ledgerJournalTrans1 order by OffsetLedgerDimension asc group by LedgerDimension
where _ledgerJournalTrans1.AccountType == LedgerJournalACType::Vend
&& _ledgerJournalTrans1.OffsetAccountType == LedgerJournalACType::Bank
&& _ledgerJournalTrans1.TransactionType == LedgerTransType::Payment
&& _ledgerJournalTrans1.LedgerDimension == _ledgerJournalTrans.LedgerDimension
&& _ledgerJournalTrans1.OffsetLedgerDimension == _dimAttrValueCombination.RecId;

Error:- Column 'OffsetLedgerDimension' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause

All responses (1)

Answers (1)

Verified answer

Martin Dráb 232,980 Most Valuable Professional on at

Like (2)

Report

Column 'XYZ' is invalid in the ORDER BY clause error

The error message is correct - ordering by OffsetLedgerDimension doesn't make a good sense if it never has any value (because it's neither grouped by nor aggregate it). If you're saying that you're satisfied with the behavior anyway, you can simply remove the order by clause.