Hierarchical Tree
I have the following tables (simplified for effect).
CREATE
TABLE
"CALCULATION"
( "ID" NUMBER(10,0),
"ROOT_ELEMENT_ID" NUMBER(10,0)
) ;
CREATE
TABLE
"CALCULATION_ELEMENT"
( "ID" NUMBER(10,0),
"NAME" VARCHAR2(255
CHAR
),
"PARENT_ID" NUMBER(10,0)
) ;
ALTER
TABLE
"CALCULATION"
ADD
CONSTRAINT
"FK_CALC_CALC_ELEM"
FOREIGN
KEY
("ROOT_ELEMENT_ID")
REFERENCES
"CALCULATION_ELEMENT" ("ID")
ON
DELETE
CASCADE
ENABLE;
ALTER
TABLE
"CALCULATION_ELEMENT"
ADD
CONSTRAINT
"FK_CALC_ELEM_PARENT"
FOREIGN
KEY
("PARENT_ID")
REFERENCES
"CALCULATION_ELEMENT" ("ID")
ON
DELETE
CASCADE
ENABLE;
What I would like to do is create a query/view that allows me to select all
calculation_elements for a particular calculation id. Ie the calculation points
me to the top of the tree of calculation_elements. The results should look
something like this:
0