I have the following table:
create table geometry_sample(geo_id number, geo_parent_id number, geo_path varchar2(512), geo_value varchar2(512));
with the following values:
insert into geometry_sample values (101, null, '/geometries', 'geometries');
insert into geometry_sample values (102, 101, '/geometries/shapes', 'shapes');
insert into geometry_sample values (103, 102, '/geometries/shapes/boxes', 'boxes');
insert into geometry_sample values (104, 102, '/geometries/shapes/curves', null);
insert into geometry_sample values (105, 103, '/geometries/shapes/boxes/rectangles', null);
insert into geometry_sample values (106, 104, '/geometries/shapes/curves/arc', null);
commit;
How do I get the values to be 'inherited' by the children if they don't have a value, so that the result looks like the following (the values marked in bold are inherited from their parents - if the immediate parent doesn't have a value, it should 'climb' up the ladder) - it is not known how many levels the hierarchy will have - but we can safely assume that it will never go beyond 50, if that is useful information. However if the solution doesn't care about the number of levels, that's the most ideal.
101 /geometries geometries
102 101 /geometries/shapes shapes
103 102 /geometries/shapes/boxes boxes
104 102 /geometries/shapes/curves
shapes105 103 /geometries/shapes/boxes/rectangles
boxes106 104 /geometries/shapes/curves/arc
shapesThank you!