member_seen_view

Materialized: false

Definition

 SELECT ms.member_seen_id,
    ms.name_last,
    ms.name_first,
    ms.name_middle,
    ms.name_prefix,
    ms.name_suffix,
    ms.primary_id_qualifier,
    ms.date_of_birth,
    ms.gender_or_sex,
    ms.member_ssn,
    ms.member_identification_code,
    ms.family_identification_code,
    ms.person_code,
    ms.address_id,
    ms.alternate_phone_number_id,
    ms.beeper_number_id,
    ms.cell_phone_number_id,
    ms.email_address_id,
    ms.fax_number_id,
    ms.home_phone_number_id,
    ms.phone_number_id,
    ms.work_phone_number_id,
    ms.phone_extension,
    ms.member_canonical_id_chosen,
    ms.member_canonical_id_chosen_at,
    ms.member_canonical_id_chosen_by,
    ms.member_match_status,
    ms.trading_partner_id,
    a.canonical_address_id,
    a.canonical_primary_address_only_id,
    a.is_invalid_address,
    a.address1,
    a.address2,
    a.city,
    a.state_or_province,
    a.postal_code,
    a.country_code,
    a.country_subdivision,
    home.phone_number AS home_phone_number,
    cell.phone_number AS cell_phone_number,
    work.phone_number AS work_phone_number,
    ph.phone_number,
    alt.phone_number AS alternate_phone_number,
    fax.fax_number,
    email.email_address
   FROM claims.member_seen ms
     LEFT JOIN claims.address_seen a ON a.address_id = ms.address_id
     LEFT JOIN claims.phone home ON home.phone_id = ms.home_phone_number_id
     LEFT JOIN claims.phone cell ON cell.phone_id = ms.cell_phone_number_id
     LEFT JOIN claims.phone work ON work.phone_id = ms.work_phone_number_id
     LEFT JOIN claims.phone ph ON ph.phone_id = ms.phone_number_id
     LEFT JOIN claims.phone alt ON alt.phone_id = ms.alternate_phone_number_id
     LEFT JOIN claims.fax ON fax.fax_id = ms.fax_number_id
     LEFT JOIN claims.email email ON email.email_id = ms.email_address_id;

Column	Data Type	Indexed
member_seen_id	bigint	No
name_last	text	No
name_first	text	No
name_middle	text	No
name_prefix	text	No
name_suffix	text	No
primary_id_qualifier	identification_type	No
date_of_birth	date	No
gender_or_sex	gender_or_sex_type	No
member_ssn	text	No
member_identification_code	text	No
family_identification_code	text	No
person_code	text	No
address_id	bigint	No
alternate_phone_number_id	bigint	No
beeper_number_id	bigint	No
cell_phone_number_id	bigint	No
email_address_id	bigint	No
fax_number_id	bigint	No
home_phone_number_id	bigint	No
phone_number_id	bigint	No
work_phone_number_id	bigint	No
phone_extension	text	No
member_canonical_id_chosen	bigint	No
member_canonical_id_chosen_at	timestamptz	No
member_canonical_id_chosen_by	bigint	No
member_match_status	match_status_type	No
trading_partner_id	bigint	No
canonical_address_id	bigint	No
canonical_primary_address_only_id	bigint	No
is_invalid_address	boolean	No
address1	text	No
address2	text	No
city	text	No
state_or_province	text	No
postal_code	text	No
country_code	text	No
country_subdivision	text	No
home_phone_number	text	No
cell_phone_number	text	No
work_phone_number	text	No
phone_number	text	No
alternate_phone_number	text	No
fax_number	text	No
email_address	text	No