December 7, 2015 at 6:09 pm
Hello,
I am trying to do a recursive loop so I can get parent to child, to grand child etc. which it depends on the child how deep it will go.
I looked up many tutorials, sadly mine is not working correctly, wondering if I can get fresh eyes and maybe i am doing something wrong that may be simple?
code is below, it is really a recursive on a part, that is created by many parts, for example, a bike, to make a bike, you need metal bars, tires etc., well the child of the tire you need these materials to make a tire etc. etc., hope that clears it up. I made a CTE first to gather all columns needed, THEN next CTE which is called final, is used to make the recursive loop, sadly not sure why its not working, P is for parent, M is for manufacture which is the child that can go into many grand childs. The Parent is Part Num, and child is MtlPart Num, any help or suggestions please.
please help, i am sadly trying to get this to work but falling flat on my face 🙁
;WITH Info
as
(
select distinct pm.company,jh.ProjectID,pm.PartNum,p.PartDescription,pm.MtlPartNum
,COALESCE(pq.OnHandQty,0.00) OnHandQty
,COALESCE(od.OrderQty,0.00) OrderQty
,SUM(jm.RequiredQty) RequiredQty,p.TypeCode
,0 as [Level]
,Cast(pm.Partnum as Varchar(255)) as [Path]
from erp.PartMtl pm
Left join erp.JobMtl jm
on pm.company = jm.company
and pm.mtlpartnum = jm.partnum
and pm.MtlSeq = jm.MtlSeq
Left Join erp.Jobhead jh
on jm.company = jh.company
and jm.jobnum = jh.JobNum
Left Join erp.OrderDtl od
on pm.company = od.company
and pm.PartNum = od.partnum
and pm.RevisionNum = od.RevisionNum
Left Join erp.Part p
on pm.mtlpartnum = p.partnum
and pm.company = p.Company
Left Join erp.PartQty pq
on pm.company = pq.company
and pm.partnum = pq.partnum
group by pm.company,jh.ProjectID,pm.PartNum,p.PartDescription,pm.MtlPartNum,pq.OnHandQty,od.OrderQty,p.TypeCode
)
,Final
as
(
select Company,ProjectID,PartNum,PartDescription,MtlPartNum,OnHandQty,OrderQty,RequiredQty,Typecode,[Level]
,[Path]
from Info
where TypeCode = 'P'
Union ALL
select a.Company,a.ProjectID,a.PartNum,a.PartDescription,a.MtlPartNum,a.OnHandQty,a.OrderQty,a.RequiredQty,a.Typecode,a.[Level]+1
,CAST(a.[Path] + '.' + CAST(a.MtlPartNum AS VARCHAR(255)) AS VARCHAR(255))
from Info a
Join Final b
on a.company = b.company
and a.Partnum = b.MtlPartNum
where a.TypeCode = 'M'
)
select *
from Final
where [path] like '%909HMFABRK-002%'
December 7, 2015 at 6:39 pm
OK I found my problem, however definitely need your guys help, so here is the correct code:
;WITH Info
as
(
select distinct pm.company,jh.ProjectID,pm.PartNum,p.PartDescription,pm.MtlPartNum
,COALESCE(pq.OnHandQty,0.00) OnHandQty
,COALESCE(od.OrderQty,0.00) OrderQty
,SUM(jm.RequiredQty) RequiredQty,p.TypeCode
,0 as [Level]
,Cast(pm.Partnum as Varchar(255)) as [Path]
from erp.PartMtl pm
Left join erp.JobMtl jm
on pm.company = jm.company
and pm.mtlpartnum = jm.partnum
and pm.MtlSeq = jm.MtlSeq
Left Join erp.Jobhead jh
on jm.company = jh.company
and jm.jobnum = jh.JobNum
Left Join erp.OrderDtl od
on pm.company = od.company
and pm.PartNum = od.partnum
--and pm.RevisionNum = od.RevisionNum
Left Join erp.Part p
on pm.mtlpartnum = p.partnum
and pm.company = p.Company
Left Join erp.PartQty pq
on pm.company = pq.company
and pm.partnum = pq.partnum
group by pm.company,jh.ProjectID,pm.PartNum,p.PartDescription,pm.MtlPartNum,pq.OnHandQty,od.OrderQty,p.TypeCode
)
,Final
as
(
select Company,ProjectID,PartNum,PartDescription,MtlPartNum,OnHandQty,OrderQty,RequiredQty,Typecode,[Level]
,[Path]
from Info
where TypeCode = 'P'
Union ALL
select a.Company,a.ProjectID,a.PartNum,a.PartDescription,a.MtlPartNum,a.OnHandQty,a.OrderQty,a.RequiredQty,a.Typecode,a.[Level]+1
,CAST(a.[Path] + '.' + CAST(a.MtlPartNum AS VARCHAR(255)) AS VARCHAR(255))
from Info a
Join Final b
on a.company = b.company
and a.MtlPartNum = b.PartNum
where a.TypeCode = 'M'
)
select *
from Final
where [path] like '%909HMFABRK-002%'
SO, my question is this, how can I get the grandchild to reference back to parent, for example:
1. Bike - Parent
2. tires - child
3. steering - child
4. plastic - grandshild to Tires child
For number 4, how can I reference it all the way back to bike parent? or if I were to put this in a query, and someone wanted to run a filter/parameter for bike, how do I show the results like:
bike
tires
steering
plastic
when I use my query, the PATH column only references the child, which is tires.
any ideas?
December 7, 2015 at 6:46 pm
Since we don't have your data nor even your table structure, I have to ask... what is the code not doing?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 7, 2015 at 7:03 pm
sorry about that,
let me start with this:
create table #table_name
(
partnum varchar(10),
Mpartnum varchar(20),
typecode varchar(1000)
)
insert into #table_name(partnum,Mpartnum,typecode)
values('bike10','tires2','P')
insert into #table_name(partnum,Mpartnum,typecode)
values('bike10','Handle','P')
insert into #table_name(partnum,Mpartnum,typecode)
values('tires2','plastic','P')
insert into #table_name(partnum,Mpartnum,typecode)
values('plastic',' ','M')
;WITH info
as
(
select *
,0 as [Level]
,Cast(Mpartnum as Varchar(255)) as [Path]
from #table_name
union all
select a.*
,b.[Level]+1
,CAST(b.[Path] + '.' + CAST(a.Mpartnum AS VARCHAR(255)) AS VARCHAR(255))
from #table_name a
join info b
on a.Mpartnum = b.partnum
)
select *
from info
where partnum = 'bike10'
drop table #table_name
so once you run it 2 things:
1. you see plastic in the Path, its fine, but I want something to reference back to parent like this:
path: bike10.tires.plastic
2. I would like the row that has tires2 as partnum and Mpartnum plastic to show up because its the next level down from bike10, then to tires2, then plastic, hope this all makes sense and helps out better on explaining.
still trying but pulling my hair (whats left of it)
thanks in advanced
December 7, 2015 at 10:36 pm
OK. The first problem with all of this is that you've not used Bike10 as a child and so you have to do all sorts of different stuff because of it and all of that get's really confusing and also makes it more difficult to "drill down" into the "sub-assemblies". I added a "Top Level" to all of this so that we could make life much easier.
The second problem is minding your "a's and b's". It's really tough to remember which is the table and which is the cte when trying to troubleshoot.
The third problem is related to the first. My personal opinion is that the columns in the table are out of order as the normally would be. The "child" column is the key to the table and it should probably be first in the table just to keep from confusing people.
With all that, here's the modified test table setup and the solution. I also recommend that you adopt a method of standard indentation to make the code a lot easier to read and troubleshoot.
--===== If it exists, drop the test table to make reruns in SSMS easier.
IF OBJECT_ID('tempdcte..#TableName','U') IS NOT NULL
DROP TABLE #TableName
;
GO
--===== Create the test table.
-- Note that I changed the order of the columns to make things more obvious
CREATE TABLE #TableName
(
MPartNum VARCHAR(20) NOT NULL PRIMARY KEY CLUSTERED --Child, which must be unique.
,PPartNum VARCHAR(20) --Parent
,TypeCode CHAR(1)
)
;
--===== Populate the test table.
-- Notice that I added a "Top Level" and got rid of your last entry.
INSERT INTO #TableName
(MPartNum,PPartNum,TypeCode)
SELECT 'Bike10' ,NULL ,'P' UNION ALL --This is the "Top Level" to make Bike10 a "Child" like it needs to be.
SELECT 'Tires2' ,'Bike10','P' UNION ALL
SELECT 'Handle' ,'Bike10','P' UNION ALL
SELECT 'Plastic','Tires2','M'
;
--===== Now it's easy to solve the problem because everything we need is a "Child" and maybe a parent.
-- We don't need to handle the very top level differently.
WITH cteHierarchy AS
(
SELECT MPartNum
,PPartNum
,TypeCode
,HLevel = 0
,SortPath = CAST(MPartNum AS VARCHAR(254))
FROM #TableName
WHERE MPartNum = 'bike10'
UNION ALL -------------------------------------------------------------------------------
SELECT tbl.MPartNum
,tbl.PPartNum
,tbl.TypeCode
,HLevel = cte.HLevel + 1
,SortPath = CAST(cte.SortPath + '.' + CAST(tbl.MPartNum AS VARCHAR(254)) AS VARCHAR(254))
FROM #TableName tbl
JOIN cteHierarchy cte
ON tbl.PPartNum = cte.MPartNum
)
SELECT *
FROM cteHierarchy
ORDER BY SortPath
;
Here are the results.
MPartNum PPartNum TypeCode HLevel SortPath
-------------------- -------------------- -------- ----------- ---------------------
Bike10 NULL P 0 Bike10
Handle Bike10 P 1 Bike10.Handle
Tires2 Bike10 P 1 Bike10.Tires2
Plastic Tires2 M 2 Bike10.Tires2.Plastic
(4 row(s) affected)
Again, the key to success on Parent/Child (Adjacency List) tables is that, except for the very to level where the parent is NULL, all parents must also be children or it becomes a real bugger to write code for especially for later drill downs in the hierarchy.
If this is going to get big, then I recommend you read the following two articles for some of the easiest maintenance and fast performance possible. It also makes aggregating things like cost and rolled up costs a whole lot easier.
http://www.sqlservercentral.com/articles/Hierarchy/94040/
http://www.sqlservercentral.com/articles/T-SQL/94570/
--Jeff Moden
Change is inevitable... Change for the better is not.
December 8, 2015 at 3:31 am
Original table looks like bill of material type which is generally not restricted to tree structure, it may be a network hierarchy as well.
I added 'bike20' to demonstrate it.
I'd suggest that there should be two separate tables - parts list and bill of material, but let's stick with what OP provided. You can easily track roots of the hierarchies as follows
create table #table_name
(
partnum varchar(20), -- 20 both
Mpartnum varchar(20),
typecode varchar(1000)
)
insert into #table_name(partnum,Mpartnum,typecode)
values('bike20','tires2','P')
insert into #table_name(partnum,Mpartnum,typecode)
values('bike10','tires2','P')
insert into #table_name(partnum,Mpartnum,typecode)
values('bike10','Handle','P')
insert into #table_name(partnum,Mpartnum,typecode)
values('tires2','plastic','P')
insert into #table_name(partnum,Mpartnum,typecode)
values('plastic',' ','M')
;WITH info as (
select distinct -- list of parts to track
root = partnum
,partnum
,parent = cast( null as varchar(20))
,0 as [Level]
,Cast(partnum as Varchar(255)) as [Path]
from #table_name t
-- Uncomment it to start from top level parts only
--where not exists (select 1 from #table_name t2 where t2.Mpartnum = t.partnum)
union all
select
b.root
,a.Mpartnum
,parent=a.partnum
,b.[Level]+1
,CAST(b.[Path] + '.' + CAST(a.Mpartnum AS VARCHAR(255)) AS VARCHAR(255))
from #table_name a
join info b
on a.partnum = b.partnum
)
select *
from info
where root = 'tires2' -- 'bike20'
drop table #table_name
December 8, 2015 at 1:24 pm
thank you serg-52, and jeff.
thanks to you both I am all that close to getting my objective done.
now that I have the query I want it, or maybe I do, but now I want to line them up in ssrs, like
Bike2
Tires
plastic
steering
hope that heirachy explains how I would like it, sadly I researched and found that I need to go to properies of the group, then advanced then recursive, but I do that and selected group by Partnum for parent and its still looking vertical instead of what i would like it to be above, any thoughts?
thanks again both of you in advance or anyone else input
December 8, 2015 at 2:06 pm
thank you both again for your help, i found this article that helped me the rest of the way:
hope it helps others as well. but thank you again both of you, really steered me in the right direction and thanks jeff for the advice... little aggressive you are, but still thank you, I liked serg approach, much nicer 🙂
December 9, 2015 at 8:17 am
Heh... I'm a little aggressive because of what? Recommending that you format your code and use meaningful aliases or the recommendation to change the data to be a true and fully drillable Adjacency List?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply