February 4, 2014 at 6:09 pm
Are you running the queries I provided using the Temp tables as I defined? Or have you modified them to operate off your real tables?
When I run the second query (SELECT * FROM #Hierarchy) this is what I get.
n OriginalPart PartNo PNPrior
1 STC3333 STC8572 STC3333
2 STC3333 STC8572 STC9191
3 STC3333 RTC5756 STC9191
3 STC3333 SFP500160 STC9191
3 STC3333 STC3765 STC9191
3 STC3333 STC9150 STC9191
3 STC3333 STC9191 STC2951
There are 7 rows because of STC3333 (a new PN that I added). With that row included in the sample data, I get this for the last query (the complicated one):
PartNo PartDec Price Supercessions
SFP500160 KIT BRAKE LINING 58.02 STC3333 > STC8572 > STC9191 > STC2951 > STC9150 > STC3765 > RTC5756 > SFP500160
Because of the below SQL assignment to @MyPartNo, if that row doesn't exist in the sample data you'll probably get no rows out of the last query.
DECLARE @N INT = 1
,@MyPartNo VARCHAR(20) = 'STC3333';
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 4, 2014 at 6:11 pm
Probably your best course of action is to post the exact SQL you are running along with the results you're getting just as I have done.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 4, 2014 at 6:25 pm
This, for example, returns 0 rows for the last segment.
CREATE TABLE #Supersessions
(
PartNo VARCHAR(20)
,PNPrior VARCHAR(20)
);
INSERT INTO #Supersessions
SELECT 'RTC5756', 'STC9191'
UNION ALL SELECT 'SFP500160','STC9191'
UNION ALL SELECT 'STC9191','STC2951'
UNION ALL SELECT 'STC3765','STC9191'
UNION ALL SELECT 'STC8572','STC9191'
UNION ALL SELECT 'STC9150','STC9191'
UNION ALL SELECT 'STC8572','STC3333';
CREATE TABLE #Source
(
PartNo VARCHAR(20)
,PartDesc VARCHAR(100)
,Price MONEY
);
SELECT 'SFP500160','KIT BRAKE LINING',58.02;
CREATE TABLE #Hierarchy (Id int NOT NULL IDENTITY(1,1) PRIMARY KEY, [n] nvarchar(max), [OriginalPart] nvarchar(max), [PartNo] nvarchar(max), [PNPrior] nvarchar(max));
DECLARE @N INT = 1
,@MyPartNo VARCHAR(20) = 'STC3333';
INSERT INTO #Hierarchy([n], [OriginalPart], [PartNo], [PNPrior])
SELECT n=@N, OriginalPart=@MyPartNo, a.PartNo, PNPrior
FROM #Supersessions a
WHERE a.PartNo = @MyPartNo OR a.PNPrior = @MyPartNo;
/*
SELECT n=@N, OriginalPart=@MyPartNo
,a.PartNo, PNPrior
INTO #Hierarchy
FROM #Supersessions a
WHERE a.PartNo = @MyPartNo OR a.PNPrior = @MyPartNo;
*/
WHILE @@ROWCOUNT > 0
BEGIN
SELECT @N = @N + 1;
INSERT INTO #Hierarchy
SELECT @N, OriginalPart, PartNo, PNPrior
FROM
(
SELECT OriginalPart, b.PartNo, b.PNPrior
FROM #Hierarchy a
JOIN #Supersessions b ON a.PNPrior = b.PNPrior
UNION ALL
SELECT OriginalPart, b.PartNo, b.PNPrior
FROM #Hierarchy a
JOIN #Supersessions b ON a.PNPrior = b.PartNo
UNION ALL
SELECT OriginalPart, b.PartNo, b.PNPrior
FROM #Hierarchy a
JOIN #Supersessions b ON a.PartNo = b.PNPrior
UNION ALL
SELECT OriginalPart, b.PartNo, b.PNPrior
FROM #Hierarchy a
JOIN #Supersessions b ON a.PartNo = b.PartNo
EXCEPT
SELECT OriginalPart, PartNo, PNPrior
FROM #Hierarchy
) a;
END
SELECT * FROM #Hierarchy;
WITH AllParts AS
(
SELECT PN, rn, N, OriginalPart, m
FROM
(
SELECT OriginalPart, PN, rn=ROW_NUMBER() OVER (PARTITION BY PN ORDER BY N), N, m
FROM #Hierarchy a
CROSS APPLY (VALUES (1, PartNo), (0, PNPrior)) b (m, PN)
) b
WHERE rn = 1
)
SELECT b.PartNo, PartDec=MAX(b.PartDesc), Price=MAX(b.Price)
,Supercessions=REPLACE(STUFF(
(
SELECT '-' + PN
FROM AllParts c
ORDER BY N, m, CASE WHEN c.PN = b.PartNo THEN '' ELSE c.PN END DESC
FOR XML PATH('')
), 1, 1, ''), '-', ' > ')
FROM AllParts a
JOIN #Source b ON b.PartNo = a.PN
GROUP BY b.PartNo;
GO
DROP TABLE #Source;
DROP TABLE #Supersessions;
DROP TABLE #Hierarchy;
February 4, 2014 at 6:39 pm
p.barwick (2/4/2014)
This, for example, returns 0 rows for the last segment.
..
CREATE TABLE #Source
(
PartNo VARCHAR(20)
,PartDesc VARCHAR(100)
,Price MONEY
);
SELECT 'SFP500160','KIT BRAKE LINING',58.02;
...
That's because you somehow lost the INSERT before SELECT when populating your #Source table. With nothing in #Source, the final query will always return 0 rows because of the INNER JOIN.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 4, 2014 at 6:55 pm
Ahh, I feel like a real dumb *** right now ...
It's working much better now, it still struggles on some part numbers though.
If I enter ERR3340, then everything is good:
ERR3340KIT BRAKE LINING 58.02 GFE187 > GFE175 > GFE168 > GFE151 > ETC4953 > ERC8721 > ERR3340
But if I enter GFE168:
ERR3340KIT BRAKE LINING 58.02 GFE168 > ERR3340
Using this code on a live table.
INSERT INTO #Source
SELECT 'ERR3340','KIT BRAKE LINING',58.02;
CREATE TABLE #Hierarchy (Id int NOT NULL IDENTITY(1,1) PRIMARY KEY, [n] nvarchar(max), [OriginalPart] nvarchar(max), [PartNo] nvarchar(max), [PNPrior] nvarchar(max));
DECLARE @N INT = 1
,@MyPartNo VARCHAR(20) = 'ERR3340';
INSERT INTO #Hierarchy([n], [OriginalPart], [PartNo], [PNPrior])
SELECT n=@N, OriginalPart=@MyPartNo, a.Superseeding_Item, Superseeded_Item
FROM dbo.ITEM_SUPERSESSION a
WHERE a.Superseeded_Item = @MyPartNo OR a.Superseeding_Item = @MyPartNo;
February 4, 2014 at 7:17 pm
I'm just guessing here because I don't have much additional time to look deeper, but this probably has to do with the fact you have no "supersession date" on your Supersessions table (recall I mentioned this earlier).
If you look at the code I provided (the last query), you see I'm jumping through quite a few hoops to try and get a reasonable sort order that places earlier part numbers before later ones. What I did there is far from perfect and probably leads you to situations like this. In effect, there is no way to determine the order that part numbers get retired/replaced. Ultimately I think you'll need that to get a good list of supersession parts in an appropriate sequence.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 5, 2014 at 7:59 pm
Sorry. Bad post deleted. I had a question on the order of things but figured that out. Order doesn't seem to matter here.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply