Antipodean salutations.
I just spotted this in another forum from Tom B.
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...)
My question (to anyone) is: Why "BREAK" instead of simply "BY"?
Rgds
N/
And, yes, I included BREAK out of habit. No other special reason.
Antipodean salutations right back at ya! ;-)
Might be habit by Tom B. What did he say about it?
On the pure break phrase, the help says:
------------------------
Over a series of block iterations, you might want to do some work based on whether the value of a certain field changes. This field defines a break group. For example, you might be accumulating some value, such as a total. You use the BREAK option to define State as the break group
=========
HTH
Hey Molly
Sure - I've been using the BREAK since it was invented - likewise Tom's suggestion of "For Each - Leave" but I always used just "BY" in that case. I was beginning to wonder if I had been leaving a trail of carnage behind me (well, more than I was aware of anyway).
As to "what did Tom say?". That was it.
N/
The FOR FIRST/LAST behaviour is on our company's list of ABL pitfalls. I've always used FOR EACH... BY, without BREAK, without any problems, so I guess adding it is not needed...
those sort of pitfalls should be on psc 'get em fixed' list, the fact
that for find/last doesn't care about 'by' clause is simply a bug...
most of us know about it (at least those favouring for instead of
find/available), we can even document it but this doesn't mean it
disappeared :)
this is what the doc say on for-each statement "The FIRST and LAST
keywords are especially useful when you are sorting records in a table
in which you want to display information."... any developer not have
been bitten before will fall for that one :(
[collapse]On 04/08/2014 10:00 AM, Lieven De Foor wrote:
>[collapse] From: Lieven De Foor
> Post: RE: For First, For Last, For Each
> Posted in: OpenEdge Development
> Link: http://community.progress.com/technicalusers/f/19/p/9396/35972.aspx#35972
>
> The FOR FIRST/LAST behaviour is on our company's list of ABL pitfalls. I've always used FOR EACH... BY, without BREAK, without any problems, so I guess adding it is not needed...
>
>
> --
> You were sent this email because you opted to receive email notifications when someone created a new thread.
>
> To unsubscribe[collapse] from:
> - ...only this thread, disable notifications at http://community.progress.com/technicalusers/f/19/p/9396/35972.aspx#35972.
> - ...all email notifications from Progress Community, navigate to "Settings", click on the "Email" tab, then under the "Email Configuration" section, set Send Notifications to "No".
>
>
--
m.edu
keep it simple
http://www.ganimede.ro
http://ro.linkedin.com/in/marianedu
medu@ganimede.ro
mobile: +40 740 036 212
skype: marian.edu[/collapse][/collapse][/collapse]
Flag this post as spam/abuse.
This is an interesting topic...I am a new programmer and very new to the performance nuances of writing Progress queries. I shared this thread with a fellow programmer who is very experienced with it. Here is his take:
***
I guess the way I’ve always interpreted the BY functionality, it seems to me the problem is an obvious issue with how they are using the BY clause on the FOR FIRST/LAST.
The BY sorts the records after they have all been retrieved in the order dictated by the index. Thus the FIRST/LAST is only going to get the one record based on the index, there is no sorting that is ever going to occur.
From the OE help:
BY expression [ DESCENDING ]
Sorts the selected records by the value of expression
This is also something to consider in (browse) queries as it can be a huge performance issue. The query must first find all the records that meet the query specifications before it can do the BY sorting.
2 examples to illustrate FOR EACH speed issue.
Take a table with 30,000 records
1) Say you want to get all the records for one person and sort by date. You use an index that has the person’s name-in. That query finds 100 records, they are sorted by date and returned. Now if you have a browse displaying only 20 at a time, the same look up of finding 100 records each time needs to be done each page change. Which really isn’t all that bad considering, if you really need a browse to display like that and no proper index is currently available (a better index should be added for next schema cut though and then the program updated).
2) For the second example you want to show every record sorted by date again, but for all the people, and there is no index with the date to narrow down the query. Now the query has to read all 30,000 records first before it can sort on the date. So if you have the browse showing 20 records, all 30k records are read. Then to query the page to the next 20 all 30k records need to be read again. So that would probably result in an unworkable/unbearable program.
In process.- thanks Brian.
To be honest, I've been aware of this issue for longer than I care to remember and just acepted that "that's the way it is". It was only the other day that I was retro-fixing someone else's code that I thought about it.
I'm now wondering if fixing this bug^h^h^hfeature will break anyone's code. Ah well - can't make an omlette.......
N/
[collapse]On 04/09/2014 01:22 AM, Nigel Allen wrote:
>[collapse] From: Nigel Allen
> Post: RE: For First, For Last, For Each
> Posted in: OpenEdge Development
> Link: http://community.progress.com/technicalusers/f/19/p/9396/36032.aspx#36032
>
> In process.- thanks Brian.
> To be honest, I've been aware of this issue for longer than I care to remember and just acepted that "that's the way it is". It was only the other day that I was retro-fixing someone else code that I thought about it.
> I'm now wondering if fixing this bug^h^h^hfeature will break anyone's code. Ah well - can't make an omlette.......
don't think anyone using 'by' in a for first/last would actually count
on actually getting a randomly record that satisfy the where clause...
if there is they can easily fix the code by removing the 'by' option :)
what would probably make a nice omelette, will be fixing the buffer
scope for the same for find/last statements ;)
> N/
>
>
> --
> You were sent this email because you opted to receive email notifications when someone created a new thread.
>
> To unsubscribe[collapse] from:
> - ...only this thread, disable notifications at http://community.progress.com/technicalusers/f/19/p/9396/36032.aspx#36032.
> - ...all email notifications from Progress Community, navigate to "Settings", click on the "Email" tab, then under the "Email Configuration" section, set Send Notifications to "No".
>
>
--
m.edu
keep it simple
http://www.ganimede.ro
http://ro.linkedin.com/in/marianedu
medu@ganimede.ro
mobile: +40 740 036 212
skype: marian.edu[/collapse][/collapse][/collapse]
First, BREAK is not necessary, unless you are working with break groups.
On the first/last and BY discussion, we have added specific information and examples under the FIRST and LAST options of the FOR statement stating that the records are retrieved before any sorting is done to bring that exact point to the developer's attention.
The model is that we resolve the query and get the record(s) first and once we have the record(s), we sort them. Sorting is done after we have the result set.
The use of "FOR EACH table BY field" with LEAVE as shown in a previous post (and in the documentation) actually looks more like what it really is and performs as expected, that is, get all records that satisfy the query, sort them and then leave once you iterate thought the first (or last) record.
Things like MAX-ROWS also have to be used with the understanding that sorting happens after the records are read. Doing anything other than that defeats the purpose if we have to read all records anyway before sorting them and could catch people by surprise with performance issues.
If all that said, then a FOR FIRST table BY field doesn't make much sense if sorting will be done. When the BY clause works as substitute for USE-INDEX, it would be clearer to use USE-INDEX instead for a FOR FIRST/LAST block.
I will say though that I sympathize with the confusion that this may cause so we could look into adding a compiler warning when we see FOR FIRST/LAST statement with a BY clause that causes sorting to alert developers that the result may not be what they expect.
The best solution is probably to remove FOR FIRST and FOR LAST from the language altogether. Unlikely to happne though.
it's a shame that you can't keyword forget "for first" ;)
On 10 April 2014 21:14, gus wrote:
> RE: For First, For Last, For Each
> Reply by gus
>
> The best solution is probably to remove FOR FIRST and FOR LAST from the
> language altogether. Unlikely to happne though.
>
> Stop receiving emails on this subject.
>
> Flag this post as spam/abuse.
--
Julian Lyndon-Smith
IT Director,
dot.r
http://www.dotr.com
"The bitterness of poor quality remains long after the sweetness of
low price is forgotten"
Follow dot.r on http://twitter.com/DotRlimited
And why not just go ahead and create a bug?
And, yes, I included BREAK out of habit. No other special reason.
which is faster find when compared to find first and find first when compared to for first?reason?
[collapse]Reply by ChUIMonsterAnd, yes, I included BREAK out of habit. No other special reason.
Stop receiving emails on this subject.Flag this post as spam/abuse.
The idea that there is a speed advantage is a myth.
If your are dealing with UNIQUE records there is no speed advantage to FIRST.
If you are not dealing with unique records the comparison is meaningless and the use of FIRST is a bug waiting to happen.
I have added two enhancement to the product backlog based on this Community thread. I added:
(1) An enhancement to the Compiler, to generate a warning message when it encounters a FOR FIRST/LAST statement with a BY clause that causes sorting. The message would alert the ABL developer that the result may not be what they expect. If this warning message was viewed as unnecessary, developers can suppress it.
(2) An enhancement to the forget list infrastructure to support FOR FIRST/LAST
Way to go Evan!!!