CODEWITHSUNDEEP
sqloracle-databasecontains
Paul Kar.
Paul Kar.

Reputation: 1324

ORA-29908: missing primary invocation for ancillary operator

I have an error that I can't seem to figure out. Anyone has an idea what I'm doing wrong here? Please assume that images has place, subject, description, and testing appears several times in subject or place or description. Also, I did set the ConText initialization parameter TEXT_ENABLE to be true. 

SELECT 4*SCORE(1) + 2*SCORE(2) + SCORE(3) AS RANK
FROM   images i
WHERE  CONTAINS(i.subject, 'testing', 1)>0
OR  CONTAINS(i.place, 'testing', 2)>0
OR  CONTAINS(i.description, 'testing', 3)>0
ORDER BY RANK;

Upvotes: 2

Views: 5464

Answers (2)

Paul Kar.
Paul Kar.

Reputation: 1324

I manged to solve the problem by surrounding the current query with another query and pulling the ORDER BY RANK to the outside query. Like so: 

SELECT * 
FROM (SELECT 4*SCORE(1) + 2*SCORE(2) + SCORE(3) AS RANK
      FROM   images i
      WHERE  CONTAINS(i.subject, 'testing', 1)>0
      OR  CONTAINS(i.place, 'testing', 2)>0
      OR  CONTAINS(i.description, 'testing', 3)>0)
ORDER BY RANK;

Upvotes: 2

Vincent Malgrat
Vincent Malgrat

Reputation: 67792

I can't reproduce on 10.2.0.4. What is your DB version?

test script:

CREATE TABLE text_data (
   txt1 VARCHAR2(4000), 
   txt2 VARCHAR2(4000), 
   txt3 VARCHAR2(4000)
);
INSERT INTO text_data 
   (SELECT text, text, text FROM all_source WHERE ROWNUM <= 1e4);

CREATE INDEX txt1 ON text_data (txt1) INDEXTYPE IS ctxsys.context;
CREATE INDEX txt2 ON text_data (txt2) INDEXTYPE IS ctxsys.context;
CREATE INDEX txt3 ON text_data (txt3) INDEXTYPE IS ctxsys.context;

SELECT 4 * SCORE(1) + 2 * SCORE(2) + SCORE(3) AS RANK
  FROM text_data i
 WHERE CONTAINS(i.txt1, 'FUNCTION', 1) > 0
    OR CONTAINS(i.txt2, 'FUNCTION', 2) > 0
    OR CONTAINS(i.txt3, 'FUNCTION', 3) > 0
 ORDER BY RANK;

Upvotes: 1

Related Questions