Friday, September 23, 2011

NOFILTER Clause on an SQL SELECT, when to use?

I believe usage of this clause has baffled a lot of users of VFP like the usage of mdot so I will try to explain things based on my perspective.

When is it needed?  The basic answer is if you are performing an SQL SELECT ... FROM  .. a single table (no joins whatsoever and no extra new field).  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 one.

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

I cannot see yet a need for a filtered result only on my end and would always prefer a true cursor and by using NOFILTER clause, that will ensure it.

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, it is still better to just simply add that clause at the end.  Like I said, I on my end never needed a filtered result.  When I want a cursor, I want a true cursor.  NOFILTER ensures to generate that 100%.

No comments:

Post a Comment