Well, this problem will be a little hard to explain, but here it goes.
I have a query that retrieves data that is displayed in a tree, such as a visualization. The yloc calculated in this query is used to locate y to display specific objects attached to these capabilities.
Just using MAX (coalesce (CAP_TREE2.depth), 0) does not work. So I wrote an algorithm that shows that I need yloc in certain situations.
Here is the algorithm:
if (capability has c2 !=1) { yloc = 0 } else if (capability is child (at any depth) of capability with c2 != 1) { yloc = depth - (depth of first parent with c2 != 1) } else { y = depth }
NOTE. c2 represents the child number (in alphabetical order). Therefore, if the function has two children, "A" and "B". "A" will have c2 = 1, and "B" will have c2 = 2.
I had a lot of problems trying to implement this algorithm in SQL (Oracle 11g R2). The problem I encountered ends up in an else if in my algorithm. I was able to get all the opportunities that are children (at any depth) of the possibilities with c2! = 1.
However, the part that I could not finish is yloc = depth - (depth of the first parent with c2! = 1)
Can someone PLEASE show me how to get the "depth of the first parent with c2! = 1"? My request would finally be complete if I could get this value.
I will show what values I expect from my data for this, in order to try to make it more understandable.
"Investment management" the first parent with c2! = 1 is "FPP" with a depth of 2. "Third-party management product" the first parent with c2! = 1 is "FPP" with a depth of 2. "First order control (PI)" with c2! = 1 - "Operations and Services (PI)" with a depth of 2.
Note. Sorry, I cannot change the circuit.
Here is what I have so far. The bottom selection operation is where I work. http://sqlfiddle.com/#!4/55b5a/116
SQL schema:
CREATE TABLE capability ( id int, parent_id int, name varchar(200) ) ; CREATE TABLE tree ( descendantid int, ancestorid int, depth int ) ; INSERT INTO capability (id, parent_id, name) VALUES (1, -1, 'BU-Specific Capabilities'); INSERT INTO capability (id, parent_id, name) VALUES (2, 1, 'PI Capability Model'); INSERT INTO capability (id, parent_id, name) VALUES (3, 2, 'Core Business Processing (PI)'); INSERT INTO capability (id, parent_id, name) VALUES (4, 3, 'Institutional Trust Administration (PI)'); INSERT INTO capability (id, parent_id, name) VALUES (5, 2, 'FPP'); INSERT INTO capability (id, parent_id, name) VALUES (6, 5, 'Investment Management'); INSERT INTO capability (id, parent_id, name) VALUES (7, 6, '3rd party Product Management'); INSERT INTO capability (id, parent_id, name) VALUES (8, 2, 'Operations and Shared Services (PI)'); INSERT INTO capability (id, parent_id, name) VALUES (9, 8, 'Order Management (PI)'); INSERT INTO capability (id, parent_id, name) VALUES (10, 8, 'Settlements (PI)'); INSERT INTO capability (id, parent_id, name) VALUES (11, -1, 'Common Core Capabilities'); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (1, 1, 0); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (2, 1, 1); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (2, 2, 0); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (3, 1, 2); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (3, 2, 1); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (3, 3, 0); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (4, 1, 3); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (4, 2, 2); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (4, 3, 1); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (4, 4, 0); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (5, 1, 2); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (5, 2, 1); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (5, 5, 0); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (6, 1, 3); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (6, 2, 2); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (6, 5, 1); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (6, 6, 0); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (7, 1, 4); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (7, 2, 3); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (7, 5, 2); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (7, 6, 1); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (7, 7, 0); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (8, 1, 2); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (8, 2, 1); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (8, 8, 0); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (9, 1, 3); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (9, 2, 2); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (9, 8, 1); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (9, 9, 0); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (10, 1, 3); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (10, 2, 2); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (10, 8, 1); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (10, 10, 0); INSERT INTO tree (descendantid, ancestorid, depth) VALUES (11, 11, 0);
SQL query:
SELECT cap.name, max(coalesce(CAP_TREE2.depth,0)) as ydepth, CASE WHEN cap.parent_id != -1 THEN DENSE_RANK() OVER (PARTITION BY cap.parent_id ORDER BY cap.name) --child number ELSE 1 END as c2 FROM capability cap INNER JOIN tree CAP_TREE2 ON CAP.id = CAP_TREE2.descendantid group by cap.name, cap.id, cap.parent_id ; WITH ids(id, c2) AS ( -- Find the ids and the ranks SELECT id, CASE WHEN parent_id = -1 THEN 1 ELSE DENSE_RANK() OVER (PARTITION BY parent_id ORDER BY name) --child number END as c2 FROM capability ), t(id, depth) AS ( SELECT id, 0 AS depth FROM ids WHERE c2 != 1 -- Take only ranks not equal to one UNION ALL SELECT c.id, depth+1 FROM capability c JOIN t ON (c.parent_id = t.id) -- Tree-walking ) --SELECT DISTINCT cap.name, t.depth --SELECT DISTINCT cap.name, min(t.depth) --FROM capability cap JOIN t USING(id) --group by cap.name SELECT cap.name, CASE -- cap has childNumber != 1 WHEN DENSE_RANK() OVER (PARTITION BY cap.parent_id ORDER BY cap.name) != 1 THEN 0 -- y = 0 -- cap is child of cap with childNumber != 1 WHEN cap.name IN (SELECT DISTINCT capa.name FROM capability capa JOIN t USING(id)) THEN max(coalesce(CAP_TREE2.depth,0)) -- y = depth - (depth of first parent with childNumber != 1) ELSE max(coalesce(CAP_TREE2.depth,0)) -- y = depth END as yloc FROM capability cap INNER JOIN tree CAP_TREE2 ON CAP.id = CAP_TREE2.descendantid group by cap.name, cap.id, cap.parent_id
Also here is my desired result
NAME DEPTH C2 YLOC BU-Specific Capabilities 0 1 0 --yloc=depth Common Core Capabilities 0 1 0 --yloc=depth PI Capability Model 1 1 1 --yloc=depth Core Business Processing (PI) 2 1 2 --yloc=depth FPP 2 2 0 --yloc=0 Operations and Shared Services (PI) 2 3 0 --yloc=0 Institutional Trust Administration (PI) 3 1 3 --yloc=depth Investment Management 3 1 1 --yloc=depth - (depth of FPP) 3rd party Product Management 4 1 2 --yloc=depth - (depth of FPP) Order Management (PI) 3 1 1 --yloc=depth -(depth of Operations and..) Settlements (PI) 3 2 0 --yloc=0