CREATE VIEW [dbo].[al_bi_contact_view]
AS
SELECT     contact_uuid,dbo.al_bi_bintohexstr(contact_uuid) AS contact_uuid_display, middle_name, alias, last_name, first_name, pri_phone_number, alt_phone_number, fax_number,
                      mobile_phone_number, pager_number, email_address, location_uuid, floor_location, pager_email_address, room_location, contact_type, inactive,
                      creation_user, creation_date, last_update_user, last_update_date, version_number, department, comments, company_uuid, organization_uuid,
                      admin_organization_uuid, alternate_identifier, job_title, job_function, mail_stop, cost_center, userid, supervisor_contact_uuid, exclude_registration,
                      delete_time, inrdid, tenant, tenant_group,(case when (middle_name= '' or middle_name is null ) and (first_name= '' or first_name is null )
then (last_name)
when (middle_name= '' or middle_name is null )
then
{fn concat(first_name,{fn concat(   ' '  ,   last_name)})} else
{fn concat(first_name  ,{fn concat(  ' '  ,{fn concat(  middle_name ,{fn concat( ' ' ,  last_name)})})})}  end) as contact_name,

(case
when (middle_name= '' or middle_name is null )
then
{fn concat(first_name,{fn concat(   ' '  ,   last_name)})} else
{fn concat(first_name  ,{fn concat(  ' '  ,{fn concat(  middle_name ,{fn concat( ' ' ,  last_name)})})})}  end) as primary_contact

FROM         dbo.ca_contact
GO