Re: Strange query - Oracle Text problems?

Now we are getting somewhere!
So -- can you agree with me that this where clause does not do what you want:
WHERE (:title_filter IS NULL OR CONTAINS(title,:title_filter) > 0)) that is, when Oracle grabs it (with a filter var of 'fubar') it parses the clause as
WHERE ('fubar' IS NULL OR CONTAINS(title,'fubar') > 0)) or if the filter var is null
WHERE ('' IS NULL OR CONTAINS(title,'') > 0)) in the first case, 'fubar' is null is always false, so the contains clause result pertains.
in the second case, you should get an oracle text error [CONTAINS(title,'') > 0 does not make sense] As far as I can tell, you must pass a value into the search expression parameter of the contains clause. Try your example with :the_filter set to NULL and see what happens.

You have to eliminate unnecessary contains clauses from the where clause as you prepare the where clause. In doing that, you can simply OR the results of the contains clauses.

hth

yitbsal_at_yahoo.com wrote:
> That is precisely what I'm trying to ask! To elaborate, what I've
> posted is a simplified version of a query (still simplified) of the
> sort:
>
> SELECT *
> FROM articles
> WHERE (:title_filter IS NULL OR CONTAINS(title,:title_filter) > 0)
> AND (:abstract_filter IS NULL OR CONTAINS(abstract,:abstract_filter) >
> 0)
> AND (:publication_filter IS NULL OR
> CONTAINS(publication,:publication_filter) >0)
> ...
>
> where title_filter, abstract_filter, and publication_filter are strings
> containing search terms for title, abstract, and publication
> respectively. These are input parameters to my procedure.
Received on Wed Nov 22 2006 - 20:43:31 CST