Tree SQL query

Hi!

I have a table GOODS, for example:
ID            
UP_ID          
TYPE_ID        
NAME          

where UP_ID refer to ID.

I can build a tree with query:

SELECT ID, UP_ID, TYPE_ID, NAME, level
FROM GOODS where id<>522
CONNECT BY PRIOR ID = up_id and id not in (2805,10048,14178,14426,14356,16920)
START WITH id=522

sometimes level exceed 7.

If i build a tree like that i will have branches that fork too much. i would
like them to fork no more then three times. for that i will need to somehow manage it.

I need for report all branches with level>3 were placed in this third-level branch.

Example

AAA
    BBB
     CCC
            DDD
          EEE              
              FFF                    
               GGG

like that:

AAA
    BBB
     CCC
        DDD
     EEE              
     FFF                    
     GGG

Thank you!
sergey_qwAsked:
Who is Participating?
 
DrSQLConnect With a Mentor Commented:
sergey,
   I would have thought that my query would yield the results you wanted (based on your follow-up I'd change the 3 to a 4 and the name of the column to LEVELS, since it's a reserved word), but if you want to repoint your parent (temporarily - for the CONNECT-BY) then you'll need a tree-walk function to find the ancestor whose level is 3 for all levels above 4 (since level 4's already point to a level 3 per your follow-up).

That requires a reverse CONNECT BY/PRIOR starting with the current node.  I've adapted a function I use (using ORACLE syntax) that will do that:

create or replace function goods_ancestor(offspring number, ancestral_level number) return number is
   ancestor number;
begin
   select parental_unit into ancestor
     from (select case when level = ancestral_level then ID else -1 end as parental_unit
            from GOODS connect by prior up_id = id start with offspring)
    where parental_unit != -1;
   return(ancestor);
end;

You may not have a case statement, but I'm sure you have an equivalent.  Then, to use it in your query in place of UPD_ID:

SELECT ID, case when sign(level - 4) = -1
                then goods_ancestor(id,level - 2)
                else UP_ID END as UP_ID, TYPE_ID, NAME, least(level,3) levels
FROM GOODS
CONNECT BY PRIOR ID = up_id and id not in (2805,10048,14178,14426,14356,16920)
START WITH id=522;

This will still DO the connections above level 3, but it'll always REPORT a level 3 connection in UP_ID and LEVELS will never be greater than 3.

Good luck!
0
 
DrSQLCommented:
sergey,

   The easiest way is to modify the "level":

SELECT ID, UP_ID, TYPE_ID, NAME, least(level,3) level
FROM GOODS where id<>522
CONNECT BY PRIOR ID = up_id and id not in (2805,10048,14178,14426,14356,16920)
START WITH id=522;

or, if your database doesn't have LEAST:

SELECT ID, UP_ID, TYPE_ID, NAME,
       case when level < 3 then level else 3 end level
FROM GOODS where id<>522
CONNECT BY PRIOR ID = up_id and id not in (2805,10048,14178,14426,14356,16920)
START WITH id=522;

You get the idea.

Good luck!

0
 
aikimarkCommented:
I'm a little confused.  Level doesn't appear to exist as a column in the GOODS table.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
DrSQLCommented:
aikimark,
   LEVEL is a pseudo column that is used in connect by/prior sql commands to allow the query to manipulate the data based on the depth (like indenting).
0
 
drittichCommented:
Can you do:

SELECT ID, UP_ID, TYPE_ID, NAME, level
FROM GOODS where id<>522
WHERE LEVEL < 4
CONNECT BY PRIOR ID = up_id and id not in (2805,10048,14178,14426,14356,16920)
START WITH id=522

UNION

SELECT ID, UP_ID, TYPE_ID, NAME, 3
FROM GOODS where id<>522
WHERE LEVEL > 3
CONNECT BY PRIOR ID = up_id and id not in (2805,10048,14178,14426,14356,16920)
START WITH id=522

0
 
sergey_qwAuthor Commented:
Yes I can do that, but it's not the solution.
I think, I dont describe my problem precisely.

I dont use level at all. All I need is update UP_ID field for rows with level>3 to reconect this rows to branches with level=3.

for example I have this rows:
ID     UP_ID     NAME       LEVEL
13188     12519     HT 7770AT   3
13189     12666     HL 7770RD   6

I need update UP_ID for second row to 13188 in this branch of tree. But every branch is different.

ID     UP_ID     NAME       LEVEL
13188     12519     HT 7770AT   3
13189     13188     HL 7770RD   4
0
 
drittichCommented:
So you want no more than 4 levels, where every level greater than 4 is moved up to level 4?
0
 
aikimarkCommented:
maybe I should ask...
1. Is the problem the depth of branches in your tree?
2. How will your users know that a node is actually parented differently than shown in your tree control?

There are some design issues I'd like cleared up.
0
All Courses

From novice to tech pro — start learning today.