Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Tree SQL query

Posted on 2002-03-26
8
Medium Priority
?
5,089 Views
Last Modified: 2012-06-21
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!
0
Comment
Question by:sergey_qw
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 22

Expert Comment

by:DrSQL
ID: 6896526
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
 
LVL 46

Expert Comment

by:aikimark
ID: 6904239
I'm a little confused.  Level doesn't appear to exist as a column in the GOODS table.
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 6904306
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 8

Expert Comment

by:drittich
ID: 6911949
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
 

Author Comment

by:sergey_qw
ID: 6912175
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
 
LVL 22

Accepted Solution

by:
DrSQL earned 200 total points
ID: 6912977
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
 
LVL 8

Expert Comment

by:drittich
ID: 6912997
So you want no more than 4 levels, where every level greater than 4 is moved up to level 4?
0
 
LVL 46

Expert Comment

by:aikimark
ID: 6913626
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
What we learned in Webroot's webinar on multi-vector protection.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question