Hi Brian,
From your question, there appears to be multiple ways to solve your
problem. Essentially what you need to do is consider both the title
and the subtitle columns together in order to obtain the desired
result.
METHOD #1: OR Condition
--Begin Query
SELECT isbn,title,subtitle
FROM MASTER
WHERE title like '%cheese%'
OR subtitle like '%cheese%'
--End Query
This query will include rows that have the string cheese somewhere
within the title and/or subtitle. In other words, only a single
conditino needs to be met for the row to be included in the result.
METHOD #2: UNION Condition
--Begin Query
SELECT isbn, title, subtitle
FROM MASTER
WHERE title like '%cheese%'
UNION
SELECT isbn, title, subtitle
FROM MASTER
WHERE subtitle like '%cheese%'
--End Query
This method is slightly different as far as the actual operation being
conducted. There are actually two queries being executed here where
each one considers one condition. The 'UNION' operator will take these
two resulting tables and combine them into a single result. For rows
that contain 'cheese' in both the title and subtitle, the duplicate
will be removed. If you want to keep the duplicate, simply replace
'UNION' with 'UNION ALL'.
Either of the above queries should do what you require, but feel free
to post a clarification if any of the above information is unclear :)
Cheers!
answerguru-ga
Google Answers Researcher |
Request for Answer Clarification by
chiefarcher-ga
on
25 Jan 2003 07:23 PST
This isn't the answer i was looking for..
like '%cheeese%' will not give me a score.. nor is it a context database.
|
Clarification of Answer by
answerguru-ga
on
25 Jan 2003 10:00 PST
Brian,
If this isn't what you are looking for could you give me some further
details about the desired output of the query? Also some further
information on your database itself would be helpful.
Just out of curiosity, what are the results of the queries that I have
provided and how is that different from what you are after?
Thanks,
answerguru-ga
|
Request for Answer Clarification by
chiefarcher-ga
on
25 Jan 2003 11:33 PST
A Context index is an index that stores words from a text blob. When
you do a
select isbn,title,subtitle from MASTER where contains (title,
'Cheese',1) > 0;
it will look for a record that has cheese in it and gives a SCORE
depending on how high the word has ranked.. . for example if i was
looking for String Cheese and the title of the book was String Cheese,
the score would be 100%. If there was a recorc that just had cheese
in it, the match score would be somewhat lower..
The above query is what I BASICLY have now... I want to create an
INDEX with two columns in it and do a context search on that
concatinated INDEX (in oracle called a datastore).. THe above URL
from my orignal post states how the current datastore is created.
Unfortuanately, when I do a
select isbn,title,subtitle from MASTER where contains (concat,
'Cheese',1) > 0;
I get 0 results.... Oracle makes me try to use the "within" clause
inside the query in order to get any results when I just want to look
at the concatinated key as a whole.
|
Clarification of Answer by
answerguru-ga
on
25 Jan 2003 11:56 PST
OK I see what you mean now...unfortunately this type of index is not
possible under Oracle or any other RDBMS. The function of an index is
to provide quick access to a dataset on the basis of the entries in
EXACTLY one column. There is no possibility of using multiple indexes
at any given time on the same table. Furthermore, calculated fields
(such as concatenations in your case) cannot be indexed dynamically.
The only possible way of getting around this is to add a field to the
table that consists of the concatenation of the title and subtitle
fields. By storing this information explicitly, you may be sacrificing
some space, but you now be able to index the information you need. Is
this possible in your situation?
answerguru-ga
|
Request for Answer Clarification by
chiefarcher-ga
on
25 Jan 2003 14:02 PST
But isn't that the purpose of a datastore?? Indexing more than 1
columns
http://otn.oracle.com/sample_code/products/text/htdocs/concatenated_text_datastore/cdstore_readme.html
"The Concatenated Datastore is an additional datastore for Oracle
Text. It provides for extremely fast searching over multiple columns."
The problem is.. they tell you how to make one... but not how to use
it
Brian
|
Clarification of Answer by
answerguru-ga
on
25 Jan 2003 16:11 PST
Let me look into this for you...my apologies for the confusion.
|
Clarification of Answer by
answerguru-ga
on
26 Jan 2003 10:04 PST
I believe I've got the solution now...the link you provided actually
gave some me insight into what could be done to use the datastore.
Consider the example located at:
http://otn.oracle.com/sample_code/products/text/htdocs/concatenated_text_datastore/cdstore_readme.html#An
The query they eventually provide is of the form:
SELECT empno, ename FROM EMP
WHERE CONTAINS (ename,
'Ford within ename and Marketing within job and 20 within deptno')
> 0
We can modify this to obtain the result you are looking for:
SELECT isbn, title, subtitle
FROM MASTER
WHERE CONTAINS (title, 'cheese within title or cheese within
subtitle') > 0
That should do what you require..
answerguru-ga
|
