Demo Demo - 3 months ago 11
SQL Question

Evaluate a hierarchical query based on results from lower levels

I'm using Oracle Database 11g and bellow is a dummy table with dummy values, that I now try to explain:

I have a table that describes a relationship between IDs that represent "groups" and IDs that are the group's members. Members of the groups can be both simple members or some of the groups themselves (without cycles). As a result, some groups are simple groups of 1 level while some could have potentially many levels. Each group has a "minimum limit" value and each group member is of some value including a group when it acts as a member of that group.

Now, what I'm trying to do is to simply check, wheter a sum of groups member values is equal or greater than the minimum limit value of the group. That would be fairly easy if it wasn't of the hierarchy. The problem is that if one of the members is a group I can count with its member value ONLY if itself has enough member values to satisfy its minimum limit. So the evaluation needs to go from the leaf to root so that the lowest group is evaluated first and based on that are evaluated the higher levels of the hierarchy.

Unfortunately I wasn't able to figure it out. Can somebody help?
Important note: I would like to solve this without using R/CTE if possible. I can't accept any answer using CREATE keyword as I'm restricted to use it.

CREATE TABLE "MYGROUPS"
(
"MYGROUP_ID" VARCHAR2(20 BYTE),
"MYGROUP_LIMIT" Number,
"MEMBER" VARCHAR2(20 BYTE),
"MEMBER_VALUE" Number
);

insert into mygroups
(Select 'g0' ,1 ,'00' ,1 from dual) union
(Select 'g1' ,5 ,'01' ,1 from dual) union
(Select 'g1' ,5 ,'02' ,1 from dual) union
(Select 'g1' ,5 ,'03' ,1 from dual) union
(Select 'g1' ,5 ,'g2' ,3 from dual) union
(Select 'g2' ,3 ,'02' ,2 from dual) union
(Select 'g2' ,3 ,'05' ,2 from dual) union
(Select 'g2' ,3 ,'g3' ,2 from dual) union
(Select 'g3' ,5 ,'03' ,1 from dual) union
(Select 'g3' ,5 ,'05' ,1 from dual)


This set of data should result in g1 OK, g2 OK, g3 NOT OK. G1 is dependent on g2, which in turn is not dependent on g3 to be OK and so the g1 is OK as well.

In this alternative set of data:

insert into mygroups
(Select 'g0' ,1 ,'00' ,1 from dual) union
(Select 'g1' ,5 ,'01' ,1 from dual) union
(Select 'g1' ,5 ,'02' ,1 from dual) union
(Select 'g1' ,5 ,'03' ,1 from dual) union
(Select 'g1' ,5 ,'g2' ,3 from dual) union
(Select 'g2' ,3 ,'02' ,1 from dual) union
(Select 'g2' ,3 ,'05' ,1 from dual) union
(Select 'g2' ,3 ,'g3' ,2 from dual) union
(Select 'g3' ,5 ,'03' ,1 from dual) union
(Select 'g3' ,5 ,'05' ,1 from dual)


G1 is NOT OK, because it depends on g2 which here is also dependent on g3 and so all three here result in NOT OK

Here's a query to get the idea. The 'OK' values evaluated the group as satisfying the minimum limit. 'NOT OK' is the opposite. 'DUNNO' is the problem where I don't know how I could evaluate it.

select connect_by_root mygroup_id as root, mygroups.*,level
from ( Select mygroups.*,
sum(member_value) over (partition by mygroup_id) sum_of_values,
CASE
WHEN sum (CASE WHEN member like 'g%' THEN 1 END) over (partition by mygroup_id) > 0 THEN 'DUNNO'
WHEN sum(member_value) over (partition by mygroup_id) >= mygroup_limit THEN 'OK'
WHEN sum(member_value) over (partition by mygroup_id) < mygroup_limit THEN 'NOT OK'
END eval
From mygroups ) mygroups
connect by prior member = mygroup_id


Also, any of the groups can contain any number of different groups over any number of levels and the member-groups can possess different member_values in different groups.

Answer

I tried to solve the problem with the "connect by". But I not find a way to pass the result of calculation to the next level of recursion.

With R/CTE encountered similar problems. And in addition were strong limitations due to the inability to use "group" and "window" functions in R/CTE. Attempts to use the "CTE alias" twice in recursive part (for join previous row) leads to ORA-06000 (internal server error) and drop connection to Oracle.

Today, it occurred to me to provide "R/CTE" recurive loop with help of a single row (hereinafter: "cursor row"), and to keep a state (list of OK-groups) in this "cursor row". Necessary data rows, numbered in advance in the order "from the leaves to the root", joined at each iteration to "cursor row" by row number.

with GRP as (
     select A.*, row_number() over (order by L) N
       from (
         select mygroup_id, min(level) L
           from  mygroups m
          start with not exists(select 1 from mygroups m2
                                 where m2.mygroup_id=m.mygroup_id and m2.member like 'g%'
                               )
        connect by member=prior mygroup_id
          group by mygroup_id
       ) A
),
Cursor_tab(mygroup_id,N,list,result) as(
   select NULL,N,lpad(',',2000),0
     from GRP where N=1
 union all
   select G.mygroup_id,Q.N+1,
          ltrim(Q.list)||decode(R.column_value,0,'',G.mygroup_id||','),
          TO_NUMBER(R.column_value)
     from Cursor_tab Q, GRP G,
          table(cast(multiset(
           select decode(sign(sum(member_value)-min(mygroup_limit)),-1,0,1)
             from mygroups m
            where m.mygroup_id=G.mygroup_id
              and (member not like 'g%' or Q.list like '%,'||member||',%')
          ) as sys.odcivarchar2list)) R
    where G.N=Q.N
)
select mygroup_id, decode(result,1,'OK','NOT OK')
  from Cursor_tab
 where mygroup_id is not null