Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Tree SQL query

Posted on 2002-03-26
8
Medium Priority
?
5,085 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

670 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