Hello,
I have checked some customer's geo area code against the addresses that are displayed in Tessitura. Most are correct but many are still being assigned to locations that make no sense to me.
For example,
Canadian's address was assigned in geo area 4 (foreign)
City: Niagara on the Lake, Post code L0S1J0, and Country 32 is in geo area 4, which is foreign in tr_geo_area
The same thing happended to customers in 1146 different cities in Canada, they were all assigned foreign(geo_area_code)
I found that update/insert triggers attached to t_address is using the procedure LP_UPDATE_GEOAREA to update and insert geo area code, which could explain why I get the error geo area code..
Why sometimes I could get a correct geo area code...not always wrong...
Any suggestions on why the errors are occurring?
Thank you very much in advance!
/*****************LP_UPDATE_GEOAREA
USE [impresario]
GO
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF
ALTER PROCEDURE [dbo].[LP_UPDATE_GEOAREA]
(
@address_no int,
@postal_code varchar(10),
@country int
)
AS
Set NoCount On -- added auto CWR 8/9/2001
UPDATE t_address
SET geo_area =
CASE
when country > 1 then 4
when country < 1 then 4
when postal_code is null then 6
when postal_code = '' then 6
when country = 1 and IsNumeric(substring(postal_code, 1, 5)) = 0 then 0
when country = 1 and charindex('.', substring(postal_code, 1, 5)) > 0 then 0
when country = 1 and charindex('-', substring(postal_code, 1, 5)) > 0 then 0
when country = 1 and charindex('e', substring(postal_code, 1, 5)) > 0 then 0
when country = 1 and charindex('d', substring(postal_code, 1, 5)) > 0 then 0
when country = 1 and (convert(int, substring(postal_code, 1, 5)) >= 10000 and convert(int, substring(postal_code, 1,
5)) <= 10199) then 1
when country = 1 and (convert(int, substring(postal_code, 1, 5)) >= 06400 and convert(int, substring(postal_code, 1,
5)) <= 07999) then 2
when country = 1 and (convert(int, substring(postal_code, 1, 5)) >= 08800 and convert(int, substring(postal_code, 1,
5)) <= 08999) then 2
when country = 1 and (convert(int, substring(postal_code, 1, 5)) >= 10200 and convert(int, substring(postal_code, 1,
5)) <= 11999) then 2
when country = 1 and (convert(int, substring(postal_code, 1, 5)) >= 01000 and convert(int, substring(postal_code, 1,
5)) <= 02999) then 3
when country = 1 and (convert(int, substring(postal_code, 1, 5)) >= 06000 and convert(int, substring(postal_code, 1,
5)) <= 06399) then 3
when country = 1 and (convert(int, substring(postal_code, 1, 5)) >= 12000 and convert(int, substring(postal_code, 1,
5)) <= 12799) then 3
when country = 1 and (convert(int, substring(postal_code, 1, 5)) >= 17100 and convert(int, substring(postal_code, 1,
5)) <= 17699) then 3
when country = 1 and (convert(int, substring(postal_code, 1, 5)) >= 18000 and convert(int, substring(postal_code, 1,
5)) <= 22399) then 3
else 5
END
WHERE address_no = @address_no
*****************
Your code is setting any non-US address to "foreign." Your LP_UPDATE_GEOAREA probably needs to be revised. Also, it seems to be missing the "@" sign before country and postal_code.
Do you have a setting in TR_GEO_AREA with id > 6? For instance, if, say, there is a row with id=7 and a description of Canada, you could amend LP_UPDATE_GEOAREA at the top right after the opening CASE:
when @country=32 then 7
You'd have to change following lines as well, depending on what you are trying to achieve with GEOAREA. For example are you interested in knowing, for Canadian addresses, which ones are local and which ones aren't? For Florida Grand Opera, I assigned all Florida addresses to a county, then leaving the default settings for the other GEOAREA settings. Our LP_UPDATE_GEOAREA has a long string of "when @country=1 and @postal_code like '32013%' then 42" statements that set various postal codes to counties in Florida that we entered in TR_GEO_AREA. You could do something like:
when @country=32 and @postal_code like 'V%' then 8
(assuming 8 refers to a line in TR_GEO_AREA for British Columbia).
Some enterprising Canadian company must have done this already!
Lucie
Hello Lucie,
Thank you very much for your help!