Getting ORA-00907: missing right parenthesis when no extra parenthesis on left

user_10551333 0
Viewed 71

Here is my SQL query:

select hck.hacker_id, hck.name, cnt
from (
    Hacker as hck 
    inner join (
       Select hacker_id, count(challenge_id) as cnt 
       from Challenges 
       group by hacker_id
    ) chl_count on hck.hacker_id = chl_count.hacker_id
) having cnt = max(cnt) or 
cnt in (select cnt 
        from chl_count 
        group by cnt 
        having count(hacker_id) = 1) 
order by cnt desc, hck.hacker_id asc;

Here Hackers has schema:

Hackers(name, hacker_id)

And Challenges has schema

Challenges(hacker_id, ,challenge_id)

I don't see any missing parenthesis in the query. So, what is wrong? Also, other syntaxes such as commas are correct as well.

oracle sql
1 Answers

It seems you are new in Oracle SQL.

You can't do this: "..FROM (Hacker as hck inner join) .." but you can do it like this:

WITH chl_count
     AS (  SELECT hacker_id, COUNT (challenge_id) AS cnt
             FROM Challenges
         GROUP BY hacker_id)
  SELECT hck.hacker_id, hck.name, cnt
    FROM Hacker hck INNER JOIN chl_count ON hck.hacker_id = chl_count.hacker_id
  HAVING    cnt = (select max(challenge_id) from Challenges)
         OR cnt IN (  SELECT cnt
                        FROM chl_count
                      WHERE hacker_id= 1)
ORDER BY cnt DESC, hck.hacker_id ASC;

It should work now.

user_57305900
Related