SQL & PL/SQL

Hello guys,

I have a small issue but I can't think of an idea to solve it... Can any of you point me to the right direction?

Imagine the following data:

DROP TABLE dpt_def;
DROP TABLE dpts;
CREATE TABLE dpt_def
(
  dpt_id NUMBER(10), 
  dpt_name VARCHAR2(100), 
  dpt_max_levels NUMBER(10) -- always bigger or equals to 1
);
INSERT INTO dpt_def(dpt_id, dpt_name, dpt_max_levels) VALUES(1, 'research', 3);
INSERT INTO dpt_def(dpt_id, dpt_name, dpt_max_levels) VALUES(2, 'hr', 2);
COMMIT;
CREATE TABLE dpts
(
  dpt_id NUMBER(10), 
  dpt_parent_id NUMBER(10), 
  dpt_name VARCHAR2(100)
);
INSERT INTO dpts(dpt_id, dpt_parent_id, dpt_name) VALUES(1, NULL, 'research');
INSERT INTO dpts(dpt_id, dpt_parent_id, dpt_name) VALUES(20, 1, 'research.001');
INSERT INTO dpts(dpt_id, dpt_parent_id, dpt_name) VALUES(18, 20, 'research.001.a');

INSERT INTO dpts(dpt_id, dpt_parent_id, dpt_name) VALUES(19, 20, 'research.001.b');
INSERT INTO dpts(dpt_id, dpt_parent_id, dpt_name) VALUES(27, 19, 'research.001.b.a');
INSERT INTO dpts(dpt_id, dpt_parent_id, dpt_name) VALUES(2, NULL, 'hr');
INSERT INTO dpts(dpt_id, dpt_parent_id, dpt_name) VALUES(32, 2, 'hr.eur');
INSERT INTO dpts(dpt_id, dpt_parent_id, dpt_name) VALUES(33, 32, 'hr.eur.ger');
COMMIT;

Basically, I have two tables; the first one contains the definition of a department and the second tables contains the list of different departments. [My problem is a little more complex than that, but I'm trying to keep it simple].

In the first table, I have a field defining the number of valid level of a department. What I would like to do, is to count or to have a flag saying that I have valid descending node.

I did the following query:

WITH get_dpt_tree(dpt_id, dpt_parent_id, dpt_name, lvl, is_valid_level, max_levels) AS
(
  SELECT d.dpt_id, s.dpt_parent_id, d.dpt_name, 1, 
         1 AS is_valid_level, 
         d.dpt_max_levels
    FROM dpt_def d
   INNER JOIN dpts s ON d.dpt_id = s.dpt_id
   UNION ALL
  SELECT d.dpt_id, d.dpt_parent_id, d.dpt_name, lvl + 1, 
         CASE WHEN lvl + 1 <= t.max_levels THEN 1 ELSE 0 END AS is_valid_level, 
         t.max_levels
    FROM get_dpt_tree t 
   INNER JOIN dpts d ON t.dpt_id = d.dpt_parent_id
) SEARCH DEPTH FIRST BY dpt_parent_id SET rnk
SELECT dpt_id, dpt_parent_id, dpt_name, lvl, is_valid_level
  FROM get_dpt_tree;

It returns this:

Does anyone has an idea on how I can proceed to have such a result? Also, if it's doable to count on each node how many valid/invalid direct child node, it would be great.

For info, I'm using Oracle 11.2;

Thank you very much,