SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Querying a Supersession Two Column Table with Multiple Supersessions in both Columns


Querying a Supersession Two Column Table with Multiple Supersessions in both Columns

Author
Message
p.barwick
p.barwick
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 50
Apparently, SELECT INTO statements are not supported in Azure SQL Server .... nicely played, Microsoft. Do you have any suggestions?
p.barwick
p.barwick
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 50
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.
p.barwick
p.barwick
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 50
These are the results I am getting - there are four images in total:

https://skydrive.live.com/?cid=97BDA2515235BDF6&id=97BDA2515235BDF6%213286&v=3#cid=97BDA2515235BDF6&id=97BDA2515235BDF6%213286&v=3

The last query doesn't seem to be doing a lot.

Thanks.
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17915 Visits: 6431
p.barwick (2/4/2014)
These are the results I am getting - there are four images in total:

https://skydrive.live.com/?cid=97BDA2515235BDF6&id=97BDA2515235BDF6%213286&v=3#cid=97BDA2515235BDF6&id=97BDA2515235BDF6%213286&v=3

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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
p.barwick
p.barwick
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 50
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.
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17915 Visits: 6431
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17915 Visits: 6431
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
p.barwick
p.barwick
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 50
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;



dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17915 Visits: 6431
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
p.barwick
p.barwick
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 50
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:


ERR3340 KIT BRAKE LINING 58.02 GFE187 > GFE175 > GFE168 > GFE151 > ETC4953 > ERC8721 > ERR3340


But if I enter GFE168:


ERR3340 KIT 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;


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search