I have tried to use Exec_SQL( "SELECT COUNT(*) FROM … WHERE …=…" ) from a function or from the Sys Ed Exec line to check how many rows in a certain table has a certain value X in one of its fields, but I get strange results.

(1)

In my first experiment the field was multivalued. I had one row where the field contained one occurrence of X and nothing else, and one row where the field contained two occurrences of X and nothing else.

If I put a BTree index on the field, SELECT COUNT(*) returned 3, which I assume is correct because SQL on LH tables will sort of make several rows out of one if the row contains multivalued fields with several values.

If I removed the BTree index on the field, however, SELECT COUNT(*) returned 1! Maybe it now didn't know the field was multivalued so it couldn't find the second row, which had the value X:@VM:X??? Is this really the expected behaviour?

Since this experiment might be to limited to draw conclusions from, I tried a different one, on a different table:

(2)

Again the field was multivalued. But now I tried to count all occurrences smaller than a certain value: "SELECT COUNT(*) FROM … WHERE … < …". (I tried to count in a person table all names < "B". In that table persons can have more than one name, but most have just one name.) To my surprise the SELECT found no occurrences. A BTree index on the field made no difference.

But then I tried with a single-valued calculated column containing the first value from the former field. Then my SELECT COUNT(*) found several hundred occurrences, as expected.

---

I'd be grateful if somebody could point out the possible mistakes I've made, replicate my results, or just send a few comforting words…

I use OI 3.3.

- Oystein -

View this thread on the forum...