====== pears.tidyaddresswithoutpostcode ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
create procedure "pears"."tidyaddresswithoutpostcode"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "persid" char(20) ) 
begin
  update "person" set "addr1" = "nullif"("trim"("addr1"),''),"addr2" = "nullif"("trim"("addr2"),''),"addr3" = "nullif"("trim"("addr3"),''),
    "town" = "nullif"("trim"("town"),''),"county" = "nullif"("trim"("county"),''),"country" = "nullif"("trim"("country"),''),"postcode" = "nullif"("trim"("postcode"),'') where "person"."personid" = "persid";
  update "person" set "addr1" = "replace"("addr1",'  ',' ') where "person"."personid" = "persid";
  update "person" set "addr1" = "replace"("addr1",'  ',' ') where "person"."personid" = "persid";
  update "person" set "addr2" = "replace"("addr2",'  ',' ') where "person"."personid" = "persid";
  update "person" set "addr2" = "replace"("addr2",'  ',' ') where "person"."personid" = "persid";
  update "person" set "addr3" = "replace"("addr3",'  ',' ') where "person"."personid" = "persid";
  update "person" set "addr3" = "replace"("addr3",'  ',' ') where "person"."personid" = "persid";
  update "person" set "town" = "replace"("town",'  ',' ') where "person"."personid" = "persid";
  update "person" set "town" = "replace"("town",'  ',' ') where "person"."personid" = "persid";
  update "person" set "county" = "replace"("county",'  ',' ') where "person"."personid" = "persid";
  update "person" set "county" = "replace"("county",'  ',' ') where "person"."personid" = "persid";
  update "person" set "country" = "replace"("country",'  ',' ') where "person"."personid" = "persid";
  update "person" set "country" = "replace"("country",'  ',' ') where "person"."personid" = "persid";
  update "person" set "country" = "county","county" = null where "personid" = "persid" and "county" = any(select "place" from "places" where "code" = 'n');
  update "person" set "country" = "town","town" = null where "personid" = "persid" and "town" = any(select "place" from "places" where "code" = 'n');
  update "person" set "country" = "addr3","addr3" = null where "personid" = "persid" and "addr3" = any(select "place" from "places" where "code" = 'n');
  update "person" set "country" = "addr2","addr2" = null where "personid" = "persid" and "addr2" = any(select "place" from "places" where "code" = 'n');
  update "person" set "country" = "addr1","addr1" = null where "personid" = "persid" and "addr1" = any(select "place" from "places" where "code" = 'n');
  update "person" set "county" = "country","country" = null where "person"."personid" = "persid" and "county" is null and "country" = any(select "place" from "places" where "code" = 'c');
  update "person" set "county" = "town","town" = null where "person"."personid" = "persid" and "county" is null and "town" = any(select "place" from "places" where "code" = 'c');
  update "person" set "county" = "addr3","addr3" = null where "person"."personid" = "persid" and "county" is null and "addr3" = any(select "place" from "places" where "code" = 'c');
  update "person" set "county" = "addr2","addr2" = null where "person"."personid" = "persid" and "county" is null and "addr2" = any(select "place" from "places" where "code" = 'c');
  update "person" set "county" = "addr1","addr1" = null where "person"."personid" = "persid" and "county" is null and "addr1" = any(select "place" from "places" where "code" = 'c');
  update "person" set "town" = "county","county" = null where "person"."personid" = "persid" and "town" is null and "county" = any(select "place" from "places" where "code" = 't');
  update "person" set "addr1" = "string"("addr1",', ',"addr2"),"addr2" = "addr3","addr3" = "town","town" = "county","county" = null where "person"."personid" = "persid"
    and "town" is not null and "county" = any(select "place" from "places" where "code" = 't');
  update "person" set "town" = "addr3","addr3" = null where "person"."personid" = "persid" and "town" is null and "addr3" = any(select "place" from "places" where "code" = 't');
  update "person" set "town" = "addr2","addr2" = null where "person"."personid" = "persid" and "town" is null and "addr2" = any(select "place" from "places" where "code" = 't');
  update "person" set "town" = "addr1","addr1" = null where "person"."personid" = "persid" and "town" is null and "addr2" is null and "addr1" = any(select "place" from "places" where "code" = 't');
  update "person" set "town" = "addr3","addr3" = null where "person"."personid" = "persid" and "town" is null and "addr3" is not null;
  update "person" set "town" = "addr2","addr2" = null where "person"."personid" = "persid" and "town" is null and "addr2" is not null
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."tidyaddresswithoutpostcode" IS 
{create procedure tidyaddresswithoutpostcode 

/* Application Maintained Function / Procedure - DO NOT EDIT*/

(in persid char(20))
begin
  update person set addr1 = nullif(trim(addr1),''),addr2 = nullif(trim(addr2),''),addr3 = nullif(trim(addr3),''), 
 town = nullif(trim(town),''),county = nullif(trim(county),''),country = nullif(trim(country),''),postcode = nullif(trim(postcode),'') where person.personid = persid;
  update person set addr1 = replace(addr1,'  ',' ') where person.personid = persid;
  update person set addr1 = replace(addr1,'  ',' ') where person.personid = persid;
  update person set addr2 = replace(addr2,'  ',' ') where person.personid = persid;
  update person set addr2 = replace(addr2,'  ',' ') where person.personid = persid;
  update person set addr3 = replace(addr3,'  ',' ') where person.personid = persid;
  update person set addr3 = replace(addr3,'  ',' ') where person.personid = persid;
  update person set town = replace(town,'  ',' ') where person.personid = persid;
  update person set town = replace(town,'  ',' ') where person.personid = persid;
  update person set county = replace(county,'  ',' ') where person.personid = persid;
  update person set county = replace(county,'  ',' ') where person.personid = persid;
  update person set country = replace(country,'  ',' ') where person.personid = persid;
  update person set country = replace(country,'  ',' ') where person.personid = persid;
  update person set country = county,county = null where personid = persid and county = any(select place from places where code = 'n');
  update person set country = town,town = null where personid = persid and town = any(select place from places where code = 'n');
  update person set country = addr3,addr3 = null where personid = persid and addr3 = any(select place from places where code = 'n');
  update person set country = addr2,addr2 = null where personid = persid and addr2 = any(select place from places where code = 'n');
  update person set country = addr1,addr1 = null where personid = persid and addr1 = any(select place from places where code = 'n');
  update person set county = country,country = null where person.personid = persid and county is null and country = any(select place from places where code = 'c');
  update person set county = town,town = null where person.personid = persid and county is null and town = any(select place from places where code = 'c');
  update person set county = addr3,addr3 = null where person.personid = persid and county is null and addr3 = any(select place from places where code = 'c');
  update person set county = addr2,addr2 = null where person.personid = persid and county is null and addr2 = any(select place from places where code = 'c');
  update person set county = addr1,addr1 = null where person.personid = persid and county is null and addr1 = any(select place from places where code = 'c');
  update person set town = county,county = null where person.personid = persid and town is null and county = any(select place from places where code = 't');
  update person set addr1 = string(addr1,', ',addr2),addr2 = addr3,addr3 = town,town = county,county = null where person.personid = persid 
 and town is not null and county = any(select place from places where code = 't');
  update person set town = addr3,addr3 = null where person.personid = persid and town is null and addr3 = any(select place from places where code = 't');
  update person set town = addr2,addr2 = null where person.personid = persid and town is null and addr2 = any(select place from places where code = 't');
  update person set town = addr1,addr1 = null where person.personid = persid and town is null and addr2 is null and addr1 = any(select place from places where code = 't');
  update person set town = addr3,addr3 = null where person.personid = persid and town is null and addr3 is not null;
  update person set town = addr2,addr2 = null where person.personid = persid and town is null and addr2 is not null
end
}
</code>