How to use two columns with clause definescore oracle text

Question

I have this code:

declare 
sName varchar(25);
iRank number := 0;
sDesc varchar(510);
cursor q is  
  SELECT *
    FROM trec_topics ORDER BY num; 
BEGIN 
  for ql in q
  loop
   sDesc := replace(replace(replace(ql.title, '?', '{?}'), ')', '{)}'), '(', '{(}');   
    --dbms_output.put_line(ql.num||'-'||sDesc);
    declare 
    cursor c is    
      SELECT /*+ FIRST_ROWS(100) */ docno, 
           CASE
            WHEN SCORE(10) >= SCORE(20) THEN SCORE(10)
            ELSE SCORE(20)
        END AS SCORE        
        FROM txt_search_docs WHERE CONTAINS(txt, 'DEFINESCORE(ql.title, OCCURRENCE)', 10) > 0 OR
         CONTAINS(txt, 'DEFINESCORE(sDesc, OCCURRENCE)', 20) > 0
        order by SCORE desc;
        begin
          iRank := 1;
          for c1 in c 
          loop 
            dbms_output.put_line(ql.num||'  Q0 '||c1.docno||'  '||lpad(iRank,3, '0')||'  '||lpad(c1.score, 2, '0')||'  myUser');
            iRank := iRank + 1; 
            exit when c%rowcount = 100; 
          end loop;
        end;
  end loop; 
end; 

As you can see I'm doing select on two different tables, however, I need to change the standard score, as it did not perform well. I'm trying to use the DEFINESCORE clause that has this 'DEFINESCORE (query_term, scoring_expression)' format.

How can I call the table columns within this clause? That is, I need to call my columns instead of "query_term", as there are several documents to do the search. Because the way I’m calling him, he’s looking for exactly the term ql.title

Anyone a suggestion to help me with this problem?

Answer 1

I finally managed to solve it.

It was about:

1. create a variable: topics varchar (525);
2. store the column value: topics := replace(replace(replace(ql.title, '?', '{?}'), ')', '{)}'), '(', '{(}');
3. and after calling it in the CONTAINS clause: FROM txt_search_docs WHERE CONTAINS(txt, 'DEFINESCORE(('''||topics||'''), OCCURRENCE)', 1) > 0