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.
| Author |
Topic |
|
Lordkirin
Starting Member
3 Posts |
 Posted - 2014-11-05 : 14:40:38
|
| I am trying to get a BOM from a table.The table is PS and it has Parent_Part and Component_Part The parent has components and each component become a parent and it has component and so on. Here is the code I have.WITH BOM (lvl,Component_Part) AS (Select 1 as lvl,ps0.Component_Part FROM PS ps0 INNER JOIN PS ps2 on ps0.Parent_Part = ps2.Component_Part WHERE ps0.Parent_Part = '122662'UNION ALL Select lvl+1,ps1.Component_Part FROM PS as ps1 INNER JOIN PS ps2 on ps1.Parent_Part = ps2.Component_Part JOIN BOM ON ps1.Parent_Part = BOM.Component_Part )SELECT * FROM BOM Any help would be great thanks |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2014-11-05 : 16:37:31
|
WITH BOM (lvl,Component_Part) AS (Select 1 as lvl,ps0.Component_Part FROM PS ps0 INNER JOIN PS ps2 on ps0.Parent_Part = ps2.Component_Part WHERE ps0.Parent_Part = '122662'UNION ALL Select lvl+1,ps1.Component_Part FROM PS as ps1 INNER JOIN PS ps2 on ps1.Parent_Part = ps2.Component_Part JOIN BOM ON ps1.Parent_Part = BOM.Component_Part )SELECT * FROM BOM ???
No amount of belief makes something a fact. -James Randi |
 |
|
|
Lordkirin
Starting Member
3 Posts |
Posted - 2014-11-05 : 17:07:55
|
| I removed that line and still it doesn't work.Gives me a error still. "The multi-part identifier 'BOM.Component_Part'could not be bound."Have any other idea? |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2014-11-05 : 17:41:22
|
WITH BOM (lvl,Component_Part) AS (Select 1 as lvl,ps0.Component_Part FROM PS ps0 INNER JOIN PS ps2 on ps0.Parent_Part = ps2.Component_Part WHERE ps0.Parent_Part = '122662'UNION ALL Select lvl+1,ps1.Component_Part FROM PS as ps1 INNER JOIN PS ps2 on ps1.Parent_Part = ps2.Component_Part JOIN BOM ON ps1.Parent_Part = BOM.Component_Part )SELECT * FROM BOM Without schema definition and data, this can only be a guess...
No amount of belief makes something a fact. -James Randi |
|
|
|
Lordkirin
Starting Member
3 Posts |
Posted - 2014-11-05 : 19:09:40
|
| Still comes up with that same error. I am doing this in VS 2013 using the query builder if that helps |
|
|
|
|
|
|
SQL BOM