pears.GetCompanyAddressOnLine

CREATE FUNCTION "pears"."GetCompanyAddressOnLine"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "reqcompanyid" CHAR(20) ) 
RETURNS CHAR(250)
BEGIN
  DECLARE "AddressOnLine" CHAR(250);
  DECLARE "addr1" CHAR(40);
  DECLARE "addr2" CHAR(40);
  DECLARE "addr3" CHAR(40);
  DECLARE "town" CHAR(30);
  DECLARE "county" CHAR(30);
  DECLARE "country" CHAR(30);
  DECLARE "postcode" CHAR(30);
  SELECT "company"."addr1","company"."addr2","company"."addr3","company"."town","company"."county","company"."country","company"."postcode" INTO "addr1",
    "addr2","addr3","town","county","country","postcode" FROM "company"
    WHERE "companyid" = "reqcompanyid";
  IF "isnull"("addr1",'') <> '' THEN SET "AddressOnLine" = "AddressOnLine" || "trim"("addr1")
  END IF;
  IF "isnull"("AddressOnLine",'') <> '' AND "isnull"("addr2",'') <> '' THEN SET "AddressOnLine" = "AddressOnLine" || ', '
  END IF;
  IF "isnull"("addr2",'') <> '' THEN SET "AddressOnLine" = "AddressOnLine" || "trim"("addr2")
  END IF;
  IF "isnull"("AddressOnLine",'') <> '' AND "isnull"("addr3",'') <> '' THEN SET "AddressOnLine" = "AddressOnLine" || ', '
  END IF;
  IF "isnull"("addr3",'') <> '' THEN SET "AddressOnLine" = "AddressOnLine" || "trim"("addr3")
  END IF;
  IF "isnull"("AddressOnLine",'') <> '' AND "isnull"("town",'') <> '' THEN SET "AddressOnLine" = "AddressOnLine" || ', '
  END IF;
  IF "isnull"("town",'') <> '' THEN SET "AddressOnLine" = "AddressOnLine" || "trim"("town")
  END IF;
  IF "isnull"("AddressOnLine",'') <> '' AND "isnull"("county",'') <> '' THEN SET "AddressOnLine" = "AddressOnLine" || ', '
  END IF;
  IF "isnull"("county",'') <> '' THEN SET "AddressOnLine" = "AddressOnLine" || "trim"("county")
  END IF;
  IF "isnull"("AddressOnLine",'') <> '' AND "isnull"("country",'') <> '' THEN SET "AddressOnLine" = "AddressOnLine" || ', '
  END IF;
  IF "isnull"("country",'') <> '' THEN SET "AddressOnLine" = "AddressOnLine" || "trim"("country")
  END IF;
  IF "isnull"("AddressOnLine",'') <> '' AND "isnull"("postcode",'') <> '' THEN SET "AddressOnLine" = "AddressOnLine" || ' '
  END IF;
  IF "isnull"("postcode",'') <> '' THEN SET "AddressOnLine" = "AddressOnLine" || "trim"("postcode")
  END IF;
  RETURN "AddressOnLine"
END