Mike Yearwood
Toronto, Ontario, Canada
General information
Category:
Coding, syntax & commands
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?
>
>Right! I have very limited experience with MS-SQL, but I see no reason why it should not behave the same way. Whenever you select ....where yourfield=yourvalue, yourvalue should always be a value or a memory variable, not a function.
>
>>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. 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.
>>
>>Thanks,
>>Robert
>
>I have very limited experience with MS-SQL, but I see no reason why it should not behave the same way. Whenever you select ....where yourfield=yourvalue, yourvalue should always be a value or a memory variable, not a function.
I don't know if you did it on purpose, but it's very funny that you told him twice. Just making sure it sinks in? :)
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only