Two different FOR EACH (BREAK BY with JOIN) loops are giving unexpectedly different results?

I am attempting to match the sort order of a piece of code. The fields and comparisons in the following two FOR EACH statements are the same except for Item.PID requests a specific value in one query, and Item.Bill is asking for a specific value in the other. The two queries, however, return records in a different order.

The primary index for the Item table is Comp, PID, ItemID, IndNum.

FOR EACH Item
WHERE Item.Comp = 1
AND Item.Bill > 0
AND Item.PID = 123
AND Item.Store <> ?
AND Item.SecNum > 0
AND Item.TerNum <> ?
AND Item.Desc <> ?
AND Item.Date <> ?
AND Item.Code <> ""
AND Item.Type = "P"
AND Item.BillDate = 09/14/2016
AND Item.Method = "P"
NO-LOCK,
FIRST Patient USE-INDEX PatID
WHERE Patient.Comp = Item.Comp
AND Patient.ID = Item.PID
NO-LOCK
BREAK BY Item.Comp
BY Item.Bill
BY Patient.LName
BY Patient.FName
BY Item.PID
BY Item.Store
BY Item.SecNum
BY Item.TerNum
BY Item.Desc
BY Item.Date
BY Item.Code:
DISPLAY Amt.
END.

FOR EACH Item
WHERE Item.Comp = 1
AND Item.Bill = 456
AND Item.PID > 0
AND Item.Store <> ?
AND Item.SecNum > 0
AND Item.TerNum <> ?
AND Item.Desc <> ?
AND Item.Date <> ?
AND Item.Code <> ""
AND Item.Type = "P"
AND Item.BillDate = 09/14/2016
AND Item.Method = "P"
NO-LOCK,
FIRST Patient USE-INDEX PatID
WHERE Patient.Comp = Item.Comp
AND Patient.ID = Item.PID
NO-LOCK
BREAK BY Item.Comp
BY Item.Bill
BY Patient.LName
BY Patient.FName
BY Item.PID
BY Item.Store
BY Item.SecNum
BY Item.TerNum
BY Item.Desc
BY Item.Date
BY Item.Code:
DISPLAY Amt.
END.


The first query returns four records where the value in the Amt field is in the order:

827, 1124, 300, 102.


The second returns four records where the value in the Amt field is in the order:

827, 1124, 102, 300.


Removing Patient.LName from the second query's Break By matches the sort of the first query, but obviously will not properly sort my results (for multiple patients). I just thought this may be where the problem is.

Continue reading...