pears.FormatAddress

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."FormatAddress" IS 
{CREATE FUNCTION FormatAddress 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
(IN name CHAR(100),IN addr1 CHAR(50),IN addr2 CHAR(50),IN addr3 CHAR(50),IN town CHAR(50),IN county CHAR(50),IN country CHAR(50),IN postcode CHAR(50),
IN Capitalise SMALLINT,IN STYLE CHAR(1)) /* Set the seperator for block or line */ /* If capitalise option is set, capitalise town. */ /* Build Formatted Address */
RETURNS CHAR(300) BEGIN DECLARE FormattedAddress CHAR(300);DECLARE Separator CHAR(2);DECLARE PrePostCodeSeparator CHAR(2);SET FormattedAddress='';
  IF STYLE = 'B' THEN SET Separator="char"(13)+"char"(10);
    SET PrePostCodeSeparator=Separator
  ELSE SET Separator=', ';
    SET PrePostCodeSeparator=' '
  END IF;
  IF Capitalise = 1 THEN SET town=UPPER(Town)
  END IF;
  IF isnull(name,'') <> '' THEN SET FormattedAddress=FormattedAddress || TRIM(name)
  END IF;
  IF isnull(FormattedAddress,'') <> '' AND isnull(addr1,'') <> '' THEN SET FormattedAddress=FormattedAddress || Separator
  END IF;
  IF isnull(addr1,'') <> '' THEN SET FormattedAddress=FormattedAddress || TRIM(addr1)
  END IF;
  IF isnull(FormattedAddress,'') <> '' AND isnull(addr2,'') <> '' THEN SET FormattedAddress=FormattedAddress || Separator
  END IF;
  IF isnull(addr2,'') <> '' THEN SET FormattedAddress=FormattedAddress || TRIM(addr2)
  END IF;
  IF isnull(FormattedAddress,'') <> '' AND isnull(addr3,'') <> '' THEN SET FormattedAddress=FormattedAddress || Separator
  END IF;
  IF isnull(addr3,'') <> '' THEN SET FormattedAddress=FormattedAddress || TRIM(addr3)
  END IF;
  IF isnull(FormattedAddress,'') <> '' AND isnull(town,'') <> '' THEN SET FormattedAddress=FormattedAddress || Separator
  END IF;
  IF isnull(town,'') <> '' THEN SET FormattedAddress=FormattedAddress || TRIM(town)
  END IF;
  IF isnull(FormattedAddress,'') <> '' AND isnull(county,'') <> '' THEN SET FormattedAddress=FormattedAddress || Separator
  END IF;
  IF isnull(county,'') <> '' THEN SET FormattedAddress=FormattedAddress || TRIM(county)
  END IF;
  IF isnull(FormattedAddress,'') <> '' AND isnull(country,'') <> '' THEN SET FormattedAddress=FormattedAddress || Separator
  END IF;
  IF isnull(country,'') <> '' THEN SET FormattedAddress=FormattedAddress || TRIM(country)
  END IF;
  IF isnull(FormattedAddress,'') <> '' AND isnull(postcode,'') <> '' THEN SET FormattedAddress=FormattedAddress || PrePostCodeSeparator
  END IF;
  IF isnull(postcode,'') <> '' THEN SET FormattedAddress=FormattedAddress || TRIM(postcode)
  END IF;
  RETURN FormattedAddress END
}