Can't use CONNECT BY and GROUP BY together

Question

I have two tables - employee and manager. One manager has many employees. I want to show it in parent-child relationship using CONNECT BY and GROUP BY command.

Structure for EMPLOYEE table:

CREATE TABLE employee (
  employee_id INTEGER,
  manager_id INTEGER,
  first_name VARCHAR2(10) NOT NULL,
  last_name VARCHAR2(10) NOT NULL
);

Structure for manager table:

CREATE TABLE manager(
  manager_id INTEGER NOT NULL,
  manager_dept VARCHAR2(20) NOT NULL,
  first_name VARCHAR2(30) NOT NULL,
  last_name  VARCHAR2(30) NOT NULL
);

I wrote this query:

SELECT E.EMPLOYEE_ID, M.MANAGER_ID, E.FIRST_NAME, E.LAST_NAME, CONNECT_BY_ISLEAF, CONNECT_BY_ISCYCLE
FROM EMPLOYEE E
LEFT OUTER JOIN MANAGER M
ON E.MANAGER_ID=M.MANAGER_ID
connect by NOCYCLE m.manager_id=prior e.employee_id
GROUP BY manager_id;

It gives this error:

ORA-00918: column ambiguously defined
00918. 00000 -  "column ambiguously defined"
Error at Line: 6 Column: 10

What's wrong with this group by clause?

Answer 1

You are getting column ambiguously defined because oracle is unable to determine which table's manager_id should be used for group by as you have not specified it. Correct Query is:

SELECT M.MANAGER_ID
FROM EMPLOYEE E
LEFT OUTER JOIN MANAGER M
ON E.MANAGER_ID=M.MANAGER_ID
connect by NOCYCLE m.manager_id=prior e.employee_id
GROUP BY m.manager_id;

Answer 2

The column manager_id in your GROUP BY is ambiguous - it could be the manager_id of the employee table, as well as the manager_id of the manager table.

You also need to add the remaining columns to your GROUP BY. The complete query then becomes:

SELECT 
  E.EMPLOYEE_ID, M.MANAGER_ID, E.FIRST_NAME, 
  E.LAST_NAME, CONNECT_BY_ISLEAF, CONNECT_BY_ISCYCLE
FROM EMPLOYEE E
LEFT OUTER JOIN MANAGER M
  ON E.MANAGER_ID=M.MANAGER_ID
connect by NOCYCLE m.manager_id=prior e.employee_id
GROUP BY 
  E.EMPLOYEE_ID, M.MANAGER_ID, E.FIRST_NAME, 
  E.LAST_NAME, CONNECT_BY_ISLEAF, CONNECT_BY_ISCYCLE;