June 25, 2004 at 9:24 am
Good Morning,
I have a database that tracks enrollment transactions. The transactions are ordered by a datetime field. The table fields include Address, City,zipcode, region, enrolldate, disenrolldate. The problem i am encountering is that the region field is being updated by a update query that joins a Zipcode_City Table with the enrollment table. However there are many new zipcodes or zipcodes with missing digit(s) in the enrollment table that do not match does in the Zipcode_City Table and as a result A Null value is inserted in the Region field for those records where no match is found. QUERY:
Update enrolltemp
set region = C.REGION
from Enrolltemp E, COUNTY_LISTING C
join Enrolltemp ON enrolltemp.[Case Head Zip]= c.ZIPCODE
The Fix for this problem is the the prefix of the zipocde can be used to distinguish which region a member belongs to e.g. East Region - Zipcode like '02%' , West Region - Zipcode Like '01%' , Southeast Region - Zipcode like '03%' Or Zipcode like '04%'. How can I prevent a Null value from being inserted into the region field during the update, and automatically use the Fix logic to remedy the problem.
Thanks,
Anthony M
Anthony Malone
June 25, 2004 at 10:26 am
Have you thought about creating a table to act as a lookup. That way if your insert fails to find a record, you can reference the lookup table. Alternatively, you may want to reference the lookup table all the time. It is better to create a lookup table than to use code to deduce the zipcodes because it will be easier to maintain.
June 25, 2004 at 12:57 pm
After you have given you first update statements give a second update statement with a select CASE like this
Update enrolltemp
set region = C.REGION
from Enrolltemp E, COUNTY_LISTING C
join Enrolltemp ON enrolltemp.[Case Head Zip]= c.ZIPCODE
Update enrolltemp
set region =
(select case when enrollTemp.[Case Head Zip] like '01%' then 'West Region'
when enrollTemp.[Case Head Zip] like '02%' then 'East Region'
when enrollTemp.[Case Head Zip] like '03%' or enrollTemp.[Case Head Zip] like '03%' then 'SE Region'
end from Enrollment)
where region is NULL
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply