December 27, 2005 at 10:08 am
Hi,
I have a two tables containing item numbers. The first table contains the Parent item number, the second table contains superitemno and suborderitemno. I can link between the parent and child. However I then need to requery the child table where the suborderitemno field becomes the superitemno field, this needs to be done until the lowest level has been reached( I don't know how many levels there are).
I'm not really sure how I can requery this same table until I've reached the last level.
These are my main fields billofmaterial_id,superitemno,suborderitemno. I would appreciated if someone can point me in the right direction.
Thanks,
December 30, 2005 at 10:13 am
This is difficult to do in T-SQL. Recursion is an option and works well in 2005, but I'm wary of it in 2000, plus you might hit a nesting limit.
Check this out:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=175525
January 3, 2006 at 7:36 am
I didn't quite do recursion. My solution albeit a bit cryptic (was successful and somewhat messy) and since I didn't have a true starting point I did however have two fields call superitemno and subitemno. I knew that I could use those two fields to retrieve the child nodes. This is what I came up with:
SELECT RTRIM(LTRIM(superitemno)) AS [toplevel],
RTRIM(LTRIM(suborditemno)) AS [firstlevel],
billofmatquant AS [demand(reserved)] INTO procurement_demand
FROM billofmaterial
WHERE RTRIM(LTRIM(superitemno)) IN (SELECT masteritemno FROM procurement_customerorders)
ORDER BY superitemno
I kept running this type of query using the previous subitemno as the new superitemno until the query did not return any records. In this BOM I came up with 5 levels (luckily). I then did a UNION to consolidate all items to give me my "Universe". This is kind of like taking a family picture.
Thanks,
Joe
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply