Re: Oracle Text Question

From: Rajesh Kapur <rkapur_at_mpr.org>
Date: Wed, 12 Apr 2006 12:18:56 -0500
Message-ID: <123qdk4db4tge43@corp.supernews.com>

The following query works ...

SELECT con_id
  FROM contact
 WHERE contains(con_first_name, 'wellstone', 1) > 0

The following SQL issues a runtime error ...

SELECT con_id
  FROM contact
 WHERE contains(con_first_name, 'wellstone', 1) > 0

     OR contains(con_last_name, 'wellstone', 1) > 0

Here is the error I get ...

---

     OR contains(con_last_name, 'wellstone', 1) > 0
        *

ERROR at line 4:
ORA-29907: found duplicate labels in primary invocations

---

Thanks for your suggestion on UNION ALL. I will try it out.

"bravegag" <bravegag_at_gmail.com> wrote in message news:1144861156.780393.145470_at_j33g2000cwa.googlegroups.com...
> >I tried the following syntax and realized that it does
>>not work. Only one CONTAINS is allowed in the where clause.
>>
> Can you explain what "does not work" when you executed it?
> i.e. was it a query syntax error? runtime error? I have noticed
> many bugs in Oracle TEXT.
>
> Nevertheless, you might want to try converting your original query
> from:
>
> SELECT ...
> FROM ..
> WHERE ...
> AND (cond1 or cond2 or cond3)
>
> into
>
> SELECT ...
> FROM ..
> WHERE ...
> AND cond1
> UNION ALL
> SELECT ...
> FROM ..
> WHERE ...
> AND cond2
> UNION ALL
> SELECT ...
> FROM ..
> WHERE ...
> AND cond3
>
> This is how I remember I got away from a very nasty CBO
> problem while using Oracle TEXT, they fixed in the 9.2.x but no idea
> if they did in their 10g code base.
>
> HTH,
> Regards,
> Me
>
Received on Wed Apr 12 2006 - 12:18:56 CDT