Re: Strange query - Oracle Text problems?

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.

What I'm saying is return the records where a title filter is not specified or (it's specified) the title contains the filter criteria. And so on.

So if title_filter = NULL, abstract_filter = 'economy', and publication_filter = NULL, then I get the rows that have 'economy' in the abstract column.

I hope this explanation is clear.

BicycleRepairman wrote:
> Your message subject is correct -- "Strange Query" -- but not in the
> way you are expecting.
>
> Think about your where clause for a minute.
>
> Oracle is expecting
>
> where {column name} is NULL
>
> you are changing it to
>
> where {string literal} is null
> (that is, not the FILE column that doesn't exist in your sample schema,
> but the letters 'file')
>
> I don't believe this is what you are trying to ask. A more likely query
> is:
> SELECT COUNT(*)
> FROM FOO
> WHERE search_col IS NULL OR CONTAINS(search_col,:the_filter) > 0;
>
> with which I have had better luck.
>
> If, for some reason, you are really trying to determine whether the
> user entered in one of your column names as their search expression,
> then you have a different problem with an entirely different approach
> -- one that doesn't have anything to do with Oracle Text.
Received on Wed Nov 22 2006 - 13:01:46 CST