Hi Doug, Great explanation and I appreciate the time taken to reply to the issue. On the display only form I have two table fields. The first looks for open records assigned to me and the second looks for open records not assigned to me. This is why I am using the != Mark clause.
Thanks for the advice. Mark From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Mueller, Doug Sent: Monday, March 14, 2011 3:07 PM To: arslist@ARSLIST.ORG Subject: Re: Question about NULL ** Mark, First, about why your search is not working as you think it should have. NULL is a strange beast in databases. It is a value. A very special value that means there is no value. The only way to find things that have this special "no value value" is to explicitly look for it. No value does not match a != comparison. This is why you have to explicitly check for it in your qualification to find things that are unassigned. Now, about indexing. It actually is not the NULL test that is causing the table scan. If you have a field indexed and you search for NULL, you will use the index. The thing that is causing the table scan is the != "Mark Brittain" clause. A != will cause the system to perform a table scan as there is nothing to index on. To find all the unassigned records using the index, just use the clause 'Assigned Person' = $NULL$ This will use the index on Assigned Person and it will find all unassigned records. I am not sure what the != "Mark Brittain" clause is for if you are looking for unassigned records. That will find ASSIGNED records that are not assigned to Mark. So, that search is really looking for all records not assigned to anyone (the NULL clause) and those not assigned to Mark. If you can remove the != Mark from your criteria, you will find things much more efficient. Now, all this is assuming you don't qualify on Status too. If there is an index on Status, it should help with the search and that index can be used. However, the algorithm for determining whether to use an index may be determining that there is a large percentage match in the table so it defaults to a table scan rather than trying to use the index on Status. I hope this helps, Doug Mueller ________________________________ From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Brittain, Mark Sent: Monday, March 14, 2011 11:06 AM To: arslist@ARSLIST.ORG Subject: Question about NULL ** HI All, I have a table that uses the following query which searches for any open record that is not assigned to me. 'Status' < "Closed" AND ('Assigned Person' != "Mark Brittain" OR 'Assigned Person' = NULL) If I use the query without the Assigned Person' = NULL the result does not include any records that are not assigned. Both fields are indexed but the NULL causes the full table scan. Is there a way to write query so that indexing is used but I get the records that are unassigned? Thanks Mark ____________________________________________ Mark Brittain Remedy Developer NaviSite mbritt...@navisite.com<mailto:mbritt...@navisite.com> (315) 453-2912 x5335 (Office) (315) 317.2897 (Cell) ________________________________ This e-mail is the property of NaviSite, Inc. It is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential, or otherwise protected from disclosure. Distribution or copying of this e-mail, or the information contained herein, to anyone other than the intended recipient is prohibited. _attend WWRUG11 www.wwrug.com ARSlist: "Where the Answers Are"_ _attend WWRUG11 www.wwrug.com ARSlist: "Where the Answers Are"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
