ap2i/sql/schema-extensions/003-view_organization_identification.sql

CREATE OR REPLACE VIEW view_organization_identification AS
    SELECT o.id, o.name, REGEXP_REPLACE(LOWER(o.name), '[^a-z0-9]+', '+') AS normalizedName,
       CONCAT(a.streetAddress, ' ', a.streetAddressSecond, ' ', a.streetAddressThird) AS address,
       a.addressCity AS city, a.postalCode, c.email, c.telphone
    FROM opentalent.Organization o
        INNER JOIN opentalent.OrganizationAddressPostal oa ON oa.organization_id = o.id
        INNER JOIN opentalent.AddressPostal a ON a.id = oa.addressPostal_id
        INNER JOIN opentalent.organization_contactpoint oc ON oc.organization_id = o.id
        INNER JOIN opentalent.ContactPoint c ON oc.contactPoint_id = c.id
    WHERE oa.type='ADDRESS_HEAD_OFFICE' AND c.contactType='PRINCIPAL';