3

I'm looking to use the following query to find the last tender id.

FOR EACH tender-table NO-LOCK WHERE tender-table.kco = 1 BY tender-table.id:
DISPLAY  tender-table.id.
END. 

This query looks at all the tender id's and brings back all the results of all the id's in ascending order. The results i get are

1,035 1.036 ...... 1,060 1,061 1,062 1,063 1,064 1,065 1,066

FOR LAST tender-table NO-LOCK WHERE tender-table.kco = 1 BY tender-table.id:
DISPLAY  tender-table.id.
END.

However when i use this query to find the last id, i get the result,

1,061

When I should be seeing the result 1,066. Can anyone suggest why this is happening?

Matt_Johndon
  • 204
  • 1
  • 6
  • 15

2 Answers2

7

FOR LAST is a very deceptive statement. (So is FOR FIRST.) It does not behave in an intuitive manner. The sort order is NOT specified by the BY statement. You will get the LAST record according to the index which is used and no sorting will take place. When the BY refers to an unindexed field (or one which does not sort in the order of the index actually used) or when the WHERE clause does not obviously map to an index in the order that you are hoping for you will have mysterious records chosen.

Personally, I strongly suggest that you forget about using FOR FIRST & FOR LAST. A better option, which always sorts as expected, would be:

FOR EACH tableName WHERE someCriteria BREAK BY sortOrder:
  LEAVE.
END.
DISPLAY whatEver.

(Add "DESCENDING" to flip from FIRST to LAST...)

Just in case anyone needs convincing -- try this with the "sports" database:

for first customer no-lock by discount:
  display name discount.
end.
Tom Bascom
  • 13,405
  • 2
  • 27
  • 33
  • WTF! oh no! I believed that it sort first ever! :( noooooo! I never noticed this in the documentation... – firhang Jan 10 '13 at 08:34
  • Can you clarify how to tell which index is being used? I'm trying to port some E9 BAQs to E10 and while my first attempt worked, the second query I tried it on I see E9 is *not* sorting according to the primary key fields of the table being joined. – Michael Nov 19 '18 at 22:27
  • Index selection depends on the WHERE clause. You can COMPILE XREF to see what index is actually being used. – Tom Bascom Nov 20 '18 at 01:17
  • I've not used ABL outside of BAQs, I'm assuming there is somewhere I can add this clause but I'm not familiar with how to get there... – Michael Nov 20 '18 at 17:08
  • Is E9 Epicor 9? I vaguely recall that Epicor has some method of adding customizations and extensions that involves writing snippets of code somehow. If that is what a "BAQ" is then Epicor's support team ought to be able to explain how to compile it to get xref output. The xref output shows many things but one of the key details is index selection. In any event, it is subtle but FOR FIRST and FOR LAST don't "sort" anything. They *select* no more than one record based on the index appropriate to the WHERE clause. "Sorting" one record (or zero records) doesn't depend at all on the index used. – Tom Bascom Nov 20 '18 at 18:02
  • Yup, E9=Epicor 9. I understand the part about selecting no more than one record based on the index appropriate to the WHERE clause. The thing is, for a particular table I'm looking at there are 15 indexes on this table, and of the first 6 I looked at, 4 didn't even return the same number of rows and the two that did had data mismatches. Assuming one of them is the right one, it's time prohibitive to try all of them for every table, assuming 15 indexes doesn't translate to "the WHERE clause does not obviously map to an index in the order that you are hoping for"... – Michael Nov 20 '18 at 18:39
  • It may be time for a new question or a different forum. Index selection is a complex topic that, while loosely related, is not really the subject of this question. – Tom Bascom Nov 20 '18 at 21:06
0

Sorry I have managed to figure it out that the 1,066 values didn't have tender-table.kco = 1. this solves the problem. thanks your time.

Matt_Johndon
  • 204
  • 1
  • 6
  • 15