| ORDER BY [message #2514] |
Mon, 22 July 2002 21:42  |
mary
Messages: 19
Registered: November 1999
|
Junior Member |
|
|
Why do I receive error in the following SQL statement?
SELECT ename
FROM emp
WHERE job = 'SALESMAN'
UNION
SELECT ename
FROM emp
WHERE job = 'CLERK'
ORDER BY empno desc
After I include the column name in which I'll sort the result the SQL below worked:
SELECT empname, empno
FROM emp
WHERE job = 'SALESMAN'
UNION
SELECT empname, empno
FROM emp
WHERE job = 'CLERK'
ORDER BY empno desc
But why does the following SQL also works:
SELECT ename
FROM emp
WHERE job = 'SALESMAN'
ORDER BY empno desc
|
|
|
|
| Re: ORDER BY [message #2515 is a reply to message #2514] |
Mon, 22 July 2002 22:08  |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
In fact, the ORDER BY clause in your first and second select refers to the entire select (including the UNION).
I'll try to explain:
Oracle first creates internally a sort of "temporary view" using the select with the union. Then, after getting the result of this select, Oracle will sort it, according to you ORDER BY clause. But, in the first case, Oracle won't find the EMPNO column in it's result set. In the second SELECT, you've included the column so it won't pose anymore problem. The third case is a single select, which doesn't cause such a "temporary view" (in fact, it is more like a subset in the memory) to be created. Oracle is well aware of the description of the table.
You might wonder why this mechanism works like that. Well, with a UNION you can perform a select over different tables. Look at the example below:
SQL> select empno
2 from emp
3 union
4 select deptno
5 from dept;
EMPNO
----------
1
10
12
....
....
7902
7934
22 rows selected.
The description of the two tables is different, yet Oracle manages to perform the requested task. As long as the number and datatype of both select match, there's no problem. But the drawback is that you won't be able to use the sort of a column that isn't in the select list.
HTH,
MHE
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
