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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How To Choose The Right "Big Data" Certification 5 111
null value 15 94
MySQL ERROR 1045 (28000) 2 67
SQL Query 34 82
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
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…

867 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

19 Experts available now in Live!

Get 1:1 Help Now