1 Reply Latest reply: Aug 1, 2013 11:21 AM by Vadim Tropashko-Oracle RSS

    SQL Developer 4EA1 Issue

    mleonard87

      Im getting a yellow underline informing me that my GROUP BY clause does not match the selected columns. A fabricated reproduction below gives the error:

       

      SELECT list inconsistent with GROUP BY; ammend GROUP BY clause to:

      e.empno, e.ename, d.deptno, e.deptno, e.job

       

      Using the following query (this query runs just fine):

       

      SELECT

        e.empno

      , e.ename

      , e.job

      , (

        SELECT MIN(d.dname)

        FROM scott.dept d

        WHERE e.deptno = d.deptno

      ) min_dept

      FROM scott.emp e

       

      Clicking the error message amends the query to an invalid state as is adds d.deptno (in red) as a GROUP BY column as well as just being completely incorrect. The d.dept_no column is only exposed in the correlated sub query:

       

      SELECT

        e.empno

      , e.ename

      , e.job

      , (

        SELECT MIN(d.dname)

        FROM scott.dept d

        WHERE e.deptno = d.deptno

      ) min_dept

      FROM scott.emp e group by e.empno, e.ename, d.deptno, e.deptno, e.job

       

      This error appears to be caused by identifying the MIN(...) KEEP (...) and assuming that the entire query needs a GROUP BY rather than realising that the MIN(...) KEEP (...) is within a sub query. For example, simply changing the query as follows causes the error to go away (edited lines are in red):

       

      SELECT

        e.empno

      , e.ename

      , e.job

      , (

        SELECT d.dname

        FROM scott.dept d

        WHERE e.deptno = d.deptno

      AND rownum = 1

      ) min_dept

      FROM scott.emp e