Good morning people,
I need some advice on two different topics related to recursion. I wonder what is the best way to tackle this kind of issue.
Imagine you have a hierarchical structure like this with an unlimited number of descendants.
drop table tree;
CREATE TABLE tree
(
node varchar2(100),
parent_node varchar2(100),
value DATE,
min_value DATE
);
INSERT INTO tree(node, parent_node, value) VALUES('root', null, TO_DATE('2000', 'YYYY'));
--
INSERT INTO tree(node, parent_node, value) VALUES('node-10', 'root', TO_DATE('2001', 'YYYY'));
INSERT INTO tree(node, parent_node, value) VALUES('node-11', 'root', TO_DATE('1999', 'YYYY'));
--
INSERT INTO tree(node, parent_node, value) VALUES('node-110', 'node-11', TO_DATE('2000', 'YYYY'));
First thing I would like to do is to derive for each node the min_value. The min value must be the minimum "value" of ALL descending nodes.
With the 3 lines scenario above:
- root = 1999 because somewhere in its descendants one value is 1999
- node-10 = 2001
- node-11 = 1999 because its descendants node (in this case node-110) none has a lower value.
The second thing is I would like to find all the nodes that don't have a correct min_Value. Like previously, if one node is bigger that parent, the identity it.
Any advice for the best solution?
Regards,