Home » SQL & PL/SQL » SQL & PL/SQL » unable to union two tables? (Oracle 10g)
unable to union two tables? [message #323090] Tue, 27 May 2008 04:54 Go to next message
rangan.s
Messages: 75
Registered: February 2008
Location: chennai
Member
SQL> desc on_get_it;
Name Type          Nullable Default Comments 
---- ------------- -------- ------- -------- 
TYPE VARCHAR2(200) Y                         
ID   ID_TYPE       Y                         
NAME VARCHAR2(100) Y                         

SQL> desc new;
Name          Type      Nullable Default Comments 
------------- --------- -------- ------- -------- 
ID            NUMBER                              
TYPEID        INTEGER                             
EXTERNAL_ID   ID_TYPE   Y                         
EXTERNAL_NAME NAME_TYPE Y     

SQL> select it.id,name_type(it.name) from on_get_it it
  2  union
  3  select n.external_id,n.external_name from node n
  4  /

select it.id,name_type(it.name) from on_get_it it
union
select n.external_id,n.external_name from new n

ORA-22950: cannot ORDER objects without MAP or ORDER method

[Updated on: Tue, 27 May 2008 04:58] by Moderator

Report message to a moderator

Re: unable to union two tables? [message #323094 is a reply to message #323090] Tue, 27 May 2008 05:02 Go to previous message
Michel Cadot
Messages: 64124
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Next time use code tags as I did it for you.

ORA-22950: cannot ORDER objects without MAP or ORDER method
 *Cause:  an object type must have a MAP or ORDER method defined for
          all comparisons other than equality and inequality comparisons.
 *Action: Define a MAP or ORDER method for the object type

You have to define these method for your custom types otherwise Orace can't know how to compare them.

Of course, maybe you just mean "UNION ALL" and not "UNION" which tries to remove duplicates and has to know how to compute what is a duplicate.

Regards
Michel

Previous Topic: Count characters
Next Topic: Issue while using Bulk Collect
Goto Forum:
  


Current Time: Wed Dec 07 06:38:08 CST 2016

Total time taken to generate the page: 0.09330 seconds