Index lookup and Multi-values (OpenInsight 32-bit Specific) [Revelation On-Line Wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
[[https://www.revelation.com/|Sign up on the Revelation Software website to have access to the most current content, and to be able to ask questions and get answers from the Revelation community]]


==== Index lookup and Multi-values (OpenInsight 32-bit Specific) ====
=== At 19 SEP 2006 06:14:19PM paxton scott wrote: ===
 
{{tag>"OpenInsight 32-bit Specific"}}
 
<QUOTE>
Greetings!



If I have a MV field with a set of integers how do I select the records where a particular integer is contained in the mv field?



Say each record has a multi-valued field  ITEM_KEYS and ITEM_KEYS contains a number of keys.  I want all the records that have 35 as one of the values in ITEM_KEYS.



What is the best way to do this?



Paxton
</QUOTE>
----

=== At 19 SEP 2006 06:40PM Warren Auyong wrote:  ===

<QUOTE>Add a BTREE index on ITEM_KEYS

</QUOTE>

----

=== At 20 SEP 2006 07:49AM paxtpn scott wrote:  ===

<QUOTE>Whoops!  I guess I forgot to say that ITEM_KEYS has a btree index.



The index seems to apply across the whole field, not the individual values.  



so "LIST LOCKER WITH TITLE_IDS EQ 116" will return nothing if there are several values in TITLE_IDS.



"LIST LOCKER TITLE_IDS  LIMIT TITLE_IDS 116",1 will display what I want, but



stmt=SELECT LOCKER TITLE_IDS  LIMIT TITLE_IDS ":titlekey

Rlist(stmt,5,'','','')



creates a select list of all records



stmt=SELECT LOCKER WITH TITLE_IDS CONTAINING ":titlekey

sort of works, but the record containing 2116 is also selected.



My solution now is to  "SELECT LOCKER WITH TITLE_IDS CONTAINING ":titlekey and the use a locate to verify that the selected record really has the wanted key in TITLE_IDS.



Seems a little clumsy, thought there must be a better way.

Thanks,  Paxton

</QUOTE>

----

=== At 20 SEP 2006 12:44PM Warren Auyong wrote:  ===

<QUOTE>Is TITLE_IDS btree indexed in your example?



In v7.2.1 I created a file TEST



 AKey

 MV (multivalued)

 XX (singlevalued)



Added BTREE to MV



Record A

 1

 2

 3

 test



Record B

 1

 2

 something



Record C

 1

 morestuff



Results as expected:



clearselect

rlist('SELECT TEST WITH MV EQ 1',5,'','','') returns @RECCOUNT=3

clearselect

rlist('SELECT TEST WITH MV EQ 2',5,'','','') returns @RECCOUNT=2

clearselect

rlist('SELECT TEST WITH MV EQ 3',5,'','','') returns @RECCOUNT=1

</QUOTE>

----

=== At 20 SEP 2006 03:59PM paxton scott wrote:  ===

<QUOTE>I wrote the test system to prove to myself..works great!

Thanks

</QUOTE>


[[https://www.revelation.com/revweb/oecgi4p.php/O4W_HANDOFF?DESTN=O4W_RUN_FORM&INQID=NONWORKS_READ&SUMMARY=1&KEY=BB45B75FA03BDAC8852571EE007A2967|View this thread on the  forum...]]