Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to use a Multi_Column_Datastore index to search, but excluding a certain column?

DannyS-OracleJan 25 2018 — edited Jan 29 2018

Hi everyone, I am a beginner in Oracle Text, please be kind to me .

I have built a multi_column_datastore CONTEXT index on my application. And then, I have a specific case where I want to perform a search using this index, but excluding one of the columns.

For example, the index was built on columnA, columnB, and columnC. I want to check whether my database entries contain a keyword, which exist on any columns other than column C, using the same index. Is it possible to do this? Or do I have to build another index just to perform this check?

Thank you in advance...

This post has been answered by Bud Light on Jan 26 2018
Jump to Answer

Comments

isotope
Well, test it:
test@ora>
test@ora> --
test@ora> with date_table as (
  2    select sysdate as dt from dual union all
  3    select sysdate - 1   from dual union all
  4    select sysdate - 2   from dual)
  5  --
  6  select dt, a.a
  7  from date_table d,
  8  (select a from (select level a  from dual connect by 1 = 1) where rownum < 3) a;

DT                 A
--------- ----------
16-JUL-08          1
15-JUL-08          1
14-JUL-08          1
16-JUL-08          2
15-JUL-08          2
14-JUL-08          2

6 rows selected.

test@ora>
test@ora> --
test@ora> -- or
test@ora> --
test@ora> with date_table as (
  2    select sysdate as dt from dual union all
  3    select sysdate - 1   from dual union all
  4    select sysdate - 2   from dual)
  5  --
  6  select dt, a.a
  7  from date_table d,
  8  (select level a from dual connect by level < 3) a;

DT                 A
--------- ----------
16-JUL-08          1
15-JUL-08          1
14-JUL-08          1
16-JUL-08          2
15-JUL-08          2
14-JUL-08          2

6 rows selected.

test@ora>
test@ora>
isotope
Frank Kulash

Hi,

Cross-joining with a counter-table, that is, your

(select a from (select level a  from dual connect by 1 = 1) where rownum < 3) a

for example, or its simpler and more common equivalent

(   SELECT      LEVEL AS a
    FROM        dual
    CONNECT BY  LEVEL <= 3
) a

can give you some of the benefits of iteration. There are differences, which can be important. For example, in a procedural loop, like

for i in 1..:n loop 
    select a_data + from date_table; 
end loop; 

you can be sure that the iteration for i=1 is done before the iteration for i=2, and variables that you set in one iteration can be referenced in the next.
In Oracle 10 (and up) the MODEL feature allows true iteration.

alinux
Thanks for your answers.
this will work for me in the first phase:
select res.level_no + a - 1, res.rp_id
  from (select level_no, rp_id from cont_cust where h = a and rownum < 2) res,
        (select level a from dual connect by level <= 5)
Res table will always return one row.

But if I want to use the level(a) in some condition in res table.

the result of res table to be modified by 1,2,3 or 4(use it in where clause)?

see in bold-that is not possible...but something similar I want to obtain.

thanks
Frank Kulash

Hi,

It looks like you now know how to create a sub-query a that contains the integers 1, 2, ... n. It's as if you had a table with n rows, containing the numbers 1, 2, ..., n. You can use that result set the same way you use any other result set. Usually, that means joining it to some other result set. You can do inner joins, outer joins, or cross joins: each are appropriate for different tasks.
The reason your bolded text:

  from (select level_no, rp_id from cont_cust where h = a and rownum < 2) res,

doesn't work is that the query is based entirely on cont_cust, which (I suppose) doesn't have a column called a. If you want to reference column a from table a, you have to join with table a. "h = a" would be a perfectly valid join condition.

I'm not sure what you're trying to do. Concrete questions are always easier to answer. Can you post a specific example?
For instance:
"I want to display five copies of a given row from a table (say, the row from scott.emp that has the employee named King). I want to number those rows 1 through 5, and flag a given row (say, #3)".

If that were your question, the answer might be:

WITH a AS 
(
    SELECT     LEVEL AS a
    FROM       dual
    CONNECT BY LEVEL <= 5
)
SELECT      a.a
,           emp.ename
,           CASE
                WHEN a.a = 3  THEN 'This is special' 
            END    AS flag
FROM        a
CROSS JOIN  scott.emp
WHERE       ename = 'KING';

Output:

         A ENAME      FLAG
---------- ---------- ---------------
         1 KING
         2 KING
         3 KING       This is special
         4 KING
         5 KING
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 26 2018
Added on Jan 25 2018
2 comments
1,383 views