Solved

URGENT: SQL Procedure With Nested Loops For Hierarchical Data Access  WILL AWARD BONUS POINTS If answer is accepted!

Posted on 2004-08-17
7
352 Views
Last Modified: 2006-11-17
Ok, let me try and explain the situation-- I have a database that stores parts, circuit cards, LRUs, systems, etc... It is stored in a hierarchical format for example: (Note: NHA is the next higher assemble, or what the part belongs to)

ID   Part       NHA        ModuleType
1     ABC      XY123     1
2     XY123  AB221      2
3     AB221  GH123      3      


The part type is identified by a ModuleType, this tells me if it is a Part ( ModuleType=1), a Circuit Card (ModuleType =2), a System (ModuleType=3), etc… There are about 20 different ones. A part(ModType=1) is the base level—nothing is ever attached to a part.

The need:
I need to be able to run  procedure that will return a System (ModType=3) and all of the Circuit Cards and Parts that belong on it—ignoring all other Module Types. I will show a Treeview example below, the show what I need from it:

(Module Types are in Parenthesis)

MainSystemA (3)
---LRUA (5)
------PartA(1)
------PartB(1)
---CircuitCardA(2)
------PartX(1)
------PartV(1)
---LRUB (5)
------CircuitCardB(2)
---------Part1T (1)
---------Part2T (1)

I need this returned:
MainSystemA
---CircuitCardA
------PartX(1)
------PartV(1)
---CircuitCardB(2)
------Part1T (1)
------Part2T (1)

Basically I want to be able to tell the system which module type to return, then all of the parts that belong to that module type—no matter how deep they are in the nest—This is what I am using now to return ALL records and their structure:

ALTER       PROC ShowHierarchy
(
      @Root int
)
AS
BEGIN
      SET NOCOUNT ON
      DECLARE @ID  int, @Mod int, @PN  varchar(50)

      SET @PN  = (SELECT PN FROM StructureFile WHERE ID = @Root)
      SET @ID  = (SELECT MIN(ID) FROM StructureFile WHERE NHAID = @Root)
      Set @Mod = (SELECT ModuleType From StructureFile WHERE NHAID=@Root)

      PRINT REPLICATE('-', @@NESTLEVEL * 4) + @PN  + @Mod
      
      WHILE @ID  IS NOT NULL
      BEGIN
            EXEC ShowHierarchy @ID
            SET @ID  = (SELECT MIN(ID) FROM StructureFile WHERE NHAID = @Root AND ID > @ID)
      END
END



0
Comment
Question by:kevman123
  • 3
  • 2
  • 2
7 Comments
 
LVL 1

Expert Comment

by:gnagelram
Comment Utility
You could just use an extra parameter (@FilterModuleType) and then place the PRINT inside a test for the correct type.

IF @Mod = @FilterModuleType OR @Mod = 1 -- Maybe test for System module type too?
BEGIN
     PRINT REPLICATE('-', @@NESTLEVEL * 4) + @PN  + @Mod
END

...

EXEC ShowHierarchy @ID, @FilterModuleType

Is this what you meant? It would seem to work for your example but I'm not sure I understood correctly.

If you don't know the maximum depth of the tree you cannot (in general) use a recursive function since SQL Server has a limit of 32 nest levels. This can be solved by using a temporary table and a stack to store the row being worked on. There are examples in the SQL Server Books Online (index search for "hierarchical information"). Or I could show you an adapted example (with your table structure) if you like.
0
 

Author Comment

by:kevman123
Comment Utility
Well, it's fine because it will never be deeper than 32 levels-- let me try what you have entered.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
I do this with a temporary table (as from SQL 2k with a table variable), with this i am NOT limited to the 32 levels, and returning the results as 1 select.

Here  a NONTESTED draft:
ALTER       PROC ShowHierarchy
(
     @Root int
)
AS
DECLARE @rows int
DECLARE @level int

set NOCOUNT ON
CREATE TABLE #tmp ( ItemID int, Part varchar(10), ParentPart varchar(10), ModuleType int, handled int )
set @level = 0

INSERT INTO #tmp select ID, PART, NHA, ModuleType, @level from StructureFile where ID = @RootID
SELECT @rows = count(*) from #tmp where handled = @level

while @rows > 0
begin
  insert into #tmp  
  select d.ID, d.PART, d.NHA, d.ModuleType, @level + 1
  from StructureFile d, #tmp t
  where t.handled = @level
  and t.ItemID = d.NHA

  set @level = @level + 1
  SELECT @rows = count(*) from #tmp where handled = @level
end

SELECT * FROM #tmp
drop table #tmp

GO

CHeers
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 1

Expert Comment

by:gnagelram
Comment Utility
Note that while this will indeed insert the correct subtree rows into the temp-table it will lose the hierarchical ordering between them. You therefore cannot print the rows in a treeview from the example. To preserve ordering you still need to insert them one by one (not level by level). using a stack You also need an extra column in the temp-table for ordering information.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
If you want to display the "tree" using pure SQL, the comment of gnagelram is correct. However, if you have a GUI that loads the records into a treeview control, you will really need the records ordered by level (field handled)...
CHeers
0
 

Author Comment

by:kevman123
Comment Utility
I will be loading a tree with this information later on-- but for now it will be used for a Crystal Report-- Still having problems implementing the fist bit of code, am experienced programmer, but new to TSQL
0
 

Author Comment

by:kevman123
Comment Utility
Not exactly what I needed but this helped with some of the issues- thanks for your time!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Creating and Managing Databases with phpMyAdmin in cPanel.
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…

772 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

11 Experts available now in Live!

Get 1:1 Help Now