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»»

identifying many to many relationship between columns Expand / Collapse
Author
Message
Posted Wednesday, November 13, 2013 1:01 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:51 PM
Points: 141, Visits: 531
Can someone please help me to catch up? I feel I'm not understanding the issue. Using the OP's initial data:

ITEM1 ITEM2 ID
0224180 0224181 1
0224180 0224190 1
0224181 0224180 1
0224181 0224190 1
0224190 0224180 1
0224190 0224181 1
0202294 0202295 2
0202295 0202294 2
0209250 0209251 3
0209251 0209250 3

Why, for example, does the last value pair "0209251, 0209250" get an ID of 3? (This value pair appears only 2 times in the data.) Or why does the value pair "0224190, 0224181" get an ID of 1? (This value pair appears twice in the data.)

Thanks, am hoping to grasp what's going on here so I can learn from it too! :)



"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Post #1514040
Posted Wednesday, November 13, 2013 5:31 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: Yesterday @ 10:07 PM
Points: 3,617, Visits: 5,236
lmeinke (11/13/2013)
Correction....those errors were due to my sloppy typing....but back to the original error:

Msg 240, Level 16, State 1, Line 1
Types don't match between the anchor and the recursive part in column "STD SKU" of recursive query "rCTE".


In my original post, the rCTE started like this:

WITH rCTE AS 
(
SELECT n=0, ITEM
,rn=ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
,ITEM1=ITEM
,ITEM2=NULL
FROM


In my suggested correction for this error, it looks like this.

WITH rCTE AS 
(
SELECT n=0, ITEM
,rn=ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
,ITEM1=ITEM
,ITEM2=CAST(NULL AS VARCHAR(20))
FROM


So did you remember to add the CAST around the NULL for ITEM2? The length of the VARCHAR should match whatever the length of your product code is in the table.



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 #1514115
Posted Wednesday, November 13, 2013 5:34 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: Yesterday @ 10:07 PM
Points: 3,617, Visits: 5,236
autoexcrement (11/13/2013)
Can someone please help me to catch up? I feel I'm not understanding the issue. Using the OP's initial data:

ITEM1 ITEM2 ID
0224180 0224181 1
0224180 0224190 1
0224181 0224180 1
0224181 0224190 1
0224190 0224180 1
0224190 0224181 1
0202294 0202295 2
0202295 0202294 2
0209250 0209251 3
0209251 0209250 3

Why, for example, does the last value pair "0209251, 0209250" get an ID of 3? (This value pair appears only 2 times in the data.) Or why does the value pair "0224190, 0224181" get an ID of 1? (This value pair appears twice in the data.)

Thanks, am hoping to grasp what's going on here so I can learn from it too! :)


I'll try to explain. Look at the last 2 rows containing 0209250 and 0209251. These share a relationship in that either product can be substituted for the other. The same is true for 0202295 and 0202294 (in the rows with ID = 2).

The first 6 rows all contain a pair chosen from any of these 3 products: 0224180, 0224181 and 0224190. Hence they share a relationship that any can be substituted for any other.

Therefore ID = Product Substitution Category (or something like that)

This is an excellent SQL problem to learn from as it is fiendishly diabolical to solve with high performance.



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 #1514116
Posted Wednesday, November 13, 2013 5:43 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:51 PM
Points: 141, Visits: 531
Wow, I never in a million years would have been able to interpret the data in that way. I still don't exactly understand how you were able to see this pattern there, or how you determined the parameters of it. Hats off, and thanks for the explanation. I can't even begin to fathom the posted SQL solution.


"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Post #1514118
Posted Wednesday, November 13, 2013 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: Yesterday @ 10:07 PM
Points: 3,617, Visits: 5,236
autoexcrement (11/13/2013)
Wow, I never in a million years would have been able to interpret the data in that way. I still don't exactly understand how you were able to see this pattern there, or how you determined the parameters of it. Hats off, and thanks for the explanation. I can't even begin to fathom the posted SQL solution.


The solution is even more fiendish if only limited substitutions are allowed.

Think about it another way. Each product in the two column table represents a node on a graph. Each row of the table represents an edge that connects two nodes in a specified direction. For the case of IDs 2 and 3, the 2 rows for each represent a single bi-directional edge on the graph. In a graph where all substitutions are allowed, there will exist exactly n (number of products) * (n - 1) edges. Limited substitution means there are less than that.

Having a little background in graph theory helps.

Edit: Added parentheses around (n - 1) to override the standard operator precedence for clarity.



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 #1514123
Posted Wednesday, November 13, 2013 6:21 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:51 PM
Points: 141, Visits: 531
I wish I had even a fraction of the clarity you have for these things.


"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Post #1514124
Posted Wednesday, November 13, 2013 6:23 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 15, 2013 12:59 PM
Points: 474, Visits: 187
Dwain,
I did remember to cast to varchar but may not have matched the actual length of varchar in the table. Can you tell me why this exact match would be important?
Thanks again for all your work. I won't get back to this until the AM in the US
Post #1514125
Posted Wednesday, November 13, 2013 6:52 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: Yesterday @ 10:07 PM
Points: 3,617, Visits: 5,236
lmeinke (11/13/2013)
Dwain,
I did remember to cast to varchar but may not have matched the actual length of varchar in the table. Can you tell me why this exact match would be important?
Thanks again for all your work. I won't get back to this until the AM in the US


I think the error message kind of tells you. Each column returned by the anchor and recursive legs of the rCTE must match in data type and length.



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 #1514129
Posted Thursday, November 14, 2013 7:03 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 15, 2013 12:59 PM
Points: 474, Visits: 187
Dwain,

The error message only mentions a type mismatch...nothing about length. I will continue to work on this this morning as time permit.

Again, thank you very much for your tenacity and I hope we can make this more efficient.

Lonnie
Post #1514261
Posted Thursday, November 14, 2013 9:15 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 15, 2013 12:59 PM
Points: 474, Visits: 187
OK...my issue again....sloppy typing. I had an nvarchar type vs. varchar type...sorry.

Dwain's solution does run however it returns double the number of rows from my original table and takes 36:37 to run vs. 8 minutes. Not sure what the difficulty is here as Dwain's solution is not cursor like and I would have expected much better run times.

Thank you again to Dwain.
Post #1514343
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse