Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2013
    Posts
    41

    Unanswered: Error in the usage of UNION operator.

    I have following two tables(DEPT and EMP):

    Code:
    DEPT
    
    DEPTNO,DNAME,LOC
    
    EMP
    
    EMPNO, ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
    I'm executing the following query: "Use a UNION to display department numbers and names and employee numbers and names. Choose appropriate column headings and sort it by name in ascending order."

    Here is my query:

    Code:
    SELECT Deptno, Dname 
    FROM DEPT
    ORDER BY Dname ASC
    UNION
    SELECT EMPNO,ENAME 
    FROM   EMP
    ORDER BY ENAME ASC;
    I'm wondering why I'm getting the following error:

    UNION * ERROR at line 4: ORA-00933: SQL command not properly ended.

    Could anyone please tell me what's wrong here?

    Thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You can only order the overalk result of the union not each individual part. Remove the order by from the first select
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Furthermore, your code will fail even if you remove ORDER BY from the first SELECT statement because - in UNIONs - you can ORDER BY column names from the first SELECT statement, and it doesn't contain the ENAME column:
    Code:
    SQL> select deptno, dname from dept
      2  union
      3  select empno, ename from emp
      4  order by ename;
    order by ename
             *
    ERROR at line 4:
    ORA-00904: "ENAME": invalid identifier
    
    
    SQL>
    That's why your homework assignment says: "Choose appropriate column headings and sort it by name ...". Therefore:
    Code:
    SQL> select deptno no, dname name from dept
      2  union
      3  select empno, ename from emp
      4  order by name;
    
            NO NAME
    ---------- --------------
            10 ACCOUNTING
          7876 ADAMS
          7499 ALLEN
          7698 BLAKE
          7782 CLARK
          7902 FORD
          7900 JAMES
          7566 JONES
          7839 KING
          7654 MARTIN
          7934 MILLER
            40 OPERATIONS
            20 RESEARCH
            30 SALES
          7788 SCOTT
          7369 SMITH
          7844 TURNER
          7521 WARD
    
    18 rows selected.
    
    SQL>
    You'd get the same result with both
    Code:
    SQL> select deptno, dname from dept
      2  union
      3  select empno, ename from emp
      4  order by dname;
    (where "dname" is a column name from the first SELECT statement) or
    Code:
    SQL> select deptno, dname from dept
      2  union
      3  select empno, ename from emp
      4  order by 2;
    (where "2" represents the second column).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •