>Hi Tore,
>
>>Pardon me for jumping in. You wrote "so I had tried: WHERE DATE()-31". This >is a terrible solution, even if it would have worked, because VFP, or MS->SQL, would have to evaluate DATE()-31 for every record! Always use >variables in where clauses, whenever possible. Use ldDate=date()-31 >followed by .. where datefield=ldDate.
>
>Glad you jumped in ... I'm completely 'self-taught' and always welcome a correction to my education.
>
>Let me make sure I understand ...
>
>If I had used "WHERE DATE()-31" in the SQL statment ... the DB server would have to make that comparison for each record in the DB. But, if I store DATE()-31 into a variable, then SQL can skip the 'every record check' and immediately pull the records that contain the date stored in the variable (or, are within a date range provided by a couple of variables), correct?
>
>I have known for a very long time that you can't (shouldn't) create a view on a large SQL table without parameters, as every record will come accross the LAN to the workstation.

I take it one step further, never create a view without parameters. This increases the consistency of the construction. However, a parameterized view ends up with many parameters over time, leading to questions like I have a parameterized view where one of the parameters is a primary key or integer. I now want to ignore that primary key or integer.

The answer is often given as use BETWEEN lowvalue AND highvalue. This works, but isn't optimal.

The optimal answer is always build all necessary conditions. This leads to paranoia responses about SQL injection attacks. The final answer then is to always build ad-hoc parameterized SQL.

So if you want all records, you specifically tell the data source (whatever it is). If you want some records, you specifically tell the data source.

However, you can't do that with the view designer.


> Therefore, I've always use variables, rather than selecting the whole table. However, I didn't realize that there was such a huge difference in putting a conditon in a WHERE clause or storing the results of the condition in a variable and then using the variable in the WHERE clause. When I think about it, this makes alot of sense but I had just missed this piece of knowledge.
>

That's right!