Order By in MorphX report doesn't work properly in X++ code

(0) Share

Report

Posted on by SGbron

157

Hello Everyone

I wrote the code below....
In the first case I try to sort the lines of the report by itemId and ItemRoutId and it works fine

while select ProdBOMtmp order by ItemRouteId,ItemId
    where ProdBOMtmp.OprNum == ProdRoute.OprNum
    outer join prodJournalBOMselect
        where prodJournalBOMselect.InventTransId == ProdBOMtmp.InventTransId
        && prodJournalBOMselect.JournalId == prodJournalBOM.JournalId
        outer join MyInventItemLocation 
            where MyInventItemLocation.InventItemLocation == prodJournalBOMselect.InventItemLocation

In the second case I try to sort the lines by printingSequence witch is in a custom MyInventItemLocation table
(I already use this field in other reports and the order works fine)

while select ProdBOMtmp 
    where ProdBOMtmp.OprNum == ProdRoute.OprNum
    outer join prodJournalBOMselect
        where prodJournalBOMselect.InventTransId == ProdBOMtmp.InventTransId
        && prodJournalBOMselect.JournalId == prodJournalBOM.JournalId
        outer join MyInventItemLocation order by printingSequence
            where MyInventItemLocation.InventItemLocation == prodJournalBOMselect.InventItemLocation

But in this one doesn't work.

Does anyone understand why?????

I have the same question (0)

All responses (4)

Answers (0)

nmaenpaa 101,162 Moderator on at

Like (1)

Report

You say that you use it in some other place and it works fine. Perhaps you can compare your x++ select statements that work, to spot the difference to the one that doesn't work as you would expect.

These queries and the sort order of the result is not related to MorphX reports, so you can just test it in a job.

The report simply displays the data that you provide to it, in the order that you provide in the select statement.

Was this reply helpful? Yes No
Gunjan Bhattachayya 35,423 on at

Like (1)

Report
Hi SGbon,

Please try this -

while select ProdBOMtmp order by MyInventItemLocation.printingSequence where ProdBOMtmp.OprNum == ProdRoute.OprNum outer join prodJournalBOMselect where prodJournalBOMselect.InventTransId == ProdBOMtmp.InventTransId && prodJournalBOMselect.JournalId == prodJournalBOM.JournalId outer join MyInventItemLocation where MyInventItemLocation.InventItemLocation == prodJournalBOMselect.InventItemLocation

Was this reply helpful? Yes No
SGbron 157 on at

Like (0)

Report

So I found where the problem was.

Somewhere in the code the ProdBOMtmp has the property ProdBOMtmp.setTmp().

That turns the table to temporary Table.

So the join statements was not working becasue of that.

Was this reply helpful? Yes No
MuthukumaranAX 2,903 on at

Like (0)

Report

Hi SGbron,

Could you pls mark it as "Resolved, if the issue is fixed.

thanks

Was this reply helpful? Yes No