create or replace view ASTERS_EO_VW (egt_id, ent_id, est_id, gname, sname, grank, srank, eo_id, eo_rank, eo_data)
as
SELECT egt.element_global_id,
        ent.element_national_id,
        est.element_subnational_id,
        gn.scientific_name,
        sn.scientific_name,
        egt.g_rank,
        est.s_rank,
        eo.eo_id,
        dr.basic_eo_rank_cd,
        eo.eo_data
FROM    element_global egt, element_national ent, element_subnational est, scientific_name gn, scientific_name sn,
        eo, d_basic_eo_rank dr, d_name_category name_cat
WHERE   egt.gname_id = gn.scientific_name_id and
        gn.d_name_category_id = name_cat.d_name_category_id and
        name_cat.name_type_cd = 'P' and
        gn.SCIENTIFIC_NAME like 'Aster%' and
        egt.element_global_id = ent.element_global_id and
        ent.element_national_id = est.element_national_id and
        est.sname_id = sn.scientific_name_id and
        est.element_subnational_id = eo.element_subnational_id(+) and
        eo.d_basic_eo_rank_id = dr.d_basic_eo_rank_id(+);