NOFILTER Clause on an SQL SELECT, when to use?

I believe some VFP developers are still wondering what this clause is all about so I will try to explain things based on my perspective.  NOFILTER clause is given to us to ensure that a true cursor and not just a filtered result of the source table (FROM) is created.   

When is it needed?  The basic answer is if you are performing an SQL SELECT ... FROM  .. a single table  with no joins whatsoever and no extra new field created.  Without adding the NOFILTER clause, what will happen is you will only have a filtered result of the source single table.  That you are still working on the same table but just a filtered result.

Whereas what most of us needed is a true cursor or something that is independent and separate from the source table.

But is that clause really needed in ALL SQL SELECT commands?  The answer is yes and no!

Yes - if you are not familiar when it is needed, then to be safe put NOFILTER clause on every SQL SELECTs.

No - If you already knew (which I hope this post can assist you in learning) where it is needed or not as there are conditions where NOFILTER will be automatic, or to rephrase, a true cursor will be created even without us placing that clause and these are:

• If READWRITE clause is used
• If SQL SELECT involves two or more tables
• When GROUP BY clause is used
• When calculated columns are done like DISTINCT, TOP, PERCENT 
• When Aggregate functions are used such as AVG(), CNT(), MAX(), MIN() or SUM().
• When you change a field type into something else via CAST()
• When a result of SQL SELECT does not result to fully optimizable cursor (like when a new field is created with something like this):

Select *, .F. As NewField From MyTable Into Cursor junk

Mandatory NOFILTER

Here is another thing you need to remember.  When performing another SQL SELECT ... FROM (a cursor), then NOFILTER becomes mandatory on first cursor.

SELECT * FROM Table1 INTO CURSOR Cursor1 NOFILTER

Without the NOFILTER clause above, this 2nd SQL SELECT will fail:

SELECT * FROM Cursor1 INTO CURSOR Cursor2 NOFILTER

Closing Words:

We add NOFILTER clause to instruct VFP to create a true cursor or a cursor that is separate and different than the source table.   But the risk of having a filtered result only if records comes just from a single source table with no JOINs whatsoever.  The moment you combine fields from different tables, that risk goes away.  The moment you created something new to the existing fields like changing its field type via CAST(), then it goes away.  Or perform aggregate functions because that will create a new field as well.

However, since most of us needs a true cursor, then even when some of the conditions I mentioned above will automatically result to VFP creating a true cursor, if you are still  getting confused when it is needed or not, then just issue NOFILTER clause.