Home » SQL & PL/SQL » SQL & PL/SQL » duplicate column name
duplicate column name [message #387552] Thu, 19 February 2009 21:19 Go to next message
philb
Messages: 10
Registered: February 2009
Junior Member
I have several tables with lots of columns in each that I want to merge together into a new table. All tables have different field names apart from the ID field. How do I create a new table avoiding the 'duplicate column name' or 'column ambigously defined' message, without having to explicity type out each field name in the select statement. The only duplicate is the ID colum.

Sorry if this is a bit of a junior question.

Thanks in advance.

CREATE TABLE pmix AS 
(SELECT 
store.*,
colour.*
 FROM 
store,
colour
 WHERE 
store.store_id = colour.store_id)
Re: duplicate column name [message #387554 is a reply to message #387552] Thu, 19 February 2009 22:02 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>How do I create a new table avoiding the 'duplicate column name' or 'column ambigously defined' message, without having to explicity type out each field name in the select statement.

CUT & PASTE works wonders to include columns names to avoid error
Re: duplicate column name [message #387555 is a reply to message #387554] Thu, 19 February 2009 22:12 Go to previous messageGo to next message
philb
Messages: 10
Registered: February 2009
Junior Member
'CUT AND PASTE' is not an option. All the SQL code is generated on the fly.

There must be a solution to this as it seems quite a rudimentary task.
Re: duplicate column name [message #387557 is a reply to message #387552] Thu, 19 February 2009 22:22 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you

>All the SQL code is generated on the fly.
BAD, BAD, Bad, bad "solution".

DDL should be static & under source code control.

>There must be a solution to this as it seems quite a rudimentary task.
If you say so.

If the application was designed to Third Normal Form, this "requirement" would not exist.

http://www.orafaq.com/forum/m/386141/136107/#msg_386141
Some folks are clue resistant. Other folks are clue repellent.

[Updated on: Thu, 19 February 2009 22:30]

Report message to a moderator

Re: duplicate column name [message #387561 is a reply to message #387557] Thu, 19 February 2009 22:36 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
What if when column name is same and data type different.

[Updated on: Thu, 19 February 2009 22:36]

Report message to a moderator

Re: duplicate column name [message #387562 is a reply to message #387557] Thu, 19 February 2009 22:38 Go to previous messageGo to next message
philb
Messages: 10
Registered: February 2009
Junior Member
and some folks speak in English!
Re: duplicate column name [message #387563 is a reply to message #387562] Thu, 19 February 2009 22:40 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Sorry , I don't get you.
Re: duplicate column name [message #387564 is a reply to message #387562] Thu, 19 February 2009 22:40 Go to previous messageGo to next message
philb
Messages: 10
Registered: February 2009
Junior Member
>BAD, BAD, Bad, bad "solution".

>Some folks are clue resistant. Other folks are clue repellent.


and some folks don't pass judgment unless they know exactly what is being done and why. And some folks actually try to answer the question rather than wasting typing time talking geek speak.

[Updated on: Thu, 19 February 2009 22:42]

Report message to a moderator

Re: duplicate column name [message #387567 is a reply to message #387552] Thu, 19 February 2009 22:51 Go to previous messageGo to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member


You will try to post the column and tables in the
User_Column_privs
table in the database


CVS
Re: duplicate column name [message #387571 is a reply to message #387564] Thu, 19 February 2009 23:00 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Ok Got it,

My question is

What if the column name is same and datatype is different or column name is same but usage is different including different size.

Just for knowlege I have shown this example, dont create and try.

create table customer_mst
(
cust_id		 number(10), 		-- Customer Id
name		 varchar2(100),		-- customer Name
id 		 number(10)		-- Some Id to store  
) 		 

create table customer_dtl
(
cust_id		 number(10), 		-- Customer id
street_id 	 number(10),		-- Street Id
name		 varchar2(1000),	-- Street Name
id 		 varchar2(10)		-- some street id
) 


What if the NAME and ID column is used in both the tables, what will you select to create a table.

Thanks
Trivendra

[Updated on: Thu, 19 February 2009 23:02]

Report message to a moderator

Re: duplicate column name [message #387633 is a reply to message #387552] Fri, 20 February 2009 02:35 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
While Black swan is unnecessarily blunt he does have a point.

Creating table create scripts on the fly is a really bad idea. Don't know why you think you need to but if tell us the requirement we can probably point you to a better solution.

To answer your original question. If this has to be done programmatically then you've got to write a program that either:
1) doesn't select both id columns
2) does select both but aliases then to different names.


SELECT * is NOT an option here.
Previous Topic: clob and ref cursor
Next Topic: insertion
Goto Forum:
  


Current Time: Sat Dec 10 05:26:40 CST 2016

Total time taken to generate the page: 0.08898 seconds