Joanmacat Joanmacat - 2 months ago 23
SQL Question

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!

Answer

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.