Can anyone see what is wrong with this query:
INSERT INTO CAND_SEARCH_RESULT_IDS (csr_id, sort_seq, cand_id)
SELECT
358572 csr, rownum sort_seq,
c.cand_id
FROM
candidates c
WHERE
EXISTS (SELECT cv.cand_id
FROM candidate_visibility cv
WHERE c.cand_id = cv.cand_id AND cv.client_id = 'client')
AND c.status IN ('AP', 'UC', 'IC', 'UI')
AND (c.client_id = 'client')
AND (c.cand_id IN (SELECT SCORE(1) score, cand_id
FROM cand_kw
WHERE client_id = 'client'
AND (CONTAINS(kw, 'customer service', 1) > 0)
ORDER BY SCORE(1) DESC));
It keeps telling me I am missing a right parenthesis but I am not. The issue is with the query inside the c.cand_id in section. If I extract :
SELECT
SCORE(1) score,
cand_id
FROM
cand_kw
WHERE
client_id = 'client'
AND (CONTAINS(kw, 'customer service', 1) > 0)
ORDER BY
SCORE(1) DESC
it works perfectly.
I am using an old Oracle 9i database on a 20 year old application which cannot be updated.
Remove the order-by clause from the subquery:
...
and ( c.cand_id in
(
SELECT
-- SCORE(1) score,
cand_id
from
cand_kw
WHERE
client_id = 'client'
AND
(
CONTAINS(kw, 'customer service', 1) > 0
)
-- ORDER BY
-- SCORE(1) DESC
)
);
It doesn't make sense there - it has no impact on the result of the IN() - and is illegal in some subqueries, depending on the context.
Because the parser knows it's illegal in this case, it is expecting to see the closing parenthesis for the subquery where you have ORDER. It doesn't see one there, so it throws the exception that it is missing. That error doesn't always mean that the number of left and right parentheses don't match, just that it isn't see one where it expects to.
As you pointed out, you also have to remove the SCORE(1) score expression and alias form the subquery. You are comparing the c.cand_id value with the list of values returned by that subquery; so it can only have a single column. (You can compare pairs of values but that isn't helpful here unless you're looking for a specific score).