OraFAQ Forum: Text & interMedia => Can you find which section_group matched in a multi_column

I know this is a little late, but I thought I would post it in case you are still looking for an answer or anybody else searches for the same thing.

-- test data:

SCOTT@orcl_11g> CREATE Table your_table
  2    (col1  VARCHAR2(10),
  3  	col2  VARCHAR2(10),
  4  	col3  VARCHAR2(10))
  5  /

Table created.

SCOTT@orcl_11g> BEGIN
  2    INSERT INTO your_table VALUES ('test1 test', NULL, NULL);
  3    INSERT INTO your_table VALUES (NULL, 'test2 test', NULL);
  4    INSERT INTO your_table VALUES (NULL, NULL, 'test3 test');
  5  END;
  6  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE ('your_datastore', 'MULTI_COLUMN_DATASTORE');
  3    CTX_DDL.SET_ATTRIBUTE ('your_datastore', 'COLUMNS', 'col1, col2, col3');
  4    CTX_DDL.CREATE_SECTION_GROUP ('your_sec_group', 'BASIC_SECTION_GROUP');
  5    CTX_DDL.ADD_FIELD_SECTION ('your_sec_group', 'column1', 'col1', TRUE);
  6    CTX_DDL.ADD_FIELD_SECTION ('your_sec_group', 'column2', 'col2', TRUE);
  7    CTX_DDL.ADD_FIELD_SECTION ('your_sec_group', 'column3', 'col3', TRUE);
  8  END;
  9  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> CREATE INDEX your_index ON your_table (col1)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS
  4    ('DATASTORE	your_datastore
  5  	 SECTION GROUP	your_sec_group
  6  	 STOPLIST	CTXSYS.EMPTY_STOPLIST')
  7  /

Index created.

-- function for usage in extracting information:

SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION list_element
  2  	(p_string    VARCHAR2,
  3  	 p_element   INTEGER,
  4  	 p_separator VARCHAR2 DEFAULT ':')
  5  	RETURN	     VARCHAR2
  6  AS
  7    v_string      VARCHAR2 (32767);
  8  BEGIN
  9    v_string := p_string || p_separator;
 10    FOR i IN 1 .. p_element - 1 LOOP
 11  	 v_string := SUBSTR (v_string,
 12  			     INSTR (v_string, p_separator)
 13  			       + LENGTH (p_separator));
 14    END LOOP;
 15    RETURN SUBSTR (v_string, 1, INSTR (v_string, p_separator) - 1);
 16  END list_element;
 17  /

Function created.

-- where the information comes from:

SCOTT@orcl_11g> SELECT t.*, ROWID FROM your_table t
  2  /

COL1       COL2       COL3       ROWID
---------- ---------- ---------- ------------------
test1 test                       AAAYHcAAEAAAyoYAAA
           test2 test            AAAYHcAAEAAAyoYAAB
                      test3 test AAAYHcAAEAAAyoYAAC

SCOTT@orcl_11g> COLUMN token_text FORMAT A15
SCOTT@orcl_11g> COLUMN token_type FORMAT 9999999999
SCOTT@orcl_11g> SELECT token_text, token_type, token_first, token_last
  2  FROM   dr$your_index$i
  3  /

TOKEN_TEXT       TOKEN_TYPE TOKEN_FIRST TOKEN_LAST
--------------- ----------- ----------- ----------
TEST                      0           1          3
TEST                     16           1          1
TEST                     17           2          2
TEST                     18           3          3
TEST1                     0           1          1
TEST1                    16           1          1
TEST2                     0           2          2
TEST2                    17           2          2
TEST3                     0           3          3
TEST3                    18           3          3

10 rows selected.

SCOTT@orcl_11g> SELECT * FROM dr$your_index$k
  2  /

     DOCID TEXTKEY
---------- ------------------
         1 AAAYHcAAEAAAyoYAAA
         2 AAAYHcAAEAAAyoYAAB
         3 AAAYHcAAEAAAyoYAAC

SCOTT@orcl_11g> COLUMN ixv_value FORMAT A20
SCOTT@orcl_11g> COLUMN from_column FORMAT A15
SCOTT@orcl_11g> SELECT ixv_value,
  2  	    list_element (ixv_value, 2) AS from_column,
  3  	    TO_NUMBER (list_element (ixv_value, 3 )) AS token_type
  4  FROM   ctx_user_index_values
  5  WHERE  ixv_index_name = 'YOUR_INDEX'
  6  AND    ixv_class = 'SECTION_GROUP'
  7  /

IXV_VALUE            FROM_COLUMN      TOKEN_TYPE
-------------------- --------------- -----------
COLUMN3:COL3:18:Y    COL3                     18
COLUMN1:COL1:16:Y    COL1                     16
COLUMN2:COL2:17:Y    COL2                     17

-- queries:

SCOTT@orcl_11g> SELECT t.*,
  2  	    i.token_text,
  3  	    list_element (v.ixv_value, 2) AS from_column
  4  FROM   your_table t, dr$your_index$i i, dr$your_index$k k, ctx_user_index_values v
  5  WHERE  CONTAINS (t.col1, 'test1 OR test2 OR test3') > 0
  6  AND    i.token_type = TO_NUMBER (list_element (v.ixv_value, 3))
  7  AND    k.docid BETWEEN i.token_first AND i.token_last
  8  AND    k.textkey = t.ROWID
  9  /

COL1       COL2       COL3       TOKEN_TEXT      FROM_COLUMN
---------- ---------- ---------- --------------- ---------------
                      test3 test TEST3           COL3
                      test3 test TEST            COL3
           test2 test            TEST2           COL2
           test2 test            TEST            COL2
test1 test                       TEST1           COL1
test1 test                       TEST            COL1

6 rows selected.

SCOTT@orcl_11g> SELECT t.*,
  2  	    i.token_text,
  3  	    list_element (v.ixv_value, 2) AS from_column
  4  FROM   your_table t, dr$your_index$i i, dr$your_index$k k, ctx_user_index_values v
  5  WHERE  CONTAINS (t.col1, 'test2') > 0
  6  AND    i.token_text = 'TEST2'
  7  AND    i.token_type = TO_NUMBER (list_element (v.ixv_value, 3))
  8  AND    k.docid BETWEEN i.token_first AND i.token_last
  9  AND    k.textkey = t.ROWID
 10  /

COL1       COL2       COL3       TOKEN_TEXT      FROM_COLUMN
---------- ---------- ---------- --------------- ---------------
           test2 test            TEST2           COL2

SCOTT@orcl_11g>