?
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
Medium Priority
?
415 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
[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
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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 143

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

741 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