ConText searches on multiple columns

I have another ConText question (Joel??) and a possible answer. After writing all of my question down, an answer occurred to me (see the end of this post). I'm sending it anyway, in case it can help someone else or be shot down by someone who knows better...:)

I want to do searches on multiple columns. I have a policy defined for each column, but, to get all of the found documents, my select statement is pretty hairy.

I understand that I can use an SQE to do an 'AND' kind of search on multiple columns -- where I narrow the search based on the columns. But I want to do an 'OR' kind of search -- if the text is found in the 'name' column OR in the 'transcript' column, I want to show the record.

Here's how I do it now:

ctx_query.gettab(CTX_QUERY.HITTAB, name_hits); ctx_query.gettab(CTX_QUERY.HITTAB, transcript_hits);

ctx_query.contains('TRANSCRIPT_POLICY', text_to_be_found, transcript_hits);
ctx_query.contains('NAME_POLICY', text_to_be_found, name_hits);

select distinct column1, column2, etc..
FROM (SELECT name_hits.score,

	     transcript_hits.score,
	     column1, 
	      column2, etc.
	FROM name_hist, transcript_hits, real_table
	WHERE transcript_hits.textkey = real_table.column1 OR
	  	name_hist.textkey = real_table.column1)

For now, we'll ignore the fact that the 'score' field in the ORDER BY clause is ambiguous.

My problem here is, the only records I get are the ones that have the text_to_be_found string in BOTH the name and transcript columns. This just started happening recently -- before, I was getting all of the records I expected, but I was getting some of them twice -- if the text occurred in both columns, I got the record twice. That was fine with me at this point.

If I just use 'score' in the FROM (SELECT...) statement, then I get a "column ambiguously defined" error, which doesn't surprise me.

*Somebody* must be doing this in the real world! This is a very common search, especially on the web, so someone must have solved this problem before. Just what kind of godawful select statement do I have to construct to make this work? How do I disambiguate the score columns??

Ideally, I'd like to have something like an OR in the SELECT portion of that statement -- SELECT name_hits.score OR transcript_hits.score, OR, if they're both defined, their sum.

Hmmm.. maybe that means I should use a PL/SQL function there. Perhaps I just answered my own question. Then I can use a column alias to disambiguate the ORDER BY clause. That might work.

I guess it helps to write this stuff down. I'll post it anyway -- maybe it will help someone else, or someone will poke holes in my idea...

leslie

-- 
Leslie Dreyer Kalra
Techies, Inc.
lbd_at_techiesinc.com