Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 identifying many to many relationship between columns Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, November 13, 2013 1:01 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Yesterday @ 4:11 PM Points: 182, Visits: 775
 Can someone please help me to catch up? I feel I'm not understanding the issue. Using the OP's initial data:ITEM1 ITEM2 ID0224180 0224181 10224180 0224190 10224181 0224180 10224181 0224190 10224190 0224180 10224190 0224181 10202294 0202295 20202295 0202294 20209250 0209251 30209251 0209250 3Why, 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 Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 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 1Types 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!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1514115
 Posted Wednesday, November 13, 2013 5:34 PM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 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 ID0224180 0224181 10224180 0224190 10224181 0224180 10224181 0224190 10224190 0224180 10224190 0224181 10202294 0202295 20202295 0202294 20209250 0209251 30209251 0209250 3Why, 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!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1514116
 Posted Wednesday, November 13, 2013 5:43 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Yesterday @ 4:11 PM Points: 182, Visits: 775
 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 Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 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!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1514123
 Posted Wednesday, November 13, 2013 6:21 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Yesterday @ 4:11 PM Points: 182, Visits: 775
 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
 Mr or Mrs. 500 Group: General Forum Members Last Login: Wednesday, June 29, 2016 7:41 AM Points: 589, Visits: 197
 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 Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 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 USI 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!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1514129
 Posted Thursday, November 14, 2013 7:03 AM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Wednesday, June 29, 2016 7:41 AM Points: 589, Visits: 197
 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
 Mr or Mrs. 500 Group: General Forum Members Last Login: Wednesday, June 29, 2016 7:41 AM Points: 589, Visits: 197
 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

 Permissions