I have the following types and tables:
CREATE TYPE person AS OBJECT (
CREATE TYPE runner UNDER person (
CREATE TYPE race AS OBJECT (
CREATE TYPE participation UNDER race(
CREATE TABLE participations OF participation;
CREATE VIEW AvgTime10k OF PARTICIPATION WITH OBJECT IDENTIFIER (runner_id, year, time) AS
SELECT runner_id, time FROM PARTICIPATIONS WHERE DISTANCE = '10';
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.
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.