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

Question

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. The primary unique index for the Patient table is PatID - Comp, ID.

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 Item.Amt field is in the order:

827, 1124, 300, 102.

The second returns four records where the value in the Item.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.

Edit - specified field 'Amt' belongs to table 'Item'. Added index for table 'Patient'.

Answer 1

Progress determines the best index to use based on the WHERE and BREAK statements. Changing those may change the index that is used, and that may change the order of the records. If you use a dynamic query, you can see which index Progress chose with the INDEX-INFORMATION attribute.

DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.
DEFINE VARIABLE cForEach AS CHARACTER NO-UNDO.
DEFINE BUFFER bfItem FOR Item.

cForEach = "FOR EACH bfItem".

CREATE QUERY hQuery.
hQuery:SET-BUFFERS(BUFFER bfItem:HANDLE).
hQuery:QUERY-PREPARE(cForEach).
hQuery:QUERY-OPEN().
hQuery:GET-FIRST(NO-LOCK).

MESSAGE hQuery:INDEX-INFORMATION[1] VIEW-AS ALERT-BOX.

Answer 2

Without sharing the index definitions for the tables it is impossible to really say what is going on.

But if I had to guess I would think it likely that using "FIRST patient" is the root of your problem. FOR FIRST does not do what you (probably) think that it does. It returns the first record that satisfies the criteria in the WHERE clause (using the chosen or specified index) without any consideration at all for the BY phrases. IOW -- patient.lname does not come into play to determine which patient is "FIRST".

You will likely have better luck changing to "EACH patient" and eliminating the USE-INDEX. (You might need also to add some logic using functions like FIRST-OF etc inside the block as a result -- I cannot say based on what little is known about your indexes.)

The "EACH item" portions of the query will potentially use different indexes because you have different sets of fields with equality matches. Such matches drive the first part of the index selection algorithm. Combined with the behavior of FOR FIRST you have plenty of scop for different possible sort orders.