How can I use Rlist or Btree.Exact to retrieve record

slowjams

Let's say I have two indexed column "Keyword"(it is a @vm multi-value column) and "Status"(Cancelled, Sold, Pending etc)
And I want to search records that has keyword of "Blanket" OR "Coffee" with status not being Cancelled AND Sold, so what's the best approach to do the search? I've tried to use btree, but it was very slow and can only filter status once not twice.

DonBakke

I've tried to use btree, but it was very slow and can only filter status once not twice.

Does "btree" in this sentence mean Btree.Extract? If so, please post your code so we can help you. You can use Btree.Extract to search against multiple indexed columns as per the description of the routine:

Searches one or more Btree indexes for data matching the search criteria passed in. Returns the keys to rows having matching data.

AusMarkB

For one pass through I would think the btree.extract search criteria would look something like

"KEYWORD":@vm:"Blanket":@vm:"Coffee":@fm:"STATUS":@vm:"#Cancelled":@vm:"#Sold":@fm

Note the keyword field is actually an "AND". It gives you everything that is "Blanket" AND everything that is "Coffee".


As for the "Status" field, I'm a little unsure and think it might be an "OR" but without testing I can't think of how to put Not This Or This

BarryStevens

Mark,
would it be:
srch_strng="KEYWORD":@vm:"Blanket":@vm:"Coffee"
srch_strng:=@fm:"STATUS":@vm:"#Cancelled"
srch_strng:=@fm:"STATUS":@vm:"#Sold":@fm

i.e
with (keyword equal Blanket or Coffee) and (with status not equal cancelled and with status not equal Sold)

slowjams

@BarryStevens @AusMarkB
I need to use Rlist, so how can Rlist search a matched multivalue columns?
SELECT tableNAme WITH KEYWORD ??? Blanket/Coffee

AusMarkB

'SELECT tablename WITH KEYWORD = "Blanket" "Coffee"'

It should return the records you're looking for if either of those keywords are any of the mv fields.

As a side note, I tend to stack my select statements when there is multiple criteria rather than have one long single select statement.

So something like this

ClearSelect theSelect = 'SELECT tablename WITH KEYWORD = "Blanket" "Coffee"' Call Rlist(theselect, 5, "", "", "") theSelect = 'SELECT tablename WITH STATUS NE "Cancelled" AND WITH STATUS NE "Sold"' Call Rlist(theselect, 5, "", "", "")

You can choose what is likely to be the most efficient sequence but in this example, you firstly retrieve all records that match either blanket or coffee and then the second select does a subselect on those results. I find this makes it easier to define the proper select statement but also, depending on the number of variables can be quicker than having OI try and process just one big select. This way you have more control over what gets processed first.

BarryStevens

SELECT tableNAme WITH ( KEYWORD EQ ‘Blanket’ OR WITH KEYWORD EQ ‘Coffee’ ) AND ( WITH STATUS NE ‘Cancelled’ AND WITH STATUS NE ‘Sold’ )

slowjams

@BarryStevens @AusMarkB
The problem is that KEYWORD is multi-value column, if you use
KEYWORD EQ ‘Blanket’ OR WITH KEYWORD EQ ‘Coffee’, it will only select the records which have only ONE value which is Blanket or Coffee, it won't select the records whose keyword value is 'Toy':@vm:'Blanket '

AusMarkB

Have you tested that?

from the TCL try LIST tablename WITH KEYWORD = "Coffee" OR WITH KEYWORD = "Blanket" KEYWORD and see what comes up.
I'm pretty sure you'll get all records that have either regardless of whatever else is in the other vm positions.
At least that's what I get with a comparable statement

AusMarkB

Here's a similar example I just tested
Supplier_code is multivalue in this table

LIST WAREHOUSE_DETAILS WITH SUPPLIER_CODE = 8159 OR WITH SUPPLIER_CODE = 104 SUPPLIER_CODE

BarryStevens

WITH KEYWORD = "Coffee" AND WITH KEYWORD = "Blanket"

at the beginning you said:
And I want to search records that has keyword of "Blanket" OR "Coffee"

>> it won't select the records whose keyword value is 'Toy':@vm:'Blanket '
Of course not, because you are not asking for them.

If you want all but not Coffee and Blanket then:

( WITH KEYWORD NE "Coffee" AND WITH KEYWORD NE "Blanket")

AusMarkB

@BarryStevens, I believe what he is saying is that he does want those with keyword 'Toy':@vm:'Blanket' because it contains 'Blanket' in which case the example remains.

@slowjams, FWIW my example above would produce the same output if I'd written it like this

LIST WAREHOUSE_DETAILS WITH SUPPLIER_CODE = "8159" "104" SUPPLIER_CODE

Just thought I'd add that for clarification as that's how I suggested in the earlier post.
So going back to your specific requirements, these two will produce the same result and both being what I believe you're after

SELECT tablename WITH KEYWORD = "Blanket" "Coffee"or
SELECT tablename WITH KEYWORD = "Blanket" OR WITH KEYWORD = "Coffee"

BarryStevens

@AusMarkB Yep, I see what you mean. I miss read it.
I think he must be making assumptions about it not working without realising that each value in the mv field is extracted and indexed as oppose to a string of "'Toy':@vm:'Blanket'" as the index.
Now I will butt out.!