May 15, 2012 at 12:59 pm
Is it possible to add case logic when identifying the join?
Here is logic of what I am trying to accomplish:
LEFT JOIN PART ON WO.ID =
CASE WHEN LEN(PART.WO_ID)>10 THEN SUBSTRING(PART.WO_ID,3,6)
WHEN LEN(PART.WO_ID)<10 THEN SUBSTRING(PART.WO_ID,3,5)
Any help will be greatly appreciated.
May 15, 2012 at 1:03 pm
Yep, that would work (though the CASE syntax is incorrect there). Its defnitely do-able.
May 15, 2012 at 2:36 pm
Can you help me with the syntax?
May 15, 2012 at 2:40 pm
Add an END to your CASE statement.
Jared
CE - Microsoft
May 15, 2012 at 3:09 pm
Wow! I was that close huh. Thanks for the help.
May 15, 2012 at 6:34 pm
Note that the computed result after all THENs (or ELSE) must match in datatype.
I don't see that as a problem in your CASE (:-)) but I thought I'd mention it.
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
May 16, 2012 at 6:47 am
Thanks for all the help.
May 16, 2012 at 7:31 am
You might also want to add the CASE statement as a computed column on your PART table and create an index on that computed column.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply