Home » SQL & PL/SQL » SQL & PL/SQL » Union (Oracle9i)
Union [message #441500] Mon, 01 February 2010 08:59 Go to next message
prakashaa
Messages: 31
Registered: November 2009
Location: Bangalore
Member
Hi Goodevening Experts,

I have a problem with the following query.

SELECT empno,ename FROM emp
UNION
SELECT deptno,dname FROM dept
ORDER By ename,dname;

while executing this qury iam getting the error
like this

dname invalid identifier
*

If i use any column from first query i am not getting ang error
please give me the reason.

And also give me the solution to display the roes in the
order of "ename" and "dname"

Please help me thanks in advance.
Re: Union [message #441501 is a reply to message #441500] Mon, 01 February 2010 09:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since ename and dname will be returned in the same column, you cannot sort on each individually.
If you want to search on columns in a union, you would typically refer to them by its position.
So, in your case, order by 2

If however you want to sort, displaying the emps first (ordered by ename) and then the depts (ordered by dname), you will have to introduce a sort-column.
Re: Union [message #441502 is a reply to message #441501] Mon, 01 February 2010 09:04 Go to previous messageGo to next message
cookiemonster
Messages: 12420
Registered: September 2008
Location: Rainy Manchester
Senior Member
Frank wrote on Mon, 01 February 2010 15:02

If you want to search on columns in a union, you would typically refer to them by its position.
So, in your case, order by 2

Or alternatively alias the two columns to the same name and refer to the alias in the order by.
Re: Union [message #441564 is a reply to message #441500] Mon, 01 February 2010 23:31 Go to previous messageGo to next message
prakashaa
Messages: 31
Registered: November 2009
Location: Bangalore
Member
Hi Experts,
According to your reply i have written the statement like this.
I want to display the output ORDER BY ename,dname so that i have written the following SQL
statement

SELECT empno,ename From emp WHERE deptno=10
UNION
SELECT deptno,dname FROM dept
ORDER BY ename,2

but it's showing the output like this.

EMPNO ENAME
10 ACCOUNTING
7782 CLARK
7839 KING
7934 MILLER
40 OPERATIONS
20 RESEARCH
30 SALES

But i want the output like this

EMPNO ENAME

7782 CLARK
7839 KING
7934 MILLER
10 ACCOUNTING
40 OPERATIONS
20 RESEARCH
30 SALES

Pleasae help
Thanks
Ramesh
Re: Union [message #441566 is a reply to message #441564] Mon, 01 February 2010 23:34 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I told you that you will need an extra column.
Select a constant in each of the unions, then do a select over the result of the union (not selecting the extra column), and order first by that column.
Re: Union [message #441568 is a reply to message #441564] Mon, 01 February 2010 23:37 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
if you still don`t know what frank said see this...

SQL> select empno,ename from (SELECT empno,ename,'1' FROM sriram
  2  UNION
  3  SELECT deptno empno,dname ename,'2' FROM sriram_dept
  4  ORDER By 3,2)
  5  /

     EMPNO ENAME
---------- --------------
      7876 ADAMS
      7499 ALLEN
      7698 BLAKE
      7782 CLARK
      7902 FORD
      7900 JAMES
      7566 JONES
      7839 KING
      7654 MARTIN
      7934 MILLER
      7788 SCOTT
      7369 SMITH
      7844 TURNER
      7521 WARD
        10 ACCOUNTING
        40 OPERATIONS
        20 RESEARCH
        30 SALES

18 rows selected.


sriram Smile

[Updated on: Mon, 01 February 2010 23:38]

Report message to a moderator

Re: Union [message #441620 is a reply to message #441568] Tue, 02 February 2010 04:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ramoradba wrote on Tue, 02 February 2010 06:37
if you still don`t know what frank said see this...

SQL> select empno,ename from (SELECT empno,ename,'1' FROM sriram
  2  UNION
  3  SELECT deptno empno,dname ename,'2' FROM sriram_dept
  4  ORDER By 3,2)
  5  /


Well, actually I did not say this.
I would move the order by to the outermost part of the query.
Re: Union [message #441625 is a reply to message #441620] Tue, 02 February 2010 04:39 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
i just talked about the "dummy" column

NOt for the Query Smile
Re: Union [message #441667 is a reply to message #441625] Tue, 02 February 2010 09:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Ok, then for the dummy column: I would never add a string containing a number for the purpose of sorting.
I would simply add it as a number column.

So there too, it is not what I said.
Re: Union [message #441716 is a reply to message #441667] Tue, 02 February 2010 20:53 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Ok then i will end this discussion ....

This is not what "FRANK" said/(his view/style of coding).
This is RAMORADBA`s style Just to show you at least without considering any of the above issues raised by frank as it is Just from the table having 14 rows.....

Sriram Smile
Re: Union [message #441718 is a reply to message #441716] Tue, 02 February 2010 21:00 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
@ FRANK,
Your last two posts are like a lesson to me.
That I/we should care/considering each and every point when we are posting it...
Because i really did n`t care about the issues when they are based on small tables so i will change that kind of attitude.
Thanks for the remarks.

Sriram Smile
Previous Topic: Extracting numbers - Query
Next Topic: Problem with Materialized View compile state
Goto Forum:
  


Current Time: Thu Dec 08 06:24:19 CST 2016

Total time taken to generate the page: 0.05798 seconds