HairyMonkeyMan
September 9th, 2008, 04:21 AM
Greetings gurus.
I am writting a query for a report (on data quality). I am stuck on the last part.
I have a table containing all uk postcodes and addresses which I am checking against our data by left joining onto the contact record. If theres a match, I check whether the first line of the address corresponds. If there is no match, it might be because the address is in southern ireland (who don't use postcodes).
For a southern address to be valid it must contain a county name (I have all of these in a table). I want to join the address together in a variable and check whether the county name is found in the variable (checking against each row in the county table).
I originally thought I could do this with a stored proceedure using a cursor.. although the cursor must be before the start of the query which wont work as I have no address at that stage.
Any ideas? Is this even a problem MySQL can solve?
Thanks
This is the query:
select
vcl.contact_id as contact_id,
dept_link.department_code as branch_code,
-- Check email address
case when email_address regexp '\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*' then 0 else 1 end as invalid_email,
-- Check mobile number
case when ((tel_num_mobile like '07%' or tel_num_mobile like '00447%' or tel_num_mobile like '+447%') and tel_num_mobile regexp '^(07|00447|\\+447)\d{9}$')
or ((tel_num_mobile like '08%' or tel_num_mobile like '003538%' or tel_num_mobile like '+3538%') and tel_num_mobile regexp '^(08|003538|\\+3538)\d{7,9}$')
or ((tel_num_mobile like '9%' or tel_num_mobile like '00479%' or tel_num_mobile like '+479') and tel_num_mobile regexp '^(9|00479|\\+479)\d{7}$')
-- or (mobile_no = 'UNKNOWN')
then 1 else 0 end as invalid_mobile,
-- Check Postcode
case when not postcode_record.post_code_key is null then 0 else 1 end as invalid_postcode,
-- Check address
case when not postcode_record.post_code_key is null then
case when upper(contact_record.Address_1) LIKE concat('%',postcode_record.POST_CODE_ADD1,'%') then 0 else 1 end
else
-- validate addresses without postcode (i.e southern ireland).
end as invalid_address
from vehicle_contact_link vcl
inner join
(
select
dept_id,
contact_id,
vehicle_id,
max(modified_date) as modified_date
from vehicle_contact_link
group by vehicle_id
) vclsub
on vcl.dept_id = vclsub.dept_id
and vcl.contact_id = vclsub.contact_id
and vcl.vehicle_id = vclsub.vehicle_id
inner join central.department_link dept_link on vcl.dept_id = dept_link.department_code
inner join contact_record on vcl.contact_id = contact_record.contact_id
-- Postcode/Address check
left join postcode_record on postcode_record.POST_CODE_KEY = contact_record.Postcode
where vcl.modified_date between StartDate and EndDate
group by contact_id;
I am writting a query for a report (on data quality). I am stuck on the last part.
I have a table containing all uk postcodes and addresses which I am checking against our data by left joining onto the contact record. If theres a match, I check whether the first line of the address corresponds. If there is no match, it might be because the address is in southern ireland (who don't use postcodes).
For a southern address to be valid it must contain a county name (I have all of these in a table). I want to join the address together in a variable and check whether the county name is found in the variable (checking against each row in the county table).
I originally thought I could do this with a stored proceedure using a cursor.. although the cursor must be before the start of the query which wont work as I have no address at that stage.
Any ideas? Is this even a problem MySQL can solve?
Thanks
This is the query:
select
vcl.contact_id as contact_id,
dept_link.department_code as branch_code,
-- Check email address
case when email_address regexp '\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*' then 0 else 1 end as invalid_email,
-- Check mobile number
case when ((tel_num_mobile like '07%' or tel_num_mobile like '00447%' or tel_num_mobile like '+447%') and tel_num_mobile regexp '^(07|00447|\\+447)\d{9}$')
or ((tel_num_mobile like '08%' or tel_num_mobile like '003538%' or tel_num_mobile like '+3538%') and tel_num_mobile regexp '^(08|003538|\\+3538)\d{7,9}$')
or ((tel_num_mobile like '9%' or tel_num_mobile like '00479%' or tel_num_mobile like '+479') and tel_num_mobile regexp '^(9|00479|\\+479)\d{7}$')
-- or (mobile_no = 'UNKNOWN')
then 1 else 0 end as invalid_mobile,
-- Check Postcode
case when not postcode_record.post_code_key is null then 0 else 1 end as invalid_postcode,
-- Check address
case when not postcode_record.post_code_key is null then
case when upper(contact_record.Address_1) LIKE concat('%',postcode_record.POST_CODE_ADD1,'%') then 0 else 1 end
else
-- validate addresses without postcode (i.e southern ireland).
end as invalid_address
from vehicle_contact_link vcl
inner join
(
select
dept_id,
contact_id,
vehicle_id,
max(modified_date) as modified_date
from vehicle_contact_link
group by vehicle_id
) vclsub
on vcl.dept_id = vclsub.dept_id
and vcl.contact_id = vclsub.contact_id
and vcl.vehicle_id = vclsub.vehicle_id
inner join central.department_link dept_link on vcl.dept_id = dept_link.department_code
inner join contact_record on vcl.contact_id = contact_record.contact_id
-- Postcode/Address check
left join postcode_record on postcode_record.POST_CODE_KEY = contact_record.Postcode
where vcl.modified_date between StartDate and EndDate
group by contact_id;