SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW al_bi_location_view
AS
SELECT     location_uuid,'0x'+UPPER(dbo.hex(location_uuid)) AS location_uuid_display, location_name, inactive, pri_phone_number, fax_number, address_1, address_2,
                      mail_address_1, mail_address_2, mail_address_3, city, state, address_3, mail_address_6, mail_address_5, mail_address_4, address_6, address_5,
                      address_4, primary_contact_uuid, zip, country, county, geo_coord_type, geo_coords, contact_address_flag, creation_user, site_id, creation_date,
                      comments, last_update_user, last_update_date, version_number, exclude_registration, delete_time, location_type_id, parent_location_uuid,
                      organization_uuid, tenant, region_id,
                       (mail_address_1 +',' + mail_address_2 +',' +mail_address_3 +',' + mail_address_4 +',' + mail_address_5 +',' + mail_address_6) mail_address,
(
(case when address_1 is null then N' ' else  address_1 end) +
(case when address_2 is null then N' ' else  address_2 end) +
(case when address_3 is null then N' ' else  address_3 end) +
(case when address_4 is null then N' ' else  address_4 end) +
(case when address_5 is null then N' ' else  address_5 end) +
(case when address_6 is null then N' ' else  address_6 end )
) address
FROM        ca_location
GO