*facepalm* Seriously?
CELKO (12/30/2012)
How can a quantity be NUMERIC(19, 5) and not an INTEGER?
Water: 1.5 gallons
Flour: .75 cups
It's a Bill of Materials table. Decimals are definately part of it.
Likewise, why are machine times expressed to less than a nano-second?
It does no harm and the fifth decimal allows for avoiding rounding issues if important, which is something that particular systems report using so they are accurate to the nanosecond, and there's no reason to force a mid-point data type conversion.
None of these things are tables at all! No keys! Rows are not records [sic], totally different concept. We also hate looping; it is procedural and SQL is declarative.
Oh here we go again. Also, lose the "WE". Not all of us like you. Be more explicit.
They are tables. Lacking keys isn't helping but these are also example entries, not full declarations, as you well know and are justing ranting about to be a pain. Rows, records, whatever. So, without looping, tell me, how do you traverse an Org table? Either you loop during the creation and update, or you loop during the read. SOMEWHERE in an org table, you loop. Nested Sets or recursive heirarchy, you will loop SOMEWHERE.
Please, take 2013 off or something? Perhaps a new resolution: I will not berate newbies for stuff that won't blow up their production systems and for not being completely anal about building sample data sets?
Bron, the rest of Celko's post is actually useful as an introduction to Nested Sets theory, but you'll need to seriously go for a long wiki-walk and a bunch of blogs to get that under your belt and understood. It's definately good to know, however. It's particularly much faster for lookups than recusion org-trees when the tree it self is fairly non-changing. All the loop/recusion/setup work is done up front, and then it's a simple quick-read to get anything else. If you add/alter things, that's where the code goes deep to keep it all straight and to 'move' things around. It is much faster for the 99.9% read type of hierarchies though.
Your sample data was untested, as you missed a comma, so your value for FG-A ASSEM is a bit off, it's 11 and not 9, due to Subassy inclusion. This is the bare-bones for what you need, and I don't believe it'll handle multiple layers of the heirarchy well, partially because there's no parent record recording the parent as a subcomponent of itself. It will, however, get you started.
If you have multiple heirarchy examples and would like more assistance, please set up that sample data and I'll see if I (or others, if they have time) can expand this example. Running a bit low on time today.
;with Construct AS
(SELECT DISTINCT
Parent AS Construct
FROM
Table1
),
rCTE AS
(SELECT
c.Construct,
t1.Child AS Inclusion
FROM
Construct AS c
JOIN
Table1 AS t1
ON c.Construct = t1.Parent
UNION ALL
SELECT
r.Construct,
t1.Child AS Inclusion
FROM
rCTE as r
JOIN
Table1 AS t1
ONr.Inclusion = t1.Parent
)
/*
SELECT * FROM rCTE
ORDER BY Construct, Inclusion
*/
SELECT
r.Construct,
t2.MachineID,
SUM( t2.MachTime) AS SumTime
FROM
( SELECT Construct, Construct AS Inclusion FROM Construct
UNION ALL
SELECT Construct, Inclusion FROM rCTE
) AS r
JOIN
Table2 AS t2
ONr.Inclusion = t2.Parent
GROUP BY
r.Construct,
t2.MachineID
ORDER BY
r.construct,
t2.MachineID
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA