Re: [U2] Multiple UniData Indexes in same select
On 08/11/12 00:47, dennis bartlett wrote: Once again (I'm a fan of these) the answer is to create a file of composite keys (FldA * FldB etc) and index that... The bummer is that the xref file will always be out unless one adds a 'trigger' to each 'to be indexed' file Are you SURE? Itypes can then be created to XLATE the data from the specific files. The index makes + the select quick, + the returned keys are in sequential FldA main sort, FldB nested, + there is a smaller subset of data to do direct reads to the two (or more files). You're assuming that the data is spread across multiple files. It's nicer if you create the files with a set prefix to define that all they are is xref files. The SELECTINDEX can be used. !!! Don't create an IType of the composite and index that... seriously bad idea (or an excellent way to get a continually random, lossy select) Yes, with multiple files. Seriously, I think you're answering the wrong question - iirc the OP wanted UD to combine multiple indexes on ONE file. Something it (and UV) never used to do. If all your data is in one file, and you get the corruption you're describing, I think all us users would be screaming blue murder at Rocket. Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Multiple UniData Indexes in same select
It will never use both. Usually the first index command possible will be used. Although I believe an EQ will override an index with GE. David A. Green (480) 813-1725 DAG Consulting -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Doug Farmer Sent: Wednesday, November 07, 2012 7:35 AM To: U2 Users List Subject: [U2] Multiple UniData Indexes in same select Good morning, I should know this, but as the gray gets thicker, some of the old knowledge seeps away. If you have indexes on multiple attributes on a file in UniData AND you include both of those indexed attributes in the same Select statement, how does UniData know which index to use? Does it use both? Neither? Thanks in advance, Doug This e-mail is for the use of the intended recipient(s) only. If you have received this e-mail in error, please notify the sender immediately and then delete it. If you are not the intended recipient, you must not use, disclose or distribute this e-mail without the author's prior permission. We have taken precautions to minimize the risk of transmitting software viruses, but we advise you to carry out your own virus checks on any attachment to this message. We cannot accept liability for any loss or damage caused by software viruses. Any views and/or opinions expressed in this e-mail are of the author only and do not represent the views of Epicor Software Corporation or any other company within its group. This message has been scanned for malware by Websense. www.websense.com ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Multiple UniData Indexes in same select
From the Using UniData manual: Alternate Indexes in UniQuery Statement Prior to UniData 5.2, only one index was used in the selection criteria of a UniQuery statement, even if the selection criteria contained more than one indexed attribute. UniQuery now uses all available indexes when processing a statement containing selection criteria.Note: You cannot use an alternate index with a LIKE clause when the comparative attribute is numeric. Wally Terhune Technical Support Engineer Rocket Software 4600 South Ulster Street, Suite 1100 **Denver, CO 80237 **USA t: +1 720 475 8055 **e: wterh...@rocketsoftware.com **w: u2.rocketsoftware.com -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Doug Farmer Sent: Wednesday, November 07, 2012 7:35 AM To: U2 Users List Subject: [U2] Multiple UniData Indexes in same select Good morning, I should know this, but as the gray gets thicker, some of the old knowledge seeps away. If you have indexes on multiple attributes on a file in UniData AND you include both of those indexed attributes in the same Select statement, how does UniData know which index to use? Does it use both? Neither? Thanks in advance, Doug This e-mail is for the use of the intended recipient(s) only. If you have received this e-mail in error, please notify the sender immediately and then delete it. If you are not the intended recipient, you must not use, disclose or distribute this e-mail without the author's prior permission. We have taken precautions to minimize the risk of transmitting software viruses, but we advise you to carry out your own virus checks on any attachment to this message. We cannot accept liability for any loss or damage caused by software viruses. Any views and/or opinions expressed in this e-mail are of the author only and do not represent the views of Epicor Software Corporation or any other company within its group. This message has been scanned for malware by Websense. www.websense.com ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Multiple UniData Indexes in same select
Excellent news. However, is there a way to utilize two indexes with the SELECTINDEX command in Unibasic? Sincerely, David Laansma IT Manager Hubbard Supply Co. Direct: 810-342-7143 Office: 810-234-8681 Fax: 810-234-6142 www.hubbardsupply.com Delivering Products, Services and Innovative Solutions -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wally Terhune Sent: Wednesday, November 07, 2012 11:47 AM To: U2 Users List Subject: Re: [U2] Multiple UniData Indexes in same select From the Using UniData manual: Alternate Indexes in UniQuery Statement Prior to UniData 5.2, only one index was used in the selection criteria of a UniQuery statement, even if the selection criteria contained more than one indexed attribute. UniQuery now uses all available indexes when processing a statement containing selection criteria.Note: You cannot use an alternate index with a LIKE clause when the comparative attribute is numeric. Wally Terhune Technical Support Engineer Rocket Software 4600 South Ulster Street, Suite 1100 **Denver, CO 80237 **USA t: +1 720 475 8055 **e: wterh...@rocketsoftware.com **w: u2.rocketsoftware.com -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Doug Farmer Sent: Wednesday, November 07, 2012 7:35 AM To: U2 Users List Subject: [U2] Multiple UniData Indexes in same select Good morning, I should know this, but as the gray gets thicker, some of the old knowledge seeps away. If you have indexes on multiple attributes on a file in UniData AND you include both of those indexed attributes in the same Select statement, how does UniData know which index to use? Does it use both? Neither? Thanks in advance, Doug This e-mail is for the use of the intended recipient(s) only. If you have received this e-mail in error, please notify the sender immediately and then delete it. If you are not the intended recipient, you must not use, disclose or distribute this e-mail without the author's prior permission. We have taken precautions to minimize the risk of transmitting software viruses, but we advise you to carry out your own virus checks on any attachment to this message. We cannot accept liability for any loss or damage caused by software viruses. Any views and/or opinions expressed in this e-mail are of the author only and do not represent the views of Epicor Software Corporation or any other company within its group. This message has been scanned for malware by Websense. www.websense.com ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Multiple UniData Indexes in same select
Thanks Wally. I was waiting for the official word. John Israel -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wally Terhune Sent: Wednesday, November 07, 2012 11:47 AM To: U2 Users List Subject: Re: [U2] Multiple UniData Indexes in same select From the Using UniData manual: Alternate Indexes in UniQuery Statement Prior to UniData 5.2, only one index was used in the selection criteria of a UniQuery statement, even if the selection criteria contained more than one indexed attribute. UniQuery now uses all available indexes when processing a statement containing selection criteria.Note: You cannot use an alternate index with a LIKE clause when the comparative attribute is numeric. Wally Terhune Technical Support Engineer Rocket Software 4600 South Ulster Street, Suite 1100 **Denver, CO 80237 **USA t: +1 720 475 8055 **e: wterh...@rocketsoftware.com **w: u2.rocketsoftware.com -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Doug Farmer Sent: Wednesday, November 07, 2012 7:35 AM To: U2 Users List Subject: [U2] Multiple UniData Indexes in same select Good morning, I should know this, but as the gray gets thicker, some of the old knowledge seeps away. If you have indexes on multiple attributes on a file in UniData AND you include both of those indexed attributes in the same Select statement, how does UniData know which index to use? Does it use both? Neither? Thanks in advance, Doug This e-mail is for the use of the intended recipient(s) only. If you have received this e-mail in error, please notify the sender immediately and then delete it. If you are not the intended recipient, you must not use, disclose or distribute this e-mail without the author's prior permission. We have taken precautions to minimize the risk of transmitting software viruses, but we advise you to carry out your own virus checks on any attachment to this message. We cannot accept liability for any loss or damage caused by software viruses. Any views and/or opinions expressed in this e-mail are of the author only and do not represent the views of Epicor Software Corporation or any other company within its group. This message has been scanned for malware by Websense. www.websense.com ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Multiple UniData Indexes in same select
No on UniBasic SELECTINDEX statement. Not sure I'd call a change made at UniData 5.2 in June of 2000 as 'news'. :-) Wally Terhune Technical Support Engineer Rocket Software 4600 South Ulster Street, Suite 1100 **Denver, CO 80237 **USA t: +1 720 475 8055 **e: wterh...@rocketsoftware.com **w: u2.rocketsoftware.com -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Dave Laansma Sent: Wednesday, November 07, 2012 9:54 AM To: U2 Users List Subject: Re: [U2] Multiple UniData Indexes in same select Excellent news. However, is there a way to utilize two indexes with the SELECTINDEX command in Unibasic? Sincerely, David Laansma IT Manager Hubbard Supply Co. Direct: 810-342-7143 Office: 810-234-8681 Fax: 810-234-6142 www.hubbardsupply.com Delivering Products, Services and Innovative Solutions -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wally Terhune Sent: Wednesday, November 07, 2012 11:47 AM To: U2 Users List Subject: Re: [U2] Multiple UniData Indexes in same select From the Using UniData manual: Alternate Indexes in UniQuery Statement Prior to UniData 5.2, only one index was used in the selection criteria of a UniQuery statement, even if the selection criteria contained more than one indexed attribute. UniQuery now uses all available indexes when processing a statement containing selection criteria.Note: You cannot use an alternate index with a LIKE clause when the comparative attribute is numeric. Wally Terhune Technical Support Engineer Rocket Software 4600 South Ulster Street, Suite 1100 **Denver, CO 80237 **USA t: +1 720 475 8055 **e: wterh...@rocketsoftware.com **w: u2.rocketsoftware.com -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Doug Farmer Sent: Wednesday, November 07, 2012 7:35 AM To: U2 Users List Subject: [U2] Multiple UniData Indexes in same select Good morning, I should know this, but as the gray gets thicker, some of the old knowledge seeps away. If you have indexes on multiple attributes on a file in UniData AND you include both of those indexed attributes in the same Select statement, how does UniData know which index to use? Does it use both? Neither? Thanks in advance, Doug This e-mail is for the use of the intended recipient(s) only. If you have received this e-mail in error, please notify the sender immediately and then delete it. If you are not the intended recipient, you must not use, disclose or distribute this e-mail without the author's prior permission. We have taken precautions to minimize the risk of transmitting software viruses, but we advise you to carry out your own virus checks on any attachment to this message. We cannot accept liability for any loss or damage caused by software viruses. Any views and/or opinions expressed in this e-mail are of the author only and do not represent the views of Epicor Software Corporation or any other company within its group. This message has been scanned for malware by Websense. www.websense.com ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Multiple UniData Indexes in same select
The first one -Original Message- From: Doug Farmer dfar...@epicor.com To: U2 Users List u2-users@listserver.u2ug.org Sent: Wed, Nov 7, 2012 6:35 am Subject: [U2] Multiple UniData Indexes in same select Good morning, I should know this, but as the gray gets thicker, some of the old knowledge seeps away. If you have indexes on multiple attributes on a file in UniData AND you include both of those indexed attributes in the same Select statement, how does UniData know which index to use? Does it use both? Neither? Thanks in advance, Doug This e-mail is for the use of the intended recipient(s) only. If you have received this e-mail in error, please notify the sender immediately and then delete it. If you are not the intended recipient, you must not use, disclose or distribute this e-mail without the author's prior permission. We have taken precautions to minimize the risk of transmitting software viruses, but we advise you to carry out your own virus checks on any attachment to this message. We cannot accept liability for any loss or damage caused by software viruses. Any views and/or opinions expressed in this e-mail are of the author only and do not represent the views of Epicor Software Corporation or any other company within its group. This message has been scanned for malware by Websense. www.websense.com ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Multiple UniData Indexes in same select
What about Universe? -Original Message- From: Wally Terhune wterh...@rocketsoftware.com To: U2 Users List u2-users@listserver.u2ug.org Sent: Wed, Nov 7, 2012 9:05 am Subject: Re: [U2] Multiple UniData Indexes in same select No on UniBasic SELECTINDEX statement. Not sure I'd call a change made at UniData 5.2 in June of 2000 as 'news'. :-) Wally Terhune Technical Support Engineer Rocket Software 4600 South Ulster Street, Suite 1100 **Denver, CO 80237 **USA t: +1 720 475 8055 **e: wterh...@rocketsoftware.com **w: u2.rocketsoftware.com -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Dave Laansma Sent: Wednesday, November 07, 2012 9:54 AM To: U2 Users List Subject: Re: [U2] Multiple UniData Indexes in same select Excellent news. However, is there a way to utilize two indexes with the SELECTINDEX command in Unibasic? Sincerely, David Laansma IT Manager Hubbard Supply Co. Direct: 810-342-7143 Office: 810-234-8681 Fax: 810-234-6142 www.hubbardsupply.com Delivering Products, Services and Innovative Solutions -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wally Terhune Sent: Wednesday, November 07, 2012 11:47 AM To: U2 Users List Subject: Re: [U2] Multiple UniData Indexes in same select From the Using UniData manual: Alternate Indexes in UniQuery Statement Prior to UniData 5.2, only one index was used in the selection criteria of a UniQuery statement, even if the selection criteria contained more than one indexed attribute. UniQuery now uses all available indexes when processing a statement containing selection criteria.Note: You cannot use an alternate index with a LIKE clause when the comparative attribute is numeric. Wally Terhune Technical Support Engineer Rocket Software 4600 South Ulster Street, Suite 1100 **Denver, CO 80237 **USA t: +1 720 475 8055 **e: wterh...@rocketsoftware.com **w: u2.rocketsoftware.com -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Doug Farmer Sent: Wednesday, November 07, 2012 7:35 AM To: U2 Users List Subject: [U2] Multiple UniData Indexes in same select Good morning, I should know this, but as the gray gets thicker, some of the old knowledge seeps away. If you have indexes on multiple attributes on a file in UniData AND you include both of those indexed attributes in the same Select statement, how does UniData know which index to use? Does it use both? Neither? Thanks in advance, Doug This e-mail is for the use of the intended recipient(s) only. If you have received this e-mail in error, please notify the sender immediately and then delete it. If you are not the intended recipient, you must not use, disclose or distribute this e-mail without the author's prior permission. We have taken precautions to minimize the risk of transmitting software viruses, but we advise you to carry out your own virus checks on any attachment to this message. We cannot accept liability for any loss or damage caused by software viruses. Any views and/or opinions expressed in this e-mail are of the author only and do not represent the views of Epicor Software Corporation or any other company within its group. This message has been scanned for malware by Websense. www.websense.com ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Multiple UniData Indexes in same select
Appreciate the answer! I was going to guess it only used the first one. Doug -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wally Terhune Sent: Wednesday, November 07, 2012 11:05 AM To: U2 Users List Subject: Re: [U2] Multiple UniData Indexes in same select No on UniBasic SELECTINDEX statement. Not sure I'd call a change made at UniData 5.2 in June of 2000 as 'news'. :-) Wally Terhune Technical Support Engineer Rocket Software 4600 South Ulster Street, Suite 1100 **Denver, CO 80237 **USA t: +1 720 475 8055 **e: wterh...@rocketsoftware.com **w: u2.rocketsoftware.com -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Dave Laansma Sent: Wednesday, November 07, 2012 9:54 AM To: U2 Users List Subject: Re: [U2] Multiple UniData Indexes in same select Excellent news. However, is there a way to utilize two indexes with the SELECTINDEX command in Unibasic? Sincerely, David Laansma IT Manager Hubbard Supply Co. Direct: 810-342-7143 Office: 810-234-8681 Fax: 810-234-6142 www.hubbardsupply.com Delivering Products, Services and Innovative Solutions -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wally Terhune Sent: Wednesday, November 07, 2012 11:47 AM To: U2 Users List Subject: Re: [U2] Multiple UniData Indexes in same select From the Using UniData manual: Alternate Indexes in UniQuery Statement Prior to UniData 5.2, only one index was used in the selection criteria of a UniQuery statement, even if the selection criteria contained more than one indexed attribute. UniQuery now uses all available indexes when processing a statement containing selection criteria.Note: You cannot use an alternate index with a LIKE clause when the comparative attribute is numeric. Wally Terhune Technical Support Engineer Rocket Software 4600 South Ulster Street, Suite 1100 **Denver, CO 80237 **USA t: +1 720 475 8055 **e: wterh...@rocketsoftware.com **w: u2.rocketsoftware.com -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Doug Farmer Sent: Wednesday, November 07, 2012 7:35 AM To: U2 Users List Subject: [U2] Multiple UniData Indexes in same select Good morning, I should know this, but as the gray gets thicker, some of the old knowledge seeps away. If you have indexes on multiple attributes on a file in UniData AND you include both of those indexed attributes in the same Select statement, how does UniData know which index to use? Does it use both? Neither? Thanks in advance, Doug This e-mail is for the use of the intended recipient(s) only. If you have received this e-mail in error, please notify the sender immediately and then delete it. If you are not the intended recipient, you must not use, disclose or distribute this e-mail without the author's prior permission. We have taken precautions to minimize the risk of transmitting software viruses, but we advise you to carry out your own virus checks on any attachment to this message. We cannot accept liability for any loss or damage caused by software viruses. Any views and/or opinions expressed in this e-mail are of the author only and do not represent the views of Epicor Software Corporation or any other company within its group. This message has been scanned for malware by Websense. www.websense.com ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users Click https://www.mailcontrol.com/sr/ggi6XfpTfmPGX2PQPOmvUmET9b5tVcUOeAv!B!MNW r18fpuHNY5rzXyEfMBpW7rnUENuAmfNZOrX405xsHCs!A== to report this email as spam. This e-mail is for the use of the intended recipient(s) only. If you have received this e-mail in error, please notify the sender immediately and then delete it. If you are not the intended recipient, you must not use, disclose or distribute this e-mail without the author's prior permission. We have taken precautions to minimize the risk of transmitting software viruses, but we advise you to carry out your own virus checks on any attachment to this message. We cannot accept liability for any loss or damage caused by software viruses. Any views and/or opinions expressed
Re: [U2] Multiple UniData Indexes in same select
On 07/11/12 17:18, Wjhonson wrote: What about Universe? imho, it just doesn't make logical sense in BASIC! just do SELECTINDEX, READLIST, SELECTINDEX, READLIST and then manipulate it yourself. Okay, that's more hassle, but IME the results of reading the index file always give me a sorted list, so doing a union or intersect of two sorted lists isn't that tricky. Cheers, Wol -Original Message- From: Wally Terhune wterh...@rocketsoftware.com To: U2 Users List u2-users@listserver.u2ug.org Sent: Wed, Nov 7, 2012 9:05 am Subject: Re: [U2] Multiple UniData Indexes in same select No on UniBasic SELECTINDEX statement. Not sure I'd call a change made at UniData 5.2 in June of 2000 as 'news'. :-) Wally Terhune Technical Support Engineer Rocket Software 4600 South Ulster Street, Suite 1100 **Denver, CO 80237 **USA t: +1 720 475 8055 **e: wterh...@rocketsoftware.com **w: u2.rocketsoftware.com -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Dave Laansma Sent: Wednesday, November 07, 2012 9:54 AM To: U2 Users List Subject: Re: [U2] Multiple UniData Indexes in same select Excellent news. However, is there a way to utilize two indexes with the SELECTINDEX command in Unibasic? Sincerely, David Laansma IT Manager Hubbard Supply Co. Direct: 810-342-7143 Office: 810-234-8681 Fax: 810-234-6142 www.hubbardsupply.com Delivering Products, Services and Innovative Solutions -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wally Terhune Sent: Wednesday, November 07, 2012 11:47 AM To: U2 Users List Subject: Re: [U2] Multiple UniData Indexes in same select From the Using UniData manual: Alternate Indexes in UniQuery Statement Prior to UniData 5.2, only one index was used in the selection criteria of a UniQuery statement, even if the selection criteria contained more than one indexed attribute. UniQuery now uses all available indexes when processing a statement containing selection criteria.Note: You cannot use an alternate index with a LIKE clause when the comparative attribute is numeric. Wally Terhune Technical Support Engineer Rocket Software 4600 South Ulster Street, Suite 1100 **Denver, CO 80237 **USA t: +1 720 475 8055 **e: wterh...@rocketsoftware.com **w: u2.rocketsoftware.com -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Doug Farmer Sent: Wednesday, November 07, 2012 7:35 AM To: U2 Users List Subject: [U2] Multiple UniData Indexes in same select Good morning, I should know this, but as the gray gets thicker, some of the old knowledge seeps away. If you have indexes on multiple attributes on a file in UniData AND you include both of those indexed attributes in the same Select statement, how does UniData know which index to use? Does it use both? Neither? Thanks in advance, Doug ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Multiple UniData Indexes in same select
In Universe I have used the EXPLAIN keyword in the past to understand how the query will be processed. You just add the keyword EXPLAIN to the end of your query statement. The documentation says it for SQL statements but it seems to work on Universe SELECT statements. Showing How a Query Will Be Processed (EXPLAIN) Use EXPLAIN in a SELECT statement to display information about how the statement will be processed, so that you can decide if you want to rewrite the query more efficiently. You can also use EXPLAIN in an INSERT, UPDATE, or DELETE statement, whenever it contains a WHERE clause or a query specification. EXPLAIN lists the tables included in the query or WHERE clause, explains how data will be retrieved (that is, by table, select list, index lookup, or explicit ID), and explains how any joins will be processed. After each message, press Q to quit, or press any other key to continue the query. If a client program uses EXPLAIN in a SELECT statement, the statement is not processed. Instead, an SQLSTATE value of IA000 is returned, along with the EXPLAIN message as the message text. Regards Adrian Halid -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: Thursday, 8 November 2012 3:00 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Multiple UniData Indexes in same select On 07/11/12 17:18, Wjhonson wrote: What about Universe? imho, it just doesn't make logical sense in BASIC! just do SELECTINDEX, READLIST, SELECTINDEX, READLIST and then manipulate it yourself. Okay, that's more hassle, but IME the results of reading the index file always give me a sorted list, so doing a union or intersect of two sorted lists isn't that tricky. Cheers, Wol -Original Message- From: Wally Terhune wterh...@rocketsoftware.com To: U2 Users List u2-users@listserver.u2ug.org Sent: Wed, Nov 7, 2012 9:05 am Subject: Re: [U2] Multiple UniData Indexes in same select No on UniBasic SELECTINDEX statement. Not sure I'd call a change made at UniData 5.2 in June of 2000 as 'news'. :-) Wally Terhune Technical Support Engineer Rocket Software 4600 South Ulster Street, Suite 1100 **Denver, CO 80237 **USA t: +1 720 475 8055 **e: wterh...@rocketsoftware.com **w: u2.rocketsoftware.com -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Dave Laansma Sent: Wednesday, November 07, 2012 9:54 AM To: U2 Users List Subject: Re: [U2] Multiple UniData Indexes in same select Excellent news. However, is there a way to utilize two indexes with the SELECTINDEX command in Unibasic? Sincerely, David Laansma IT Manager Hubbard Supply Co. Direct: 810-342-7143 Office: 810-234-8681 Fax: 810-234-6142 www.hubbardsupply.com Delivering Products, Services and Innovative Solutions -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wally Terhune Sent: Wednesday, November 07, 2012 11:47 AM To: U2 Users List Subject: Re: [U2] Multiple UniData Indexes in same select From the Using UniData manual: Alternate Indexes in UniQuery Statement Prior to UniData 5.2, only one index was used in the selection criteria of a UniQuery statement, even if the selection criteria contained more than one indexed attribute. UniQuery now uses all available indexes when processing a statement containing selection criteria.Note: You cannot use an alternate index with a LIKE clause when the comparative attribute is numeric. Wally Terhune Technical Support Engineer Rocket Software 4600 South Ulster Street, Suite 1100 **Denver, CO 80237 **USA t: +1 720 475 8055 **e: wterh...@rocketsoftware.com **w: u2.rocketsoftware.com -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Doug Farmer Sent: Wednesday, November 07, 2012 7:35 AM To: U2 Users List Subject: [U2] Multiple UniData Indexes in same select Good morning, I should know this, but as the gray gets thicker, some of the old knowledge seeps away. If you have indexes on multiple attributes on a file in UniData AND you include both of those indexed attributes in the same Select statement, how does UniData know which index to use? Does it use both? Neither? Thanks in advance, Doug ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Multiple UniData Indexes in same select
Once again (I'm a fan of these) the answer is to create a file of composite keys (FldA * FldB etc) and index that... The bummer is that the xref file will always be out unless one adds a 'trigger' to each 'to be indexed' file Itypes can then be created to XLATE the data from the specific files. The index makes + the select quick, + the returned keys are in sequential FldA main sort, FldB nested, + there is a smaller subset of data to do direct reads to the two (or more files). It's nicer if you create the files with a set prefix to define that all they are is xref files. The SELECTINDEX can be used. !!! Don't create an IType of the composite and index that... seriously bad idea (or an excellent way to get a continually random, lossy select) On 8 November 2012 02:54, Dave Laansma dlaan...@hubbardsupply.com wrote: Excellent news. However, is there a way to utilize two indexes with the SELECTINDEX command in Unibasic? ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
