SQL Select problem 1

[chpicker]

Ok...for the first month or so that I started using SQL Select statements, I thought they were pretty cool. You end up with a small table of just the items you want.

However, for some strange reason, occasionally it will behave rather odd. I issue this statement:

SELECT TimeField,Addy1,Addy2 FROM (MyTable) WHERE ;
     CustNum=m.CustNum and DateField=DATE() ;
     INTO CURSOR (TempCur)

Instead of my nice little table that contains a handful of records, what I seem to end up with is a filtered version of the original table. The current/total record on the status bar reflect the original table. The name shown on the bar is "TempCur(Database!MyTable)" instead of just "TempCur". If I set a grid recordsource to "TempCur", even though there is only 1 record in the query, the scrollbar is tiny, indicating that there are 98000 records. If I click the UP arrow on the grid...it locks VFP up for about 15 seconds while it scans for more records to display.

Does anyone know if there is some kind of setting that causes SQL-SELECT to do this? It doesn't always happen...just sometimes.

 

[rgbean]

Add the optional NOFILTER after your cursor name to force the creation of a real cursor, rather than a filtered table.
i.e.
SELECT TimeField,Addy1,Addy2 FROM (MyTable) WHERE ;
CustNum=m.CustNum and DateField=DATE() ;
INTO CURSOR (TempCur) NOFILTER


Rick

 

[chpicker]

Thanks, Rick...that gives me a workaround for it.

But can you tell me how FoxPro determines which to use? I never ran into this situation until this week, it always worked properly for me before. And if I execute the command (without NOFILTER) in the Command Window in a fresh VFP session, it works properly as well. It's just in my project that it forms a filtered version of the original table (which completely defeats the purpose of using the SQL command in the first place instead of simply applying a filter to the original cursor).

 

[rgbean]

I have no personal knowledge, but often rely on the "Hacker's Guide to Visual FoxPro". The following is a quote from there:
"Another VFP 5 addition is the NOFILTER clause for INTO CURSOR. In order to do things as fast as it can, FoxPro pulls a little trick. If a query involves only a single table, has no calculated fields, is fully optimizable, and is sent to a cursor, rather than going to the trouble of creating a whole new cursor, FoxPro simply filters the original table. Often, that's good enough for whatever you have in mind. However, in some situations, especially those where the query result is then used in a subsequent query or where you want to index the cursor, having only a filtered view of the original causes problems. In VFP 3 and earlier versions, you worked around this by putting something in the query that outwitted FoxPro. Starting in VFP 5, it's a lot easier—just add NOFILTER to the query and FoxPro always creates a real cursor. (Doesn't "real cursor" sound like an oxymoron?)"

Rick

 

[chpicker]

Aha...thanks, Rick, that tells me EXACTLY what I needed to know!

If a query involves only a single table, has no calculated fields, is fully optimizable, and is sent to a cursor

This is the link I was looking for! )