Re: Using ConText contains() in subquery

From: Joel R. Kallman <jkallman_at_us.oracle.com>
Date: Tue, 16 Nov 1999 12:24:06 GMT
Message-ID: <38314c5c.44520857@newshost.us.oracle.com>

Keep in mind that this restriction has been lifted in interMedia Text in Oracle8i.

As an example:

SQL> create table foo1 (id number primary key, text varchar2(200)); Table created.

SQL> create table foo2 (id number primary key, text varchar2(200)); Table created.

SQL> insert into foo1 values(1,'Joel was here'); 1 row created.

SQL> insert into foo1 values(2,'Joel is cool'); 1 row created.

SQL> commit;
Commit complete.

SQL> create index foo_idx on foo1(text) indextype is ctxsys.context; Index created.

SQL> select count(*) from foo1 where contains(text,'cool') > 0;   COUNT(*)

---

         1

SQL> insert into foo2 select id, text from foo1 where contains(text,'cool') > 0;

1 row created.

On 14 Nov 1999 04:22:37 GMT, timkarnold_at_aol.com (TimKArnold) wrote:

>From the documentation:
>
>Restrictions
>The CONTAINS function can only appear in the WHERE clause of a SELECT
>statement.
>
>
>You cannot issue the CONTAINS function in the WHERE clause of an UPDATE, INSERT
>or DELETE statement.
>
>
>regards,
>Tim
>
>
>>In the code below, the contains() function call fails with an "invalid
>>column" error message. But if you comment out the first line and run
>>the select statement as a top-level query, it works perfectly. We're
>>building the (much more complicated) select statement dynamically, so
>>redoing this as a stored procedure would be awkward. Any ideas?
>>
>>insert into search_results (catalog_entry_id, score)
>>SELECT catalog_entry.catalog_entry_id,
>>((score(10) * 5) + (score(30) * 2) + (score(20) * 1))
>>FROM catalog_entry
>>WHERE
>>( contains(catalog_entry.title, 'pigskin', 10) > 0
>>or contains(catalog_entry.short_description, 'pigskin', 30) > 0
>>or contains(catalog_entry.long_description, 'pigskin', 20) > 0
>>)
>>
>>
>>
>>
>> Marc Schabb
>> Foundry
>> 410-223-2760 x3016
>>
>>
>
>

Thanks!

Joel

Joel R. Kallman Oracle Service Industries

Columbus, OH                             http://govt.us.oracle.com

jkallman@us.oracle.com                   http://www.oracle.com

---

The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Received on Tue Nov 16 1999 - 06:24:06 CST