CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Tan
Tan

Reputation: 1575

UNION Query Error

I have following two tables(DEPT and EMP):

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:

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

Upvotes: 0

Views: 92

Answers (2)

sivareddy
sivareddy

Reputation: 48

Try this

SELECT Deptno AS ID, Dname  AS NAME
FROM DEPT
UNION
SELECT EMPNO  AS ID,ENAME  AS NAME
FROM   EMP
ORDER BY NAME ASC

EDIT

To display four columns check below query using join

SELECT E.EMPNO ,E.ENAME ,D.Deptno , D.Dname 
FROM DEPT D
INNER JOIN 
EMP E ON E.DeptNO = D.DeptNo
ORDER BY E.ENAME ASC

Upvotes: 2

Glitch Desire
Glitch Desire

Reputation: 15023

Remove ORDER BY clause in the first query.

So change to:

SELECT Deptno, Dname 
FROM DEPT
UNION
SELECT EMPNO,ENAME 
FROM   EMP
ORDER BY Dept.Dname, Emp.Ename ASC;

Should work.

Upvotes: 0

Related Questions