Re: Index on collector tag collector unique
Thanks Ted, I just realized that If I set deleted off, I get the correct results, so the first record with collector 31 must be deleted. I will just use the SQL statement you suggested to make sure it's always correct. Thank you. From: Ted Roche <tedro...@gmail.com> To: "profox@leafe.com" <profox@leafe.com> Sent: Monday, November 28, 2016 3:46 PM Subject: Re: Index on collector tag collector unique Pretty weird. That's a bug. Or perhaps a corruption in the existing indexes. SELECT DISTINCT collector in MyTable ORDER BY collector INTO CURSOR YourCursor can give you the same thing, real quick if you already have an index tag on collector, and even if you don't. On Mon, Nov 28, 2016 at 3:30 PM, Michael Madigan <mmadi10...@yahoo.com> wrote: > Strange behavior I've never seen before > I have a 3 character field called collector. I have many records with > collector= '31' . When I index using the command "Index on collector tag > collector unique" I get every one except collector 31. I get collector 3, I > get collector 30, but I don't get collector 31. There are over 2000 records > with collector = '31' > > I realize you have to reindex every time to make sure you have every > collector accounted for, so it's not that > > This is really odd. > > --- StripMime Report -- processed MIME parts --- > multipart/alternative > text/plain (text body -- kept) > text/html > --- > [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/1894721230.1808438.1480368663...@mail.yahoo.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Index on collector tag collector unique
Pretty weird. That's a bug. Or perhaps a corruption in the existing indexes. SELECT DISTINCT collector in MyTable ORDER BY collector INTO CURSOR YourCursor can give you the same thing, real quick if you already have an index tag on collector, and even if you don't. On Mon, Nov 28, 2016 at 3:30 PM, Michael Madiganwrote: > Strange behavior I've never seen before > I have a 3 character field called collector.I have many records with > collector= '31' . When I index using the command "Index on collector tag > collector unique" I get every one except collector 31. I get collector 3, > I get collector 30, but I don't get collector 31. There are over 2000 > records with collector = '31' > > I realize you have to reindex every time to make sure you have every > collector accounted for, so it's not that > > This is really odd. > > --- StripMime Report -- processed MIME parts --- > multipart/alternative > text/plain (text body -- kept) > text/html > --- > [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/CACW6n4tjJ2scWrT9cavOh9a7Ngsqs=lk_ymv98cnkafjter...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: Index on collector tag collector unique
Michael Madigan wrote: Yep, you're right, I should have a collector database. This was a band-aid added quick and dirty. Turned out to be too dirty. Why not create it via a select distinct into a table and then just normalize your data? Probably a lot of work after you hit all your input forms as well as reports. Stephen Russell DBA / .Net Developer Memphis TN 38115 901.246-0159 Our scientific power has outrun our spiritual power. We have guided missiles and misguided men. Dr. Martin Luther King Jr. http://spaces.msn.com/members/srussell/ -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.432 / Virus Database: 268.16.14/636 - Release Date: 1/18/2007 4:00 AM ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: Index on collector tag collector unique
Unique indexes are bad and the behavior you are observing is how they are designed. Direct from HackFox: A so-called unique index contains a key only for the first record that has a particular key value. That is, once a key value occurs, no other records with that key value get added to the index. There's no mechanism to enforce uniqueness here, just a way to find one of each. However, unique indexes are not properly maintained. If you delete a record that's represented in the index, FoxPro does not add the next record in the table that has the same key value. Don't ever use unique indexesthere's always a better way to do it. Use candidate index tags to get and enforce uniqueness or be prepared to rebuild the index each time a record is deleted. Rick White Light Computing, Inc. www.whitelightcomputing.com www.rickschummer.com 586.254.2530 - office 586.254.2539 - fax -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Michael Madigan Sent: Wednesday, January 17, 2007 03:04 PM To: [EMAIL PROTECTED] Subject: Index on collector tag collector unique OK, so I have a client database and on that database I have a collector ID. I have a tag in the compound index that is created by index on collector tag collector unique. This gives me a list of unique collectors using the system, each one assigned to one or more clients. I have a collector 5. If I happen to delete the record which is in the unique index, there no longer are any more '5's in the index. Shouldn't it add another record with a collector='5' to that index? Am I understanding the way unique works? [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: Index on collector tag collector unique
Thanks. What do you mean by candidate? --- Rick Schummer [EMAIL PROTECTED] wrote: Unique indexes are bad and the behavior you are observing is how they are designed. Direct from HackFox: A so-called unique index contains a key only for the first record that has a particular key value. That is, once a key value occurs, no other records with that key value get added to the index. There's no mechanism to enforce uniqueness here, just a way to find one of each. However, unique indexes are not properly maintained. If you delete a record that's represented in the index, FoxPro does not add the next record in the table that has the same key value. Don't ever use unique indexesthere's always a better way to do it. Use candidate index tags to get and enforce uniqueness or be prepared to rebuild the index each time a record is deleted. Rick White Light Computing, Inc. www.whitelightcomputing.com www.rickschummer.com 586.254.2530 - office 586.254.2539 - fax -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Michael Madigan Sent: Wednesday, January 17, 2007 03:04 PM To: [EMAIL PROTECTED] Subject: Index on collector tag collector unique OK, so I have a client database and on that database I have a collector ID. I have a tag in the compound index that is created by index on collector tag collector unique. This gives me a list of unique collectors using the system, each one assigned to one or more clients. I have a collector 5. If I happen to delete the record which is in the unique index, there no longer are any more '5's in the index. Shouldn't it add another record with a collector='5' to that index? Am I understanding the way unique works? [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Index on collector tag collector unique
Michael Madigan wrote: Thanks. What do you mean by candidate? Same as a Primary index, meaning that the values in that field (combination) are all unique, i.e., without duplicates, but that it is not the primary index (because something else is generally). -- Michael J. Babcock, MCP MB Software Solutions, LLC http://mbsoftwaresolutions.com http://fabmate.com Work smarter, not harder, with MBSS custom software solutions! ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Index on collector tag collector unique
MB Software Solutions wrote: Michael Madigan wrote: Thanks. What do you mean by candidate? Same as a Primary index, meaning that the values in that field (combination) are all unique, i.e., without duplicates, but that it is not the primary index (because something else is generally). Let me add this: ...but a field that might be intelligible or human-understandable (like a Customer_ID), unlike a Primary Key field where it may simply be an autoincrementing field. -- Michael J. Babcock, MCP MB Software Solutions, LLC http://mbsoftwaresolutions.com http://fabmate.com Work smarter, not harder, with MBSS custom software solutions! ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: Index on collector tag collector unique
It is a type of index for VFP data, which enforces uniqueness and does not accept nulls (just like a primary key, but you can only have one primary key per table). Check out the topic Visual FoxPro Index Types in the VFP Help file. It clearly explains all the different index types (including a tip on how to avoid using UNIQUE indexes). Rick White Light Computing, Inc. www.whitelightcomputing.com www.rickschummer.com 586.254.2530 - office 586.254.2539 - fax -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Michael Madigan Sent: Wednesday, January 17, 2007 04:00 PM To: [EMAIL PROTECTED] Subject: RE: Index on collector tag collector unique Thanks. What do you mean by candidate? ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Index on collector tag collector unique
Michael Madigan wrote: OK, so I have a client database and on that database I have a collector ID. I have a tag in the compound index that is created by index on collector tag collector unique. This gives me a list of unique collectors using the system, each one assigned to one or more clients. I have a collector 5. If I happen to delete the record which is in the unique index, there no longer are any more '5's in the index. Shouldn't it add another record with a collector='5' to that index? Am I understanding the way unique works? Yes but I would follow some of the other posts here and use candidate instead. This does make me ask the question. Wouldn't you be better off having the collectors in their own parent table and just relate them into the customer table? then you could just use a query. select distinct customer.coll_id, collector.coll_name ; from customer ; left outer join collector on customer.coll_id == collector.col_id ; where customer.coll_id 0 you could also easily get a count for each collector too select distinct customer.coll_id, collector.coll_name, count(*) as coll_count ; from customer ; left outer join collector on customer.coll_id == collector.col_id ; where customer.coll_id 0 Of course normal indexing rules would apply to optimize the query... just a thought ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Index on collector tag collector unique
Yep, you're right, I should have a collector database. This was a band-aid added quick and dirty. Turned out to be too dirty. --- Eugene Vital [EMAIL PROTECTED] wrote: Michael Madigan wrote: OK, so I have a client database and on that database I have a collector ID. I have a tag in the compound index that is created by index on collector tag collector unique. This gives me a list of unique collectors using the system, each one assigned to one or more clients. I have a collector 5. If I happen to delete the record which is in the unique index, there no longer are any more '5's in the index. Shouldn't it add another record with a collector='5' to that index? Am I understanding the way unique works? Yes but I would follow some of the other posts here and use candidate instead. This does make me ask the question. Wouldn't you be better off having the collectors in their own parent table and just relate them into the customer table? then you could just use a query. select distinct customer.coll_id, collector.coll_name ; from customer ; left outer join collector on customer.coll_id == collector.col_id ; where customer.coll_id 0 you could also easily get a count for each collector too select distinct customer.coll_id, collector.coll_name, count(*) as coll_count ; from customer ; left outer join collector on customer.coll_id == collector.col_id ; where customer.coll_id 0 Of course normal indexing rules would apply to optimize the query... just a thought ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious. ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.