create table person_description (
id integer not null primary key,
prename varchar2(100),
name varchar2(100),
some varchar2(100),
other varchar2(100),
really varchar2(100),
important varchar2(100),
information varchar2(100)
)Table created.
insert into person_description values ( 1, 'Luke', 'Skywalker', 'It''s', 'hard', 'to', 'create', 'testdata')1 row(s) inserted.
insert into person_description values ( 2, 'Anakin', 'Skywalker', 'It''s', 'hard', 'to', 'create', 'testdata')1 row(s) inserted.
insert into person_description values ( 3, 'Exar', 'Kun', 'It''s', 'hard', 'to', 'create', 'testdata')1 row(s) inserted.
create table jedi (
id integer not null primary key,
title varchar2(100),
fk_description integer not null,
constraint jedi_fk_desc foreign key ( fk_description ) references person_description ( id )
)Table created.
insert into jedi values (1, 'Master', 1)1 row(s) inserted.
insert into jedi values (2, 'Padawan', 2)1 row(s) inserted.
create table sith (
id integer not null primary key,
title varchar2(100),
fk_description integer not null,
constraint sith_fk_desc foreign key ( fk_description ) references person_description ( id )
)Table created.
insert into sith values (1, 'Darth', 2)1 row(s) inserted.
insert into sith values (2, 'Darth', 3)1 row(s) inserted.
create or replace view all_sith_and_jedi as
with info as (
select
id description_id,
prename,
name,
some,
other,
information
from
person_description)
select
j.id, j.title, i.*
from
jedi j
inner join info i on j.fk_description = i.description_id
union all
select
s.id, s.title, i.*
from
sith s
inner join info i on s.fk_description = i.description_idView created.
select * from all_sith_and_jedi| ID | TITLE | DESCRIPTION_ID | PRENAME | NAME | SOME | OTHER | INFORMATION | 1 | Master | 1 | Luke | Skywalker | It's | hard | testdata | 2 | Padawan | 2 | Anakin | Skywalker | It's | hard | testdata | 1 | Darth | 2 | Anakin | Skywalker | It's | hard | testdata | 2 | Darth | 3 | Exar | Kun | It's | hard | testdata |
|---|
select * from user_views where view_name = 'ALL_SITH_AND_JEDI'| VIEW_NAME | TEXT_LENGTH | TEXT | TEXT_VC | TYPE_TEXT_LENGTH | TYPE_TEXT | OID_TEXT_LENGTH | OID_TEXT | VIEW_TYPE_OWNER | VIEW_TYPE | SUPERVIEW_NAME | EDITIONING_VIEW | READ_ONLY | CONTAINER_DATA | BEQUEATH | ORIGIN_CON_ID | DEFAULT_COLLATION | CONTAINERS_DEFAULT | CONTAINER_MAP | EXTENDED_DATA_LINK | EXTENDED_DATA_LINK_MAP | HAS_SENSITIVE_COLUMN | ALL_SITH_AND_JEDI | 557 | with info as ( select id description_id, prename, name, some, other, information from person_description) select j.id, j.title, i."DESCRIPTION_ID",i."PRENAME",i."NAME",i."SOME",i."OTHER",i."INFORMATION" from jedi j inner join info i on j.fk_description = i.description_id union all select s.id, s.title, i."DESCRIPTION_ID",i."PRENAME",i."NAME",i."SOME",i."OTHER",i."INFORMATION" from sith s inner join info i on s.fk_description = i.description_id | with info as ( select id description_id, prename, name, some, other, information from person_description) select j.id, j.title, i."DESCRIPTION_ID",i."PRENAME",i."NAME",i."SOME",i."OTHER",i."INFORMATION" from jedi j inner join info i on j.fk_description = i.description_id union all select s.id, s.title, i."DESCRIPTION_ID",i."PRENAME",i."NAME",i."SOME",i."OTHER",i."INFORMATION" from sith s inner join info i on s.fk_description = i.description_id | - | - | - | - | - | - | - | N | N | N | DEFINER | 3 | USING_NLS_COMP | NO | NO | NO | NO | NO |
|---|