General information
Category:
Coding, syntax & commands
Rick,
I have interspersed my answers to your questions/comments. . .
>Hi,
>
>Thanks all for your suggestions, I am learning heaps if nothing else. To many Auzy programmers have to big an ego to share their knowledge. I thank you for being open with yours....
>
>Now that I have sucked up ha ha, can I ask you this.
>
>How do you create an index with a filter on a BETWEEN() function?
>Would this not be hard coded, e.g how could I replace the range options with dynamic values.
>I do have an index on every field so i do not understand what you mean by saying you doubt if i have indices on my BETWEEN and INLIST option.
RIght - I don't know how to create an index on a "dynamic" BETWEEN either. That's why I simply pointed out that you say you have indexes on everything, but you don't really.
>
>The fiedls used are TAGs etc.. on there own. I have not used Functions to TRIM the indices, should I?
>
This is the main problem (if I read you correctly)! *IF* you are going to use a funxction in your WHERE clause, then that EXACT SAME function *must* be coded in the index too FOR RUSHMORE TO BE EFFECTIVE. In other words, as I tried to say before, your index expression must look identical to your WHERE clause in terms of structure.
>I am using SET KEYS. e.g. SET ORDER TO IDXDefault where
>IDXDefault = INDEX ON Suburb+street+regions TAG IDXDefault, then
>SET KEY TO Var1+Var2+Var3 where
>Var1=Suburb
>Var2=Street
>Var3=Regions for example.
>
>This is quick on a single table, I'm not sure how this would go on multiple JOINED tables. It's also no good for a SELECT statement. Because SELE don't honour the filter from the key espression.
>
>Can you give me an example of how you would create indices for the above criteria and how you would use them in a select taking advantage of Rushmore.
>
The index above would be used with a Where clause along the lines of:
. . . Where a.suburb+a.street+a.regions == m.suburb+m.street+m.regions. . .
- That's what is meant by having the expressions *MATCH* the index expression.
>Oh Yeah! I looked up help for between(), found nothing interesting. It just gives you syntax help only. the tastraders and solutions are a little underdone for my liking too.
>
What I said was that SQL has a different format for the BETWEEN clause, which is only documented in the Examples under Select-SQL. Look there to find it and copy its format in your code.
>Look forward to your reply.
>TIA
>Rick
The most fundamental thing that I could recommend to you is to read CAREFULLY - both posts and Documentation. From your questions you did *NOT* seem to catch what I wrote the first time (and I think you should have).
Good luck,
Jim N
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