SQL issue with parenthesis

Question

I'm getting an error while processing this query:

SELECT *
FROM example
WHERE pri IN (
    SELECT pri
    FROM (
        SELECT pri
            ,sbst
            ,st
            ,count(*) AS cnt
        FROM example
        WHERE sbst = 'oi'
        GROUP BY pri
        ) AS tmp
    WHERE cnt = 1
        AND st = 'ko'
    )
AND sbst = 'cp';

the error is the following:

ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis" *Cause:
*Action: Error at Line: 5 Column: 136

But I don't think I missed a parenthesis.

Answer 1

Probably you need to GROUP BY pri, sbst, st

SELECT * 
from example 
WHERE pri IN 
(
    SELECT pri 
    FROM 
    (
        SELECT  pri, 
                sbst, 
                st, 
                count(*) AS cnt 
        FROM example 
        WHERE sbst = 'oi' 
        GROUP BY pri, 
                 sbst, 
                 st
    ) tmp 
    WHERE cnt = 1 AND st = 'ko'
) AND sbst = 'cp';

Answer 2

please format long queries. Then you could see what's wrong. Also use aliases to avoid hidden bugs

select *
from example as e
where
   e.pri in (
       select
           tmp.pri
       from (
           select e2.pri, e2.sbst, e2.st, count(*) as cnt
           from example as e2
           where e2.sbst = 'oi'
           group by e2.pri
       ) as tmp where tmp.cnt = 1 and tmp.st = 'ko'
   ) and e.sbst = 'cp';

Sorry for that not being the answer, by I really don't like to read unformatted queries on SO or in production code.