Solved

Tree SQL query

Posted on 2002-03-26
8
5,080 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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 …
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…
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…

617 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