CODEWITHSUNDEEP
sqloracle-database
Joanmacat
Joanmacat

Reputation: 1541

Create view in SQL with object identifier

I have the following types and tables:

CREATE TYPE person AS OBJECT (
dni VARCHAR2(10),
name VARCHAR2(30),
surname VARCHAR2(100)
)NOT FINAL;

CREATE TYPE runner UNDER person (
runningClub VARCHAR2(100)
);

CREATE TYPE race AS OBJECT (
name VARCHAR2(50),
city VARCHAR2(50),
distance INTEGER
)NOT FINAL;

CREATE TYPE participation UNDER race(
    runner_id runner,
    year VARCHAR(4),
    time INTEGER
);

CREATE TABLE participations OF participation;

And now I need to create a VIEW and I tried this:

CREATE VIEW AvgTime10k OF PARTICIPATION WITH OBJECT IDENTIFIER (runner_id, year, time) AS
SELECT runner_id, time FROM PARTICIPATIONS WHERE DISTANCE = '10';

But the error I get is: Error SQL: ORA-01730: invalid number of column names specified

Anybody knows why? It's my first time with object-relational SQL and this is struggling me a lot. Thanks!

Upvotes: 2

Views: 1980

Answers (3)

Ignacio
Ignacio

Reputation: 21

Object views are used when you want to access object information trough a relational table.

I think that race and runner should have their own object type and object table, but participation must be a relational table. For example:

CREATE TYPE race_t AS OBJECT (
 ra_name VARCHAR(30),
 ....
);

CREATE TABLE race_o of race_t (ra_name PRIMARY KEY)
OBJECT IDENTIFIER IS PRIMARY KEY;

CREATE TYPE runner_t AS OBJECT (
ru_dni VARCHAR(10),
...
);

CREATE TABLE runner_o of runner_t (ru_dni PRIMARY KEY)
OBJECT IDENTIFIER IS PRIMARY KEY;

Also you must create a view for people that has made distance of 10. Km, miles?

You will need a relational table to use the object view. Also you will need a type for the view with the same atributes as your view

This will be the relational table

CREATE TABLE PARTICIPACIONS (
--You must use a REF with SCOPE for race, and a REF with SCOPE for runner
...
);

Object view to fill the view

CREATE TYPE AvgTime AS Object (
avg_runner_id VARCHAR(30),
avg_time      INTEGER
);

Object view for distance=10. Please note that you will need the same atributes and order in your select as in your object type, but not in the object identifier

CREATE VIEW AvgTime10k OF PARTICIPATION WITH OBJECT IDENTIFIER (avg_runner_id) AS
SELECT p.runner_id, p. time FROM PARTICIPATIONS P WHERE DISTANCE = '10';
)

Remember, you use the view to see objects trough a relational table.

Upvotes: 2

Alex Poole
Alex Poole

Reputation: 191265

Your query needs to get a column value corresponding to each field in the object type you're using, including those from the parent type:

CREATE VIEW AvgTime10k OF PARTICIPATION WITH OBJECT IDENTIFIER (name, city, distance) AS
SELECT name, city, distance, runner_id, year, time
FROM PARTICIPATIONS WHERE DISTANCE = '10';

View AVGTIME10K created.

However, from the documentation:

Use the WITH OBJECT IDENTIFIER clause to specify a top-level (root) object view. This clause lets you specify the attributes of the object type that will be used as a key to identify each row in the object view. In most cases these attributes correspond to the primary key columns of the base table. You must ensure that the attribute list is unique and identifies exactly one row in the view.

The name, city, distance I've used doesn't uniquely identify an object, and all runners from a race will get the same object_id pseudocolumn value. You can add year, time to that, which will help unless two people get exactly the same time (which happens, of course); but you can't add runner_id - you'd get ORA-22971: invalid datatype for PRIMARY KEY-based object identifier.

Upvotes: 2

Ritesh Patel
Ritesh Patel

Reputation: 315

Hi Please match column it's simple

CREATE VIEW AvgTime10k OF PARTICIPATION WITH OBJECT IDENTIFIER (runner_id, year, time) AS
SELECT runner_id, year, time FROM PARTICIPATIONS WHERE DISTANCE = '10';

Upvotes: 0

Related Questions