member_canonical_view

Materialized: false

Definition

 SELECT mc.member_canonical_id,
    ms.member_seen_id,
    ms.name_last,
    ms.name_first,
    ms.name_middle,
    ms.name_prefix,
    ms.name_suffix,
    ms.date_of_birth,
    ms.gender_or_sex,
    mc.ssn,
    ms.member_identification_code,
    ms.family_identification_code,
    ms.person_code,
    ap.phone_number AS alternate_phone_number,
    bp.phone_number AS beeper_number,
    cp.phone_number AS cell_phone_number,
    f.fax_number,
    hp.phone_number AS home_phone_number,
    p.phone_number,
    wp.phone_number AS work_phone_number,
    ms.phone_extension
   FROM claims.member_canonical mc
     JOIN claims.member_seen ms ON ms.member_seen_id = mc.canonical_member_seen_id
     JOIN claims.trading_partner tp USING (trading_partner_id)
     JOIN claims.solution s USING (solution_id)
     LEFT JOIN claims.address_seen a USING (address_id)
     LEFT JOIN claims.phone ap ON ap.phone_id = ms.alternate_phone_number_id
     LEFT JOIN claims.phone bp ON bp.phone_id = ms.beeper_number_id
     LEFT JOIN claims.phone cp ON cp.phone_id = ms.cell_phone_number_id
     LEFT JOIN claims.phone hp ON hp.phone_id = ms.home_phone_number_id
     LEFT JOIN claims.phone p ON p.phone_id = ms.phone_number_id
     LEFT JOIN claims.phone wp ON wp.phone_id = ms.work_phone_number_id
     LEFT JOIN claims.email e ON e.email_id = ms.email_address_id
     LEFT JOIN claims.fax f ON f.fax_id = ms.fax_number_id;

Column	Data Type	Indexed
member_canonical_id	bigint	No
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
date_of_birth	date	No
gender_or_sex	gender_or_sex_type	No
ssn	text	No
member_identification_code	text	No
family_identification_code	text	No
person_code	text	No
alternate_phone_number	text	No
beeper_number	text	No
cell_phone_number	text	No
fax_number	text	No
home_phone_number	text	No
phone_number	text	No
work_phone_number	text	No
phone_extension	text	No