CAN-FIND(FIRST ...) Surprise

D.Cook

D.Cook

Member
Having read the documentation and a few posts here, I'm still a little confused about the purpose of the FIRST and LAST keywords.

I'm especially confused why these keywords are allowed in a CAN-FIND statement, as they seem completely irrelevant.

It's for this reason I coded some CAN-FIND statements without a FIRST. Quite some time later I discovered that at least one of these statements were not behaving -- returning FALSE when a record matching the conditions exists. I added the FIRST keyword and to my surprise, the statement returns TRUE!
Now I've boiled the statement down to just the table name (eg below), and still get the same results. I must be missing something big..

Code: 
DISPLAY CAN-FIND(FIRST tablename)
 
It's perhaps best to consider CAN-FIND as a logical interpretation of the following statements:

Code: 
FIND [FIRST] table WHERE <some-criteria>.
IF AVAILABLE table THEN
    RETURN TRUE.
ELSE
    RETURN FALSE.

(although the CAN-FIND wouldn't actutally read the contents of the record)

Now, if you have multiple records which are satisfied by the WHERE criteria, and you don't use FIRST, the standard FIND statement will fail due to ambiguity. So, the corresponding CAN-FIND will also fail for the same reason. However, a CAN-FIND(FIRST) will succeed because you're only interested in finding a single record, thereby avoiding the ambiguous result.

A simple example would be on the customer table:

Code: 
CAN-FIND([FIRST] customer where customer.active = TRUE NO-LOCK).

Because there are most likely lots of "active" customer records, the CAN-FIND will return FALSE unless you put the FIRST in, because the corresponding FIND statement would also fail (it would lead to an ambiguous result).

Hope this helps!
 
Also: use FIND only if you are 100% sure there is a single record of that kind. But be aware that progress scans the whole table (WHOLE-INDEX) because even if it found that particular record, it scans further till the end of the table to check if there is no other record that matches the condition.
 
enoon said:
Also: use FIND only if you are 100% sure there is a single record of that kind. But be aware that progress scans the whole table (WHOLE-INDEX) because even if it found that particular record, it scans further till the end of the table to check if there is no other record that matches the condition.
Click to expand...

The use of FIND does NOT automatically result in a full-table scan using WHOLE-INDEX. If your FIND or FIND FIRST doesn't pick a "good" index, then you run the risk of a full-table scan either way.

I guess the point is to ensure that any FIND (FIRST or otherwise) uses a correctly-defined index. If you don't, then FIND could be slower than FIND FIRST for the reasons you mentioned. But why would you do a FIND on a large table with a bad index selection anyway?
 
You must log in or register to reply here.
Back
Top