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
376 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
ID: 11827997
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
ID: 11829649
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
ID: 11830727
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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 1

Expert Comment

by:gnagelram
ID: 11830842
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]
ID: 11831080
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
ID: 11831111
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
ID: 12055186
Not exactly what I needed but this helped with some of the issues- thanks for your time!
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Pivot not using aggregate yield error 3 53
Runtime 3044 error 14 43
Exchange 2007 standard - defrag (eseutul /d) 10 39
Merging spreadsheets 8 31
This article describes some very basic things about SQL Server filegroups.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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…

785 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