January 29, 2014 at 5:22 pm
Hi Guys,
I'm fairly new to SQL and am just setting up a Windows 8 app using an Azure SQL server. The issue I have is looking up a part number supersession and getting the latest number. One part number can have multiple supersessions (ie RTC5756 > STC8572 > STC3765 > STC9150 > STC9191 > SFP500160 )
The data I am supplied monthly has both the superseeded items and the supersession information in both columns and is not easy to decipher - for example:
Supersessions Table
----------------------
RTC5756 | STC9191
SFP500160 | STC9191
STC9191 | STC2951
STC3765 | STC9191
STC8572 | STC9191
STC9150 | STC9191
Source Table
----------------------
SFP500160 | KIT BRAKE LINING | 58.02
The newest part number is kept in a separate table - called "source" - which in this instance is SFP500160.
I need access to the latest part number but also to the part's previous numbers, due to the fact that some people may still be stocking them as an old part number and for them to search by. Is there an easy and efficient way of doing both a lookup for the supersessions and a join on the two tables to minimize the queries on the database?
Any help would be greatly appreciated. If I need to make anything clearer, please let me know.
Thanks
January 29, 2014 at 9:34 pm
Are you looking for something like this:
WITH Supercessions (PartNo, PNPrior) AS
(
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'
),
[Source] (PartNo, PartDesc, Price) AS
(
SELECT 'SFP500160','KIT BRAKE LINING',58.02
),
PartsHierarchy AS
(
SELECT n=1, OriginalPart=b.PartNo, a.PartNo, PNPrior, PartDesc, Price
FROM Supercessions a
JOIN [Source] b ON a.PartNo = b.PartNo
UNION ALL
SELECT n+1, OriginalPart, b.PartNo, b.PNPrior, PartDesc, Price
FROM PartsHierarchy a
JOIN Supercessions b ON a.PNPrior = b.Partno
)
SELECT *
FROM PartsHierarchy;
Note how I've set up your sample data for you in a consumable form. Forum posting etiquette suggests you should do this to help your helpers help you. I've been kind today.
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
January 30, 2014 at 5:17 am
Hi,
Many thanks for your prompt reply and patience.
What I'm really trying to achieve is, a user inputs a part number, ie STC8572 (the customer does not necessarily have access to or know any of the other part numbers associated with this number), the lookup table then checks for supersessions of that part number - in this case:
(oldest number) RTC5756 > STC8572 > STC3765 > STC9150 > STC9191 > SFP500160 (newest number)
The query then returns the newest number as the number to be used, along with the part information, which it knows based on the 'source' table, with a message such as 'The part number you have entered has been superseded to SFP500160'.
The query will also return all of the previous numbers associated with that number, to be consumed by the application for cross reference.
I hope this clarifies things a little bit.
February 2, 2014 at 5:17 pm
p.barwick (1/30/2014)
Hi,(oldest number) RTC5756 > STC8572 > STC3765 > STC9150 > STC9191 > SFP500160 (newest number)
Is the above exactly the way you want to see the output from the query? In other words, do you want a single row returned as your results? Are the part numbers in one column or more than one? If the latter, how many supersessions are possible?
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 3, 2014 at 4:22 pm
Hi,
I would like the result returned as
'SFP500160','KIT BRAKE LINING',58.02,{array of previous and current part numbers}
ie
'SFP500160','KIT BRAKE LINING',58.02,{ 'RTC5756','STC8572','STC3765','STC9150','STC9191','SFP500160' }
or two split results, whichever is easier
'SFP500160','KIT BRAKE LINING',58.02
'RTC5756'
'STC8572'
'STC3765'
'STC9150'
'STC9191'
'SFP500160'
Baring in mind,that we are starting the search with only one of these part numbers known to the end user.
The part supersession trail length can be quite long, I think the longest so far is 177. They average 5-10 in most instances though.
The way the supersessions table is provided to me is a reason for my headache. Usually, you would expect the search to filter "old part number > new part number > old part number > new part number" and so forth but in this instance, the later part number can be in either the "OriginalPartNumber" column or the "NewPartNumber" column. With more than 70,000 rows and a monthly update of the Supersessions table in this format, it would be nice to have a clean and easy way to find the part number supersession trail.
Thanks.
February 3, 2014 at 6:41 pm
Not sure this is exactly what will work for you but it may be close. Note that I added a PN (STC3333) to see if it would put it at the beginning of the chain (oldest).
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
);
INSERT INTO #Source
SELECT 'SFP500160','KIT BRAKE LINING',58.02;
DECLARE @N INT = 1;
SELECT n=@N, OriginalPart=b.PartNo, a.PartNo, PNPrior
INTO #Hierarchy
FROM #Supersessions a
JOIN #Source b ON a.PartNo = b.PartNo;
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 #Supersessions;
SELECT PartNo, PartDesc, Price
,Supercessions=REPLACE(STUFF(
(
SELECT '-' + PN
FROM
(
SELECT OriginalPart, PN, rn=ROW_NUMBER() OVER (PARTITION BY PN ORDER BY N)
,orb=CASE WHEN OriginalPart = PN THEN 0 ELSE N END, N
FROM #Hierarchy a
CROSS APPLY (VALUES (PartNo), (PNPrior)) b (PN)
) b
WHERE rn = 1 AND a.PartNo = b.OriginalPart
ORDER BY orb DESC
FOR XML PATH('')
), 1, 1, ''), '-', ' > ')
FROM #Source a;
GO
DROP TABLE #Source;
DROP TABLE #Supersessions;
DROP TABLE #Hierarchy;
There are several key issues here:
1. The need to eliminate a PN from the chain when it's already been resolved, hence I switched from the recursive CTE to the WHILE LOOP with EXCEPT.
2. At a particular level (and all of your PNs just below STC9191 fall into this category), I am not sure of the sort criteria. Perhaps you've got another column (like a date the PN was introduced) to help here.
3. It was not real clear from your initial description whether your Supersessions table lists the prior PN in the second column or whether those columns are reversed. I called that column PNPrior based on my assumption.
Edit: Oh yeah, and one more thing.
4. I'm not clear if you're trying to resolve a PN passed in by the UI (like @PartNo) or whether you always want to resolve from the Source table. I think I may have gotten that part wrong. But you should be able to work around that by using @PartNo in a WHERE clause of the first SELECT INTO #Hierarchy (instead of the 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 3, 2014 at 7:41 pm
Hi,
Thanks for your hard work, it's appreciated. As it stands, executing that query results in:
[Err] 42000 - [SQL Server] Must declare the scalar variable "@N"
just after
JOIN #Source b ON a.PartNo = b.PartNo
The supersessions chain appears to have no logic to the old and new part number columns. Old and new numbers are mixed up in both. Unfortunately, I only have two columns worth of data, there is no time period logged for the part number change. Largely, because I suppose it's mostly irrelevant to the end user.
The important thing, is that a list of previous part numbers is returned, regardless of order, for a point of reference.
And yes, I will be pushing data from the UI in to the SELECT statement - purely the one part number that they are searching for though.
For instance, if I searched for STC3333 in your example, it would redirect me to SFP500160 and then provide the UI with an array of previous part numbers linked to that latest number, as well as information from the [source] table for SFP500160.
The more I think about it, the more of a nightmare I can see it becoming, with no point of reference on the supersession columns ...
Can I send you the complete table, which I can export as either a CSV or TAB delimited file?
Thanks.
February 3, 2014 at 7:48 pm
p.barwick (2/3/2014)
Hi,Thanks for your hard work, it's appreciated. As it stands, executing that query results in:
[Err] 42000 - [SQL Server] Must declare the scalar variable "@N"
...
Can I send you the complete table, which I can export as either a CSV or TAB delimited file?
Thanks.
Before you send more data, fix the error by including the DECLARE of @N that is in the code I provided you right after the table CREATEs/INSERTs. See if what you're getting is close to what you need.
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 3, 2014 at 8:09 pm
Color me stupid but where exactly do I need to declare it?
Thanks.
February 3, 2014 at 8:17 pm
p.barwick (2/3/2014)
Color me stupid but where exactly do I need to declare it?Thanks.
It is already declared in the code I provided. You need to do it before the first SELECT into the temp #Hierarchy table. Let's do this a step at a time (new solution that may be closer to what you need):
First, your sample data with the row I added for STC3333:
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
);
INSERT INTO #Source
SELECT 'SFP500160','KIT BRAKE LINING',58.02;
Now the DECLARE (with the new variable @MYPartNo which gets passed in by the UI):
DECLARE @N INT = 1
,@MyPartNo VARCHAR(20) = 'STC3333';
First SELECT to create the #Hierarchy table, plus the WHILE loop to fully populate it:
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
Now we take a look at what's in the #Hierarchy table (so you can see what I did) and then do a SELECT that might be close to what you want:
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;
Finally, we drop our temp tables, cleaning up our sandbox.
GO
DROP TABLE #Source;
DROP TABLE #Supersessions;
DROP TABLE #Hierarchy;
That whole sorting thing is pretty problematic. I suspect you won't like the ordering if you happen to pick a PN that's somewhere in the middle of the chain. Like I said, without a date of supersession or something, driving the ordering is going to be a headache.
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 5:23 am
Apparently, SELECT INTO statements are not supported in Azure SQL Server .... nicely played, Microsoft. Do you have any suggestions?
February 4, 2014 at 6:28 am
Ok, so it seems to be working with this:
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';
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) = 'stc8572';
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;
The PartNo column on the second part of the query is returning all of the superseded part numbers, so it looks like that it is working as it should.
February 4, 2014 at 12:14 pm
These are the results I am getting - there are four images in total:
The last query doesn't seem to be doing a lot.
Thanks.
February 4, 2014 at 5:23 pm
p.barwick (2/4/2014)
These are the results I am getting - there are four images in total:The last query doesn't seem to be doing a lot.
Thanks.
Unfortunately I don't have a Skydrive account and I'm not inclined to get one.
Your prior two posts are unclear on whether your solution is in hand. One says yes but this one seems to indicate not.
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 5:45 pm
I didn't realise there was a third part to the query. The second part returns all of the supersessions (6 rows) of the chosen number, in the PartNo column - so, that looks good.
The third part returns no rows at all.
Edit: Ok, well after having it operate on a live table with 40,000+ part numbers, I can say it works about 30% of the time.
I think columns like this ...
Old No | New No
1080 | 1080FC
1080F | 1080FC
1080FA | 1080FC
1080FB | 1080FC
... seem to throw it a little bit. If you search for 1080, it returns (OriginalPart) 1080, (PartNo) 1080FC, (PNPrior) 1080 and skips the other numbers entirely.
It would probably help if you could see the data in a larger context.
Thanks for your continued help.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply