Help with CONTAINS near....

[jdemmi]

OK...so I have a 3rd party app that is using Oracle intermedia/Text for full text searching capbilities. One table in particualr contains documents (pdf, word, excel, plain text, etc) in a BLOB column. This column contains a CTXSYS (domain) Index.

The full text search capability through the app's front end is limited in nature -- much like many search engines you cannot use operators like instr, substr, to_char and contains. Normally this poses no issue as user never need to perform this advanced a search. However I've now been asked to search for all documents which contain a string which is not followed by any date.

For example most documents contain a section that includes a "sentence" like this..

Effective Date: 6/13/2009
or
Effective Date: June 13, 2009

The users need to see documents where Effective Date: is not followed by a date.

So here's the approach I took..

select bla 
from foo
where not contains (blob_field 'near((Effective Date:, 200?),100,TRUE)') > 0)

This query fails because it returns records that DO have Effective Date near 200?

I have also tried a specific date just to see if I could exclude a known record...like this...

select bla 
from foo
where not contains (blob_field 'near((Effective Date:, 02/28/2008),100,TRUE)') > 0)

This one failed as well.

I then started testing all sorts of other queries WITHOUT numbers/dates (word1 near word2) and they all work, even if I use word1? near word2%.

So it appears that the numbers are the issue. So I'd REALLY like to know how to make this work but if you have a better approach ENTIRELY (like not using contains and near) then by all means, please let me know.

What am I missing? I've reviewed the Oracle documentation that I have access to and don't see any specific examples and the few I've found via google are all text near text - no text near number.

PS -- I have also tried CONTAINS....near.....< 1

BTW...this is Oracle 10g on IBM AIX.

Let me know if you need more info to solve the problem.

Thanks in advance.

Jason

-- Jason
"It's Just Ones and Zeros

 

[jdemmi]

Just in case anyone was wondering....

I've solved my own problem and it wasn't the query. It was the domain index.

I dropped it an re-created it and the problem was fixed (only the records I expected to be returned were returned)

FYI -- my query ended up being several unions to accomodate the different dtae formats

select bla
from foo
where  contains (blob_field,'NEAR((Effective, Date:, 20__),10,TRUE)', 1) > 0

I then union the above, changing only "the date"

like __/__/200_ or __/__/0_ or __/__/199_

-- Jason
"It's Just Ones and Zeros