Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Querying a Supersession Two Column Table with Multiple Supersessions in both Columns Expand / Collapse
Author
Message
Posted Tuesday, February 4, 2014 5:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 5:49 PM
Points: 11, Visits: 50
Apparently, SELECT INTO statements are not supported in Azure SQL Server .... nicely played, Microsoft. Do you have any suggestions?
Post #1537700
Posted Tuesday, February 4, 2014 6:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 5:49 PM
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.
Post #1537734
Posted Tuesday, February 4, 2014 12:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 5:49 PM
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.
Post #1537909
Posted Tuesday, February 4, 2014 5:23 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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!
Post #1537997
Posted Tuesday, February 4, 2014 5:45 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 5:49 PM
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.
Post #1538001
Posted Tuesday, February 4, 2014 6:09 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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!
Post #1538004
Posted Tuesday, February 4, 2014 6:11 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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!
Post #1538005
Posted Tuesday, February 4, 2014 6:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 5:49 PM
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;


Post #1538008
Posted Tuesday, February 4, 2014 6:39 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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!
Post #1538012
Posted Tuesday, February 4, 2014 6:55 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 5:49 PM
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;

Post #1538015
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse