November 10, 2010 at 3:05 pm
I am wanting to create a query from two tables.
The join is on string fields.
I have a wildcard match that works most of the time.
...
from LateExemptions LE
LEFT OUTER JOIN LateExemptionsV2 V2
ON LE.CARRIER = V2.CARRIER
AND LE.ExemptionString LIKE V2.ExemptionMsg + '%'
This works so that:
'Incorrect address'matches 'Incorrect address'
and
'Incorrect address - Street number' also matches 'Incorrect address'
Which is what I want. The wildcard on the end of the string.
The problem is on short strings. For example.
'DELIVERY RESCHEDULED' matches on both 'DELIVERY' and 'DELIVERY RESCHEDULED'.
'DELIVERY' and 'DELIVERY RESCHEDULED' are both valid strings in table V2 which mean completely different things.
I want 'DELIVERY RESCHEDULED' to match just against 'DELIVERY RESCHEDULED' and not on 'DELIVERY'. which is a "more complete" match.
Any suggestions?
I've looked at CASE within the JOIN but I might be missing something.
In the situation above, I need the longer of the two strings if a match produces multiple matches.
November 10, 2010 at 3:13 pm
You're going to have a tough time with doing this *and* keeping it optimized. Pretty much a one or the other scenario.
The other difficulty you'll have in this is excepting for DELIVERY/DELIVERY RESCHEDULED on a generic basis also means that you would only get 'Incorrect address - Street doesn't exist' out of the possible following results:
'Incorrect address - Street number'
'Incorrect address - Street doesn't exist'
'Incorrect address - Invalid Zipcode'
Which, from my understanding, is something you don't want. You're probably going to have to figure out a specific lookup table that could fill a drop down that would look something like:
DELIVERYDELIVERY
DELVIERY RESCHEDULEDDELVIERY RESCHEDULED
INCORRECT ADDRESSINCORRECT ADDRESS%
Add in more items as needed. A little dynamic SQL to deal with your like vs. = statements and away you go.
This is not a pretty solution. Not sure there IS a good way to get where you're going.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply