Hi
I have a table with self-relationship , among other columns I have the ID, PARENT_ID and one that I will call T_P
The column T_P is
'T' Typed
'P' Processed
Sometimes it is necessary to delete a node and all its children, but if there is any line with T_P equal to 'P' among the children of this node, it can not be deleted (nor the children)
On the other hand, if it has an ancestor with T_P equal to 'P', it can not be deleted either
At first I thought about counting to see if there were any knots in the above conditions, but I packed the count of only the children and still without looking at the conditions of column T_P
with tbl_process as (
select 1 id , null parent_id , 'T' T_P FROM DUAL UNION ALL
select 2, NULL,'T' from dual union all
select 3, 1 ,'T' from dual union all
select 4, 3 ,'T' from dual union all
select 5, 4 ,'T' from dual union all
select 6, 5, 'P' from dual union all
select 7, 6, 'T' from dual union all
select 8, 7, 'T' from dual union all
select 9, 2, 'T' from dual union all
select 10, 9, 'T' from dual)
select link, count(*) as "RESULT COUNT"
from (
select connect_by_root(id) link
from tbl_process
connect by parent_id = prior id
start with id = 1)
group by link
order by 1 asc
In example I can not to delete row with ID equal 1 because the ID = 6 have 'P' (1-->3-->4-->5-->6)
Too can not do delete row with ID equal 8 (or 7) because the ID = 6 have ''P' (6-->7-->8)
How can I to count if exists some with status equal 'P' when start with any ID ?
TIA