February 25, 2009 at 11:36 am
I am maintaining a system that compares Customer address data with a master table containing address ranges. A vast simplification of my current task might be:
A customer may live at 123 Oak Street, and I need to find the address range row from the master table that the customers address "fits" into.
The problem arises in that the table data is Char, including the address "numbers" I wish to compare.
Consider this :
CREATE TABLE #address_ranges (
Street char(20),
low_add char(10),
high_add char(10))
CREATE TABLE #cust_address (
cust_num int,
cust_name char(50),
Street char(20),
add_number char(10))
INSERT INTO #address_ranges
(Street, low_add, high_add)
SELECT 'Oak', '1', '2100'
UNION ALL
SELECT 'Oak', '1200', '1999'
UNION ALL
SELECT 'Oak', '18300', '99999'
INSERT INTO #cust_address
(cust_num, cust_name, Street, add_number)
SELECT 1, 'My Restaurant', 'Oak', '19207'
UNION ALL
SELECT 2, 'My Gas', 'Oak', '2101'
UNION ALL
SELECT 3, 'My coffee', 'Oak', '9239'
If I now query the tables looking for matches, customer "My Restaurant" gets hits in the #Address_ranges table based on CHAR comparisons of the street number, giving incorrect results.
SELECT *
FROM #cust_address ca
INNER JOIN #address_ranges ad
on ca.Street = ad.Street
AND ca.add_number BETWEEN ad.low_add AND ad.high_add
So I simply converted the values in question to INTs and get the desired result:
SELECT *
FROM #cust_address ca
INNER JOIN #address_ranges ad
on ca.Street = ad.Street
AND convert(int,ca.add_number) BETWEEN convert(int,ad.low_add) AND convert(int,ad.high_add)
Along comes my root problem. The Char nature of the #cust_address add_number column has allowed legacy data into the add_number column that is not numeric. For example :
INSERT INTO #cust_address
(cust_num, cust_name, Street, add_number)
SELECT 4, 'My Corner Store', 'Oak', '455 1/2'
Now my improved comparison criteria blows up with a non-numeric in the add_number column. What I had hoped to do was to replace the JOIN (or use a WHERE clause instead) that used a CASE statement to determine if the CONVERTED values or original values should be used for the comparison, something like:
SELECT *
FROM #cust_address ca
INNER JOIN #address_ranges ad
on ca.Street = ad.Street
WHERE
CASE isnumeric(ca.add_number)
WHEN 1
THEN convert(int,ca.add_number) BETWEEN convert(int,ad.low_add) AND convert(int,ad.high_add)
ELSE
ca.add_number BETWEEN ad.low_add AND ad.high_add
END
I realize this syntax is faulty, but I use it as pseudo-code to illustrate what I am trying to accomplish. I need to incorporate a different comparison criteria based on the contents of the add_number column. I am hitting a brain block on this one, although it seems it should be a simple work-around.
Suggestions appreciated.
February 25, 2009 at 12:31 pm
Does this help? The code below does nothing for character comparisons - it just matches integer values...
One thing to note with ISNUMERIC - it will return true for values like "$" and "." which is because ISNUMERIC returns 1 for the money datatype (in addition to the expected int, decimal and float datatypes)...
In the quite impossible case that someone's add_number is "$100" ISNUMERIC will return 1 and the code will fail at the conversion to int
USE tempdb;
GO
CREATE TABLE #address_ranges (
Street char(20),
low_add char(10),
high_add char(10))
CREATE TABLE #cust_address (
cust_num int,
cust_name char(50),
Street char(20),
add_number char(10))
INSERT INTO #address_ranges
(Street, low_add, high_add)
SELECT 'Oak', '1', '2100'
UNION ALL
SELECT 'Oak', '1200', '1999'
UNION ALL
SELECT 'Oak', '18300', '99999'
INSERT INTO #cust_address
(cust_num, cust_name, Street, add_number)
SELECT 1, 'My Restaurant', 'Oak', '19207'
UNION ALL
SELECT 2, 'My Gas', 'Oak', '2101'
UNION ALL
SELECT 3, 'My coffee', 'Oak', '9239'
UNION ALL
-- inserting character values in address
SELECT 3, 'My coffee', 'Oak', 'CharVal'
SELECT * FROM #address_ranges
SELECT * FROM #cust_address
--SELECT *
--FROM #cust_address ca
--INNER JOIN #address_ranges ad
--ON ca.Street = ad.Street
--AND CAST(ca.add_number as int) BETWEEN
-- CAST(ad.low_add as int) AND CAST(ad.high_add as int)
SELECT *
FROM #cust_address ca
INNER JOIN #address_ranges ad
ON ca.Street = ad.Street
WHERE
(CASE ISNUMERIC(ca.add_number) WHEN 1 THEN
CASE WHEN CAST(ca.add_number as int) BETWEEN
CAST(ad.low_add as int) AND CAST(ad.high_add as int)
THEN 1
ELSE 0
END
END)=1
DROP TABLE #address_ranges
DROP TABLE #cust_address
February 25, 2009 at 12:39 pm
Assuming you're using SQL 2005 from the forum posted in, try this:
;with
AddNumCTE (ID, AddNum) as
(select cust_num, left(add_number, charindex(' ', add_number, 0))
from #cust_address
where isnumeric(left(add_number, charindex(' ', add_number, 0))) = 1),
AddStrCTE (ID, AddStr) as
(select cust_num, add_number
from #cust_address
where isnumeric(left(add_number, charindex(' ', add_number, 0))) = 0)
select *
from #cust_address
left outer join AddNumCTE
on cust_num = AddNumCTE.ID
left outer join AddStrCTE
on cust_num = AddStrCTE.ID
inner join #address_ranges
on AddNum >= cast(low_add as int)
and AddNum <= cast(high_add as int)
or AddStr >= low_add
and AddStr <= high_add;
Same sample data you used.
Will that accomplish what you need?
It won't work in 2000, but you could accomplish the same thing there with standard derived tables in the From clause.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply