Archives

Removing Null Country Values From the Syaddr Table

Unfortunately M2M does not automatically populate the Country field on many screens when a US State is selected. This can cause problems in reporting when an executive wants sales broken down by country and many of them are grouped under Null.

Anyway, if you want to see how many addresses are missing “United States” you can run the following script. Once again, please read my standard disclaimer.

select * from syaddr 
WHERE FCCOUNTRY = ''
AND fcstate IN ('AL','AK','AZ','AR',
                       'CA','CO','CT','DE',
                       'DC','FL','GA','HI',
                       'ID','IL','IN','IA',
                       'KS','KY','LA','ME',
                       'MD','MA','MI','MN',
                       'MS','MO','MT','NE',
                       'NV','NH','NJ','NM',
                       'NY','NC','ND','OH',
                       'OK','OR','PA','RI',
                       'SC','SD','TN','TX',
                       'UT','VT','VA','WA',
                       'WV','WI','WY')

If you want to fix them automatically, you may want to run the following script. It will update all of the blank country records fields that are associated with US states.

UPDATE syaddr
SET FCCOUNTRY = 'United States'
WHERE FCCOUNTRY = ''
AND fcstate IN ('AL','AK','AZ','AR',
                       'CA','CO','CT','DE',
                       'DC','FL','GA','HI',
                       'ID','IL','IN','IA',
                       'KS','KY','LA','ME',
                       'MD','MA','MI','MN',
                       'MS','MO','MT','NE',
                       'NV','NH','NJ','NM',
                       'NY','NC','ND','OH',
                       'OK','OR','PA','RI',
                       'SC','SD','TN','TX',
                       'UT','VT','VA','WA',
                       'WV','WI','WY')

Related posts:

2 comments to Removing Null Country Values From the Syaddr Table

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>