Solved

Tree SQL query

Posted on 2002-03-26
8
5,072 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 45

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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 50 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 45

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 Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Has anyone used domo? 1 39
#deleted rowsc access 2010 backend 4 33
query returning everything 11 68
SQL BACKUP - 2008 R2 8 5
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now