| Near Clause [message #496270] |
Fri, 25 February 2011 11:40  |
rajivn786
Messages: 161
Registered: January 2010
|
Senior Member |
|
|
Hi,
I have an issue regarding near clause..Created sample table and also created an index including stop list (indextype
is ctxsys.context parameters ('stoplist ctxsys.empty_stoplist')..
Table create & insert are as follows :
create table comic (mus varchar2(20), rep_text varchar2(1000));
Insert into comic values ('1','press on a door');
Insert into comic values ('2','press on a remit');
Insert into comic values ('3','press on a recover');
Insert into comic values ('4','press on a resolve');
Insert into comic values ('5','press in remis');
create index a_idx on comic (rep_text) indextype
is
ctxsys.context parameters ('stoplist ctxsys.empty_stoplist')
parallel 32;
The following code works good..
Select distinct mus from comic
where contains(rep_text,'near((press,remit = recover = resolve ), 3)') > 0
But now when I add the following it is giving incorrect results..
Select distinct mus from comic
where contains(rep_text,'near((press,remit = recover = resolve = in remis ), 3)') > 0
In fact the code works fine, but it doesn't equal the count when I perform the same with union..
Select distinct mus from comic where contains(rep_text,'near((press,remit), 3)') > 0
union
Select distinct mus from comic where contains(rep_text,'near((press,recover), 3)') > 0
union
Select distinct mus from comic where contains(rep_text,'near((press,resolve), 3)') > 0
union
Select distinct mus from comic where contains(rep_text,'near((press,in remis), 3)') > 0
May I know what's the problem?? I think it's with the in-clause...Can anyone resolve this??
|
|
|
|
|
|
| Re: Near Clause [message #496277 is a reply to message #496276] |
Fri, 25 February 2011 12:52  |
rajivn786
Messages: 161
Registered: January 2010
|
Senior Member |
|
|
Yes Barbara,
I did face this issue several times...Is there any workaround for searching multiple terms using nearby clause...So is is that whenever we encounter multiple terms in near clause do we need to do them separately using union statement?
Thanks..
|
|
|
|
| Re: Near Clause [message #496278 is a reply to message #496277] |
Fri, 25 February 2011 13:10 |
 |
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following would be more efficient syntax.
SCOTT@orcl_11gR2> create table comic
2 (mus varchar2 ( 20),
3 rep_text varchar2 (1000))
4 /
Table created.
SCOTT@orcl_11gR2> Insert all
2 into comic values ('1', 'press on a door')
3 into comic values ('2', 'press on a remit')
4 into comic values ('3', 'press on a recover')
5 into comic values ('4', 'press on a resolve')
6 into comic values ('5', 'press in remis')
7 select * from dual
8 /
5 rows created.
SCOTT@orcl_11gR2> create index a_idx on comic (rep_text)
2 indextype is ctxsys.context
3 parameters ('stoplist ctxsys.empty_stoplist')
4 parallel 32
5 /
Index created.
SCOTT@orcl_11gR2> Select distinct mus from comic
2 where contains
3 (rep_text,
4 'near ((press, remit = recover = resolve), 3) or
5 near ((press, {in} remis), 3)') > 0
6 /
MUS
--------------------
3
5
2
4
4 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
