Home » SQL & PL/SQL » SQL & PL/SQL » View issue / inheritance advice (merged)
icon5.gif  View issue / inheritance advice (merged) [message #233631] Fri, 27 April 2007 00:41 Go to next message
Power Penguin
Messages: 33
Registered: April 2007
Location: Australia
Member
I guys,

Im trying to create a view and some tables so I can use inheritance. I want everything from the Items table to be inherited by books and videos.

I think im on the right track, my tables are:

CREATE TABLE Books_P (Code number (12), Title varchar (25), StandardPrice number (2), MaxDaysBorrowed number (2), Category char (15), NoPages number (5),
CONSTRAINT PK_BOOKS_CODE PRIMARY KEY (Code),
CONSTRAINT FK_ITEM_CODE FOREIGN KEY (Code) REFERENCES Item (Code));



CREATE TABLE Videos_P (Title varchar (25), Description varchar (15), Rating varchar (3), Length number (1),
CONSTRAINT PK_CODE PRIMARY KEY (Code),
CONSTRAINT PK_TITLE_LENGTH PRIMARY KEY (Title, Length),
CONSTRAINT FK_ITEM_CODE FOREIGN KEY Item (Code));


CREATE TABLE Item (Code number (12), Title varchar (25), StandardPrice decimal (7,2), MaxDaysBorrowed number (2), Category char (15),
CONSTRAINT PK_CODE PRIMARY KEY (Code),
CONSTRAINT FK_CATEGORY_CATNAME FOREIGN KEY (Category) REFERENCES Category (CatName));


CREATE VIEW Videos (Code, Name, StandardPrice, MaxDaysBorrowed, Category, Title, Description, Rating Length)
SELECT i.code, i.name, i.standardprice, i.maxdaysborrowed, i.category, v.title, v.description, v.rating, v.length
FROM item i, videos v
WHERE i.name = v.title;


CREATE VIEW Videos (Code, Name, StandardPrice, MaxDaysBorrowed, Category, Title, NoPages)
SELECT i.code, i.name, i.standardprice, i.maxdaysborrowed, i.category, b.title, b.nopages
FROM item i, books b
WHERE i.name = b.title;




The error im getting with sqlplus on oracle 10 is

ERROR at line 4:
ORA-00904: "I"."NAME": invalid identifier




Any help would be great.

Thanks
Re: 'View' Issue [message #233635 is a reply to message #233631] Fri, 27 April 2007 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no column named "name" in table "item".

Regards
Michel
Inheritence advice :) [message #233910 is a reply to message #233631] Sat, 28 April 2007 02:16 Go to previous messageGo to next message
Power Penguin
Messages: 33
Registered: April 2007
Location: Australia
Member
Hi guys,

I have one table Item that I want as my super class and I want videos and Books to be able to inherit the values, im having trouble making this happen. Any advice and recomendations would be great.

Im using sqlplus and oracle 10.


This is what ive got so far.

CREATE TABLE Item (Code varchar (12), Name varchar (25), StandardPrice decimal (7,2), MaxDaysBorrowed number (2), Category char (15),
CONSTRAINT PK_CODE PRIMARY KEY (Code),
CONSTRAINT FK_CATEGORY_CATNAME FOREIGN KEY (Category) REFERENCES Category (CatName));





CREATE TABLE Books_P (Code varchar (12), Title varchar (25), StandardPrice number (2), MaxDaysBorrowed number (2), Category char (15), NoPages number (5),
CONSTRAINT PK_BOOKS_CODE PRIMARY KEY (Code),
CONSTRAINT FK_ITEM_CODE FOREIGN KEY (Code) REFERENCES Item (Code));





CREATE TABLE Videos_P (Code varchar (12), Title varchar (25), StandardPrice decimal (7,2), MaxDaysBorrowed number (2), Category char (15), Description varchar (15), Rating varchar (3), Length number (1),
CONSTRAINT PK_Videos_CODE PRIMARY KEY (Code),
CONSTRAINT FK_ITEM_CODE2 FOREIGN KEY (Code) REFERENCES Item (Code));




CREATE VIEW Videos (Code, Name, StandardPrice, MaxDaysBorrowed, Category, Title, Description, Rating, Length) AS
SELECT i.code, i.name, i.standardprice, i.maxdaysborrowed, i.category, v.title, v.description, v.rating, v.length
FROM Item i, Videos_p v
WHERE i.name = v.title;




CREATE VIEW Books (Code, Name, StandardPrice, MaxDaysBorrowed, Category, Title, NoPages) AS
SELECT i.code, i.name, i.standardprice, i.maxdaysborrowed, i.category, b.title, b.nopages
FROM item i, books_p b
WHERE i.name = b.title;






Thanks in advance.

[Updated on: Sat, 28 April 2007 02:17]

Report message to a moderator

Re: Inheritence advice :) [message #233912 is a reply to message #233910] Sat, 28 April 2007 02:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is the same issue as in your thread: http://www.orafaq.com/forum/m/233631/102589/#msg_233631
Please don't start a new topic, continue on the previous one.
Please break your lines, I don't have a 55" screen and I'm not able to read lines of 200 characters.

Regards
Michel
Re: Inheritence advice :) [message #233929 is a reply to message #233912] Sat, 28 April 2007 05:10 Go to previous message
Power Penguin
Messages: 33
Registered: April 2007
Location: Australia
Member
I know but the thing is im not sure if im on the right track making the inheritance.
Previous Topic: last wednesday in month
Next Topic: ODBC configuration?
Goto Forum:
  


Current Time: Sat Dec 10 10:44:57 CST 2016

Total time taken to generate the page: 0.10135 seconds