CODEWITHSUNDEEP
oracle-database
Olga Vasileva
Olga Vasileva

Reputation: 31

Oracle Missing right parenthesis ORA-00907

Consider a database schema with three relations:

Employee (*eid:integer*, ename:string, age:integer, salary:real)
Works (*eid:integer*, *did:integer*, pct_time:integer)
Department(*did:integer*, dname:string, budget:real, managerid:integer)

Query the view above to find the name of the manager who manages most employees. If thesame employee works in several departments, that employee is counted once in each of the departments. The manager is included in the count the same as all other employees, i.e., based on his or her records in the Works table.

 

Why do I get this error:

ORDER BY SUM (EmpCount) DESC)

*

ERROR at line 6: ORA-00907: missing right parenthesis

Here is my query:

SELECT distinct(MgrName)
FROM ManagerSummary
WHERE MgrID = (SELECT MgrID
             FROM ManagerSummary
             GROUP BY MgrID
             ORDER BY SUM (EmpCount) DESC
             LIMIT 1 );

The view is:

CREATE VIEW ManagerSummary (DeptName, MgrID, MgrName, MgrSalary, EmpCount)
AS SELECT d.dname, d.managerid, e.ename, e.salary,
   (SELECT COUNT (w.did)
    FROM works w
    WHERE w.did = d.did
    GROUP BY w.did)
FROM employee e, department d WHERE d.managerid = e.eid;

Thank you

Update: Changing LIMIT 1 for WHERE ROWNUM = 1 doesn't help

Upvotes: 0

Views: 308

Answers (3)

cableload
cableload

Reputation: 4375

Try this

SELECT DISTINCT (MgrName)
FROM ManagerSummary
WHERE MgrID = (SELECT MgrId
              FROM (  SELECT MgrId, SUM (empcount) AS maxemp
                        FROM ManagerSummary
                    GROUP BY MgrId
                    ORDER BY SUM (empcount) DESC)
             WHERE ROWNUM = 1)

Upvotes: 1

T Gray
T Gray

Reputation: 712

SELECT mgrname
  FROM (SELECT mgrname, numemps
          FROM (SELECT mgrname, count(*) numemps
                  FROM mgrsummary
              GROUP BY mgrname)
        ORDER BY NUMEMPS desc);

Just noticed - this is based on a view. This is ~not~ going to perform well.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You seem to want the name of the manager that has the most employees.

My guess is that you can do this in Oracle as:

select ms.MgrName
from (select ms.*
      from ManagerSummary ms
      order by EmpCount desc
     ) ms
where rownum = 1;

It is hard for me to envision a table called ManagerSummary that would have more than one row per MgrId. That's why I don't think aggregation is necessary.

Upvotes: 0

Related Questions