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

Add a BTREE index on ITEM_KEYS

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

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

Thanks

View this thread on the forum...