Home » SQL & PL/SQL » SQL & PL/SQL » Error "ORA-00957 Duplicate Column Names" while creating view
 Error "ORA-00957 Duplicate Column Names" while creating view [message #111014] |
Sat, 12 March 2005 02:33  |
S. M. Waqas Shabir
Messages: 30
Registered: January 2004
|
Member |
|
|
Hi
Please have a look at the database picture.
--------------------------------Scenario-------------------------
* A province has more than one districts.
* A Project can be initiated in many provinces & Districts.
* "Wing" can be understood as department. i.e Department (wing) can look after more than one project
* PStatus means status of the project either to be satisfactory or problematic
-----------------------------------------------------------------
I have designed the database according to this requirement (see picture).
When i create a view
(CREATE VIEW PROVINCE_DISTRICTS AS select DISTRICT.DESCRIPTION, PROVINCE.DESCRIPTION from DISTRICT, PROVINCE WITH READ ONLY) to get the province title and the corresponsing districts, the oracle says
"ORA-00957 Duplicate column names".
After viewing the picture do you see any normalization faults. If yes then please help me in not only normalizing it but also making the required quries.
Many Thanks in advance.
--------------------------------SCRIPT---------------------------
CREATE TABLE Wing
(
WingIDP varchar2(35),
Description varchar2(100),
CONSTRAINT Wing_PK PRIMARY KEY (WingIDP)
);
insert into wing values('1','UN');
insert into wing values('2','hin');
insert into wing values('3','WBA');
insert into wing values('4','IDBF');
insert into wing values('5','JPN');
insert into wing values('6','PCB');
insert into wing values('7','ECL');
insert into wing values('8','ADBP');
CREATE TABLE Province
(
ProvinceIDP varchar2(35),
Description varchar2(100),
CONSTRAINT Province_PK PRIMARY KEY (ProvinceIDP)
);
insert into province values('1','P');
insert into province values('2','S');
insert into province values('3','B');
insert into province values('4','NWFP');
insert into province values('5','FA');
CREATE TABLE District
(
ProvinceIDFP varchar2(35),
DistrictIDP varchar2(35),
Description varchar2(100),
CONSTRAINT District_PK PRIMARY KEY (ProvinceIDFP,DistrictIDP),
CONSTRAINT District_FK FOREIGN KEY (ProvinceIDFP) REFERENCES Province(ProvinceIDP)
);
insert into District values('1','1','RWP');
insert into District values('1','2','LHR');
insert into District values('2','1','KHI');
insert into District values('2','2','HBD');
insert into District values('3','1','QTA');
insert into District values('4','1','Pesh');
insert into District values('4','2','Atd');
insert into District values('5','1','Ibd');
CREATE TABLE PStatus
(
PStatusIDP varchar2(35),
Description varchar2(100),
CONSTRAINT PStatus_PK PRIMARY KEY (PStatusIDP)
);
insert into PStatus values('1','Satisfactory');
insert into PStatus values('2','Problematic');
CREATE TABLE Project
(
ProjectIDP varchar2(35),
ProjectTitle varchar2(200),
WingIDF varchar2(35),
ProjectBrief varchar2(2000),
PStatusIDF varchar2(35),
IsComplete varchar2(1),
CONSTRAINT Project_PK PRIMARY KEY (ProjectIDP),
CONSTRAINT Project_FK1 FOREIGN KEY (WingIDF) REFERENCES Wing(WingIDP),
CONSTRAINT Project_FK2 FOREIGN KEY (PStatusIDF) REFERENCES PStatus(PStatusIDP)
);
insert into Project(ProjectIDP,ProjectTitle,WingIDF,PStatusIDF) values('1','P1','1','1');
insert into Project(ProjectIDP,ProjectTitle,WingIDF,PStatusIDF) values('2','P2','2','1');
insert into Project(ProjectIDP,ProjectTitle,WingIDF,PStatusIDF) values('3','P3','1','1');
insert into Project(ProjectIDP,ProjectTitle,WingIDF,PStatusIDF) values('4','P4','3','2');
insert into Project(ProjectIDP,ProjectTitle,WingIDF,PStatusIDF) values('5','P5','4','2');
CREATE TABLE ProjectLocation
(
ProjectIDFP varchar2(35),
ProvinceIDFP varchar2(35),
DistrictIDFP varchar2(35),
CONSTRAINT ProjectLocation_PK PRIMARY KEY (ProjectIDFP,ProvinceIDFP,DistrictIDFP),
CONSTRAINT ProjectLocation_FK1 FOREIGN KEY (ProjectIDFP) REFERENCES Project(ProjectIDP),
CONSTRAINT ProjectLocation_FK2 FOREIGN KEY (ProvinceIDFP) REFERENCES Province(ProvinceIDP)
);
insert into ProjectLocation values('1','1','1');
insert into ProjectLocation values('1','2','2');
insert into ProjectLocation values('2','5','1');
insert into ProjectLocation values('3','1','2');
insert into ProjectLocation values('4','3','1');
insert into ProjectLocation values('5','1','1');
insert into ProjectLocation values('5','1','2');
insert into ProjectLocation values('5','2','1');
insert into ProjectLocation values('5','2','2');
insert into ProjectLocation values('5','3','1');
insert into ProjectLocation values('5','4','1');
insert into ProjectLocation values('5','4','2');
insert into ProjectLocation values('5','5','1');
-----------------------------------------------------------------
[Updated on: Sat, 12 March 2005 05:12] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 02 16:58:02 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
