Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Using ConText contains() in subquery
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