Hi,
I am trying to create a view which shows inheritence but cannot get this
right. I have provided the table and data to assist anyone kind enough to
help us plus details of what we are trying to achieve.
CREATE TABLE GLEN_TEST (
TYPE VARCHAR2 (10),
PARENT VARCHAR2 (10),
PROPERTY NUMBER);

INSERT INTO GLEN_TEST ( TYPE, PARENT, PROPERTY ) VALUES ( 'A', NULL, 1);
INSERT INTO GLEN_TEST ( TYPE, PARENT, PROPERTY ) VALUES ( 'A', NULL, 2);
INSERT INTO GLEN_TEST ( TYPE, PARENT, PROPERTY ) VALUES ( 'B', 'A', 3);
INSERT INTO GLEN_TEST ( TYPE, PARENT, PROPERTY ) VALUES ( 'B', 'A', 4);
INSERT INTO GLEN_TEST ( TYPE, PARENT, PROPERTY ) VALUES ( 'C', 'B', 5);
INSERT INTO GLEN_TEST ( TYPE, PARENT, PROPERTY ) VALUES ( 'D', 'B', 6);
COMMIT;

We can see that this is a simple child-parent relationship.

If we do a select * we would get the following result:
TYPE PARENT PROPERTY
------- ------ ----------
A null 1
A null 2
B A 3
B A 4
C B 5
D B 6

What we are trying to achieve would look like this:
TYPE PARENT PROPERTY
------ ------ ----------
A null 1
A null 2
B A 1
B A 2
B null 3
B null 4
C A 1
C A 2
C B 3
C B 4
C null 5
D A 1
D A 2
D B 3
D B 4
D null 6

What we are trying to achieve is for every type to show all it's inheritance
i.e. type B inherits from type A because A is it's parent so the properties
for type B are 3 and 4 plus the properties for type A which are 1 and 2.
Type C would have it's own properties (5) plus those from type B (3,4) which
is it's parent plus those of type A (1,2) which is B's parent.

I have tried using the connect by prior but with no success as the
inheritence could be n layers deep. If anyone out there likes a challenge
and can help - we would be extremely grateful.
Regards
Glen

Search Google for "adjacency list model celko"


"GlenT" <glen.turner@bt.com> wrote in message
news:bf8cpn$5sh$1@pheidippides.axion.bt.co.uk...
> Hi,
> I am trying to create a view which shows inheritence but cannot get this
> right. I have provided the table and data to assist anyone kind enough to
> help us plus details of what we are trying to achieve.
> CREATE TABLE GLEN_TEST (
> TYPE VARCHAR2 (10),
> PARENT VARCHAR2 (10),
> PROPERTY NUMBER);
>
> INSERT INTO GLEN_TEST ( TYPE, PARENT, PROPERTY ) VALUES ( 'A', NULL, 1);
> INSERT INTO GLEN_TEST ( TYPE, PARENT, PROPERTY ) VALUES ( 'A', NULL, 2);
> INSERT INTO GLEN_TEST ( TYPE, PARENT, PROPERTY ) VALUES ( 'B', 'A', 3);
> INSERT INTO GLEN_TEST ( TYPE, PARENT, PROPERTY ) VALUES ( 'B', 'A', 4);
> INSERT INTO GLEN_TEST ( TYPE, PARENT, PROPERTY ) VALUES ( 'C', 'B', 5);
> INSERT INTO GLEN_TEST ( TYPE, PARENT, PROPERTY ) VALUES ( 'D', 'B', 6);
> COMMIT;
>
> We can see that this is a simple child-parent relationship.
>
> If we do a select * we would get the following result:
> TYPE PARENT PROPERTY
> ------- ------ ----------
> A null 1
> A null 2
> B A 3
> B A 4
> C B 5
> D B 6
>
> What we are trying to achieve would look like this:
> TYPE PARENT PROPERTY
> ------ ------ ----------
> A null 1
> A null 2
> B A 1
> B A 2
> B null 3
> B null 4
> C A 1
> C A 2
> C B 3
> C B 4
> C null 5
> D A 1
> D A 2
> D B 3
> D B 4
> D null 6
>
> What we are trying to achieve is for every type to show all it's
inheritance
> i.e. type B inherits from type A because A is it's parent so the
properties
> for type B are 3 and 4 plus the properties for type A which are 1 and 2.
> Type C would have it's own properties (5) plus those from type B (3,4)
which
> is it's parent plus those of type A (1,2) which is B's parent.
>
> I have tried using the connect by prior but with no success as the
> inheritence could be n layers deep. If anyone out there likes a challenge
> and can help - we would be extremely grateful.
> Regards
> Glen
>
>
>

Hi

I got the query for getting the inheritance of one element :

if you want to get it for D :

select
'D', parent.parent, glen_test.property
from
(
select distinct parent
from glen_test
where parent is not null
start with type='D'
connect by prior parent=type
) parent,
glen_test
where
parent.parent = glen_test.type
union
select
type, null, property
from
glen_test
where
type = 'D'

But I cannot get a query that will bring all the inheritance for all the
elements.

HTH,

Laly.





"GlenT" <glen.turner@bt.com> a écrit dans le message de news:
bf8cpn$5sh$1@pheidippides.axion.bt.co.uk...

> Hi,
> I am trying to create a view which shows inheritence but cannot get this
> right. I have provided the table and data to assist anyone kind enough to
> help us plus details of what we are trying to achieve.
> CREATE TABLE GLEN_TEST (
> TYPE VARCHAR2 (10),
> PARENT VARCHAR2 (10),
> PROPERTY NUMBER);
>
> INSERT INTO GLEN_TEST ( TYPE, PARENT, PROPERTY ) VALUES ( 'A', NULL, 1);
> INSERT INTO GLEN_TEST ( TYPE, PARENT, PROPERTY ) VALUES ( 'A', NULL, 2);
> INSERT INTO GLEN_TEST ( TYPE, PARENT, PROPERTY ) VALUES ( 'B', 'A', 3);
> INSERT INTO GLEN_TEST ( TYPE, PARENT, PROPERTY ) VALUES ( 'B', 'A', 4);
> INSERT INTO GLEN_TEST ( TYPE, PARENT, PROPERTY ) VALUES ( 'C', 'B', 5);
> INSERT INTO GLEN_TEST ( TYPE, PARENT, PROPERTY ) VALUES ( 'D', 'B', 6);
> COMMIT;
>
> We can see that this is a simple child-parent relationship.
>
> If we do a select * we would get the following result:
> TYPE PARENT PROPERTY
> ------- ------ ----------
> A null 1
> A null 2
> B A 3
> B A 4
> C B 5
> D B 6
>
> What we are trying to achieve would look like this:
> TYPE PARENT PROPERTY
> ------ ------ ----------
> A null 1
> A null 2
> B A 1
> B A 2
> B null 3
> B null 4
> C A 1
> C A 2
> C B 3
> C B 4
> C null 5
> D A 1
> D A 2
> D B 3
> D B 4
> D null 6
>
> What we are trying to achieve is for every type to show all it's
inheritance
> i.e. type B inherits from type A because A is it's parent so the
properties
> for type B are 3 and 4 plus the properties for type A which are 1 and 2.
> Type C would have it's own properties (5) plus those from type B (3,4)
which
> is it's parent plus those of type A (1,2) which is B's parent.
>
> I have tried using the connect by prior but with no success as the
> inheritence could be n layers deep. If anyone out there likes a challenge
> and can help - we would be extremely grateful.
> Regards
> Glen
>
>
>