Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Indented BOM

Author  Topic 

Vack
Aged Yak Warrior

530 Posts
Posted - 2015-03-16 : 10:42:56

Is there a way to write a select statement to get an indented BOM?

One table: BMPRDSTR


Parent_Item   Component_Item   Qty_Per
   A1             C1              3
   A1             C2              2
   C1             D1              1
   C1             D2              4


If I query parent A1 I would want the following:

Level   Comp      Qty
1        C1        3
2        D1        1
2        D2        4
1        C2        3

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2015-03-16 : 10:57:17
Question: How deep can the BOM go?

If there is a fixed max depth, you can do it as a sequence of self-joins. If the max depth is not fixed, you can use a recursive CTE to do it.

BTW I don't seen any indentation on your sample output.
Go to Top of Page

Vack
Aged Yak Warrior

530 Posts
Posted - 2015-03-16 : 11:01:17
11 levels deep
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2015-03-16 : 11:07:46
I'd go with a series of self-joins unless the table is small. It looks ugly but performs much better than a recursive CTE
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2015-03-16 : 12:16:16
e.g. worked out to four levels


declare @ table (Parent_Item char(2), Component_Item char(2),  Qty_Per int)
insert into @ (Parent_Item,   Component_Item,   Qty_Per) values
--Parent_Item   Component_Item   Qty_Per
   ('A1', 'C1', 3),
   ('A1', 'C2', 2),
   ('C1', 'D1', 1),
   ('C1', 'D2', 4)

select bom.[Level], bom.Comp, bom.Qty
from 
(
	select distinct bom.* 
	from @ _0

	left join (select Parent_Item, Component_Item as Comp, Qty_Per  as Qty from @) _1 on _0.Component_Item = _1.Parent_Item
	left join (select Parent_Item, Component_Item as Comp, Qty_Per  as Qty from @) _2 on _1.Comp = _2.Parent_Item
	left join (select Parent_Item, Component_Item as Comp, Qty_Per  as Qty from @) _3 on _2.Comp = _3.Parent_Item
	left join (select Parent_Item, Component_Item as Comp, Qty_Per  as Qty from @) _4 on _3.Comp = _4.Parent_Item

	cross apply (
		select distinct * from 
		(
			select 1 as [Level], _0.Component_Item as Comp, _0.Qty_Per as Qty, _0.Component_Item as OrderOn
			union all 
			select 2, _1.Comp, _1.Qty, _0.Component_Item + _1.Comp
			union all 
			select 3, _2.Comp, _2.Qty, _0.Component_Item + _1.Comp + _2.Comp
			union all 
			select 4, _3.Comp, _3.Qty, _0.Component_Item + _1.Comp + _2.Comp + _3.Comp 
			union all 
			select 5, _4.Comp, _4.Qty, _0.Component_Item + _1.Comp + _2.Comp + _3.Comp + _4.Comp 
		) bom
	) bom

	where _0.Parent_Item = 'A1' and bom.Comp is not null
) bom
order By bom.OrderOn
Go to Top of Page

Vack
Aged Yak Warrior

530 Posts
Posted - 2015-03-16 : 15:06:02
The above is working, thanks for that.

There is a seq_no that I did not include in the original post. any way to sort by that?

So the level one items will have seq_no's of 10, 20 , 30 , 40

which tells the order they should show. Each level under will have the same seq_no.

I tried this, but it puts all level 2
select bom.[Level], bom.Comp, bom.Qty, bom.seq_no
from
(
select distinct bom.*
from bmprdstr_sql _0

left join (select item_no,seq_no, comp_item_no as Comp, qty_per_par as Qty from bmprdstr_sql) _1 on _0.comp_item_no = _1.item_no
left join (select item_no,seq_no, comp_item_no as Comp, qty_per_par as Qty from bmprdstr_sql) _2 on _1.Comp = _2.item_no
left join (select item_no,seq_no, comp_item_no as Comp, qty_per_par as Qty from bmprdstr_sql) _3 on _2.Comp = _3.item_no
left join (select item_no,seq_no, comp_item_no as Comp, qty_per_par as Qty from bmprdstr_sql) _4 on _3.Comp = _4.item_no

cross apply (
select distinct * from
(
select 1 as [Level],_0.seq_no, _0.comp_item_no as Comp, _0.qty_per_par as Qty, _0.comp_item_no as OrderOn
union all
select 2,_1.seq_no, _1.Comp, _1.Qty, _0.comp_item_no + _1.Comp
union all
select 3,_2.seq_no, _2.Comp, _2.Qty, _0.comp_item_no + _1.Comp + _2.Comp
union all
select 4, _3.seq_no, _3.Comp, _3.Qty, _0.comp_item_no + _1.Comp + _2.Comp + _3.Comp
union all
select 5,_4.seq_no, _4.Comp, _4.Qty, _0.comp_item_no + _1.Comp + _2.Comp + _3.Comp + _4.Comp
) bom
) bom

where _0.item_no = '56141' and bom.Comp is not null
) bom
order By OrderOn,seq_no
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2015-03-16 : 15:17:25
Please post your revised data
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2015-03-16 : 15:45:14
I mean your revised input data with the SeqNo column
Go to Top of Page

Vack
Aged Yak Warrior

530 Posts
Posted - 2015-03-16 : 15:45:20
[code]
Parent_Item Component_Item Qty_Per SEQ_no
A1 C1 3 10
A1 C2 2 20
A1 C3 3 30
C1 D1 1 10
C1 D2 4 20
D1 and D2 should still fall under C1
[/code]
Go to Top of Page

Vack
Aged Yak Warrior

530 Posts
Posted - 2015-03-16 : 15:47:33
[code]
Level Comp Qty
1 C1 3
2 D1 1
2 D2 4
1 C2 3
1 C3 3


[/code]
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2015-03-16 : 15:56:30
OK -- so with the new column, I have this:


declare @ table (Parent_Item char(2), Component_Item char(2),  Qty_Per int, SeqNo Int)
insert into @ (Parent_Item,   Component_Item,   Qty_Per, SeqNo) values
-- Parent_Item   Component_Item   Qty_Per  SEQ_no  
('A1','C1',3,10),
('A1','C2',2,20),
('A1','C3',3,30),
('C1','D1',1,10),
('C1','D2',4,20)

select bom.[Level], bom.Comp, bom.Qty, bom.SeqNo
from 
(
	select distinct bom.* 
	from @ _0

	left join (select Parent_Item, Component_Item as Comp, Qty_Per  as Qty, SeqNo from @) _1 on _0.Component_Item = _1.Parent_Item
	left join (select Parent_Item, Component_Item as Comp, Qty_Per  as Qty, SeqNo from @) _2 on _1.Comp = _2.Parent_Item
	left join (select Parent_Item, Component_Item as Comp, Qty_Per  as Qty, SeqNo from @) _3 on _2.Comp = _3.Parent_Item
	left join (select Parent_Item, Component_Item as Comp, Qty_Per  as Qty, SeqNo from @) _4 on _3.Comp = _4.Parent_Item

	cross apply (
		select distinct * from 
		(
			select 1 as [Level], _0.Component_Item as Comp, _0.Qty_Per as Qty, _0.SeqNo, _0.Component_Item as OrderBY
			union all 
			select 2, _1.Comp, _1.Qty, _1.SeqNo,  _0.Component_Item + _1.Comp
			union all 
			select 3, _2.Comp, _2.Qty, _2.SeqNo,  _0.Component_Item + _1.Comp + _2.Comp
			union all 
			select 4, _3.Comp, _3.Qty, _3.SeqNo,  _0.Component_Item + _1.Comp + _2.Comp + _3.Comp 
			union all 
			select 5, _4.Comp, _4.Qty, _4.SeqNo, _0.Component_Item + _1.Comp + _2.Comp + _3.Comp + _4.Comp 
		) bom
	) bom

	where _0.Parent_Item = 'A1' and bom.Comp is not null
) bom
order By bom.OrderBy, bom.SeqNo


which returns this:


Level	Comp	Qty	SeqNo
1	C1	3	10
2	D1	1	10
2	D2	4	20
1	C2	2	20
1	C3	3	30


So, what should the correct order be?
Go to Top of Page

Vack
Aged Yak Warrior

530 Posts
Posted - 2015-03-16 : 16:12:39
Still not coming out quite right when I run against the live data.

Here is a sample of what I'm getting. 

Level	Comp	                          Qty	     seq_no
1	32285                           8.000000	18
1	34996                         	1.000000	30
1	34997                         	3.000000	28
1	37905                         	2.000000	26
1	55754                         	1.000000	6
2	67719                         	1.000000	2
1	55755                         	1.000000	16
2	67719                         	1.000000	2 



They should be in this order.
1	55754                         	1.000000	6
2	67719                         	1.000000	2
1	55755                         	1.000000	16
2	67719                         	1.000000	2 
1	32285                           8.000000	18
1	37905                         	2.000000	26
1	34997                         	3.000000	28
1	34996                         	1.000000	30


I think if the level 1's are in order by sequence number then the rest will fall into place
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2015-03-16 : 16:25:52
Try this. Since I don't have live data and the test data set is a little small, I can't test it fully, but I think it's on the right track:


declare @ table (Parent_Item char(2), Component_Item char(2),  Qty_Per int, SeqNo Int)
insert into @ (Parent_Item,   Component_Item,   Qty_Per, SeqNo) values
-- Parent_Item   Component_Item   Qty_Per  SEQ_no  
('A1','C1',3,10),
('A1','C2',2,20),
('A1','C3',3,30),
('C1','D1',1,10),
('C1','D2',4,20)

select bom.[Level], bom.Comp, bom.Qty, bom.SeqNo
from 
(
	select distinct bom.* 
	from @ _0

	left join (select Parent_Item, Component_Item as Comp, Qty_Per  as Qty, SeqNo from @) _1 on _0.Component_Item = _1.Parent_Item
	left join (select Parent_Item, Component_Item as Comp, Qty_Per  as Qty, SeqNo from @) _2 on _1.Comp = _2.Parent_Item
	left join (select Parent_Item, Component_Item as Comp, Qty_Per  as Qty, SeqNo from @) _3 on _2.Comp = _3.Parent_Item
	left join (select Parent_Item, Component_Item as Comp, Qty_Per  as Qty, SeqNo from @) _4 on _3.Comp = _4.Parent_Item

	cross apply (
		select cast(_0.SeqNo as char(2)) As _0
		     , cast(_1.SeqNo as char(2)) As _1
			 , cast(_2.SeqNo as char(2)) As _2
			 , cast(_3.SeqNo as char(2)) As _3
			 , cast(_4.SeqNo as char(2)) As _4
	) seq

	cross apply (
		select distinct * from 
		(
			select 1 as [Level], _0.Component_Item as Comp, _0.Qty_Per as Qty, _0.SeqNo, seq._0 + _0.Component_Item as OrderBY
			union all 
			select 2, _1.Comp, _1.Qty, _1.SeqNo, seq._0 + _0.Component_Item + seq._1 + _1.Comp
			union all 
			select 3, _2.Comp, _2.Qty, _2.SeqNo, seq._0 + _0.Component_Item + seq._1 + _1.Comp + seq._2 + _2.Comp
			union all 
			select 4, _3.Comp, _3.Qty, _3.SeqNo, seq._0 + _0.Component_Item + seq._1 + _1.Comp + seq._2 + _2.Comp + seq._3 + _3.Comp 
			union all 
			select 5, _4.Comp, _4.Qty, _4.SeqNo, seq._0 + _0.Component_Item + seq._1 + _1.Comp + seq._2 + _2.Comp + seq._3 + _3.Comp + seq._4 + _4.Comp
		) bom
	) bom

	where _0.Parent_Item = 'A1' and bom.Comp is not null
) bom
order By bom.OrderBy
Go to Top of Page

Vack
Aged Yak Warrior

530 Posts
Posted - 2015-03-17 : 12:51:42
Still not ordering.
Use this data and you should see what I'm seeing. First item listed should be 67805 because it level 1 and has the lowest seqno:

declare @ table (Parent_Item char(5), Component_Item char(5),  Qty_Per int, SeqNo Int)
insert into @ (parent_item, component_item, qty_per, seqno) values
('56141','67805',1,4),
('67805','30699',1,2),
('67805','32885',1,4),
('67805','30724',1,6),
('56141','55754',1,6),
('55754','67719',1,2),
('56141','55913',1,8),
('55913','67719',1,2),
('56141','55761',1,12),
('55761','67720',1,2),
('56141','32285',1,14),
('56141','60579',1,16),
('56141','67805',1,4)
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2015-03-17 : 13:08:34
Changed the inner cross apply to:


	cross apply (
		select right('00' + cast(_0.SeqNo as varchar(2)), 2) As _0
		     , right('00' + cast(_1.SeqNo as varchar(2)), 2) As _1
			 , right('00' + cast(_2.SeqNo as varchar(2)), 2) As _2
			 , right('00' + cast(_3.SeqNo as varchar(2)), 2) As _3
			 , right('00' + cast(_4.SeqNo as varchar(2)), 2) As _4
	) seq


since the order by is ascii, not numeric
Go to Top of Page

Vack
Aged Yak Warrior

530 Posts
Posted - 2015-03-17 : 13:12:34
Who ever you work for, tell them they don't pay you enough money. : )

Thanks for your help.
Go to Top of Page

huangchen
Starting Member

37 Posts
Posted - 2015-04-02 : 05:57:01
unspammed
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site