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

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



kevman123Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
gnagelramCommented:
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
 
kevman123Author Commented:
Well, it's fine because it will never be deeper than 32 levels-- let me try what you have entered.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
gnagelramCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
kevman123Author Commented:
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
 
kevman123Author Commented:
Not exactly what I needed but this helped with some of the issues- thanks for your time!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.