Thunderstone Support Forums

Hi,

we are using the virtual columns in our query as :

where " ((last_name\sc_last_name likep thomas* ) and (first_name\sc_first_name\preferred_name likep david*))" and active_flag=0

but it seems that it is returning results based on only the first part of query that is:
where (last_name\sc_last_name likep thomas* )

and the second part does not have any effect on number of results returned. Can you please suggest any change in our query or indexes.


Thanks and Regards
Priti B

Do you have indexes on both virtual fields?

What rank values do you get?

The total number of results will be limited by likeprows.

Hi John,

let me eloborate my problem a bit.

I am using the following query for searching people with lastname as thomas and firstname as haamid

"select last_name, first_name from person_profile_test where ((last_name\sc_last_name likep 'thomas*' ) and (first_name\sc_first_name\preferred_name likep 'haamid*')) and active_flag=0 order by ohr_id"

For this I am getting 995 results. Now if i change the first name to any text it still gives me the same number of results.
The results are coming only according to lastname part of AND clause "(last_name\sc_last_name likep 'thomas*' )" but the AND clause on the firstname seems to be ignored.

Hence if i search for search terms like "thomas,david" , "thomas,abd" , "thomas,haamid". it returns the same number of results.

The rank values for the above three search term comes out to be same as 372.

There are the two metamorph inverted indexes created:

first_name\sc_first_name\preferred_name ohr_id active_flag email business_name dialcomm

and

last_name\sc_last_name ohr_id active_flag email business_name dialcomm

Looking forward for your reply.

Regards
Priti

You may want to use LIKE instead of LIKEP, as relevance will probably have little impact on short text such as names.

If the first LIKEP returns fewer than 1000 records (settable via maxlinearrows) then it won't use the index for other parts of the query, but should still rank. The LIKEP without an index will still return the record, but should have a zero rank for that portion of the WHERE clause.

Hi John,

Thanks for your inputs. It resolved the issue.

Thanks & Regards,
Priti