select * from (SELECT element_global.element_global_id as Id,cast('Global' as VARCHAR(6)) as GeoLevel,to_char(element_global.element_global_id) as Full_Id,cast(element_global.elcode_bcd as VARCHAR(10)) as ElcodeBcd,case upper(d_classification_framework.classification_framework_type) when 'C' then element_global.concatenated_cd else higher_class_unit.concatenated_cd end as Classification_Code,gname.scientific_name as Scientific_Name,element_global.g_primary_common_name as Common_Name,element_global.g_rank as Rank,'' as Nation,'' as Subnation,d_classification_status.classification_status_desc as Classification_Status,d_classification_level.classification_level_name as ClassificationLevel,element_global.inactive_ind as Inactive, working_list_element_data.display_order as DisplayOrder FROM higher_class_unit,d_classification_level,scientific_name gname,d_classification_framework,element_global,working_list_element_data,d_classification_status WHERE working_list_element_data.working_list_id = :id and element_global.higher_class_unit_id=higher_class_unit.higher_class_unit_id(+) and element_global.gname_id=gname.scientific_name_id and element_global.d_classification_status_id=d_classification_status.d_classification_status_id and gname.d_classification_level_id=d_classification_level.d_classification_level_id and d_classification_level.d_class_framework_id=d_classification_framework.d_classification_framework_id and working_list_element_data.data_id = element_global.element_global_id union SELECT element_national.element_national_id as Id,cast('National' as VARCHAR(8)) as GeoLevel,element_global.element_global_id || '_' || element_national.element_national_id as Full_Id,cast(element_global.elcode_bcd as VARCHAR(10)) as ElcodeBcd,case upper(d_classification_framework.classification_framework_type) when 'C' then element_global.concatenated_cd else higher_class_unit.concatenated_cd end as Classification_Code,nname.scientific_name as Scientific_Name,element_national.n_primary_common_name as Common_Name,element_national.n_rank as Rank,nation.iso_nation_cd as Nation,'' as Subnation,d_classification_status.classification_status_desc as Classification_Status,d_classification_level.classification_level_name as ClassificationLevel,element_global.inactive_ind as Inactive, working_list_element_data.display_order as DisplayOrder FROM higher_class_unit,d_classification_level,d_classification_framework,scientific_name nname,element_global,working_list_element_data,d_classification_status,nation,element_national WHERE working_list_element_data.working_list_id = :id and element_national.nname_id=nname.scientific_name_id and element_global.higher_class_unit_id=higher_class_unit.higher_class_unit_id(+) and element_global.d_classification_status_id=d_classification_status.d_classification_status_id and d_classification_level.d_class_framework_id=d_classification_framework.d_classification_framework_id and element_national.nation_id=nation.nation_id and working_list_element_data.data_id = element_national.element_national_id and element_national.element_global_id=element_global.element_global_id and nname.d_classification_level_id=d_classification_level.d_classification_level_id union SELECT element_subnational.element_subnational_id as Id,cast('Subnational' as VARCHAR(11)) as GeoLevel,element_global.element_global_id || '_' || element_national.element_national_id || '_' || element_subnational.element_subnational_id as Full_Id,cast(element_global.elcode_bcd as VARCHAR(10)) as ElcodeBcd,case upper(d_classification_framework.classification_framework_type) when 'C' then element_global.concatenated_cd else higher_class_unit.concatenated_cd end as Classification_Code,sname.scientific_name as Scientific_Name,element_subnational.s_primary_common_name as Common_Name,element_subnational.s_rank as Rank,nation.iso_nation_cd as Nation,subnation.subnation_code as Subnation,d_classification_status.classification_status_desc as Classification_Status,d_classification_level.classification_level_name as ClassificationLevel,element_global.inactive_ind as Inactive, working_list_element_data.display_order as DisplayOrder FROM higher_class_unit,d_classification_level,d_classification_framework,element_subnational,scientific_name sname,element_global,subnation,working_list_element_data,d_classification_status,nation,element_national WHERE working_list_element_data.working_list_id = :id and element_global.higher_class_unit_id=higher_class_unit.higher_class_unit_id(+) and working_list_element_data.data_id = element_subnational.element_subnational_id and element_global.d_classification_status_id=d_classification_status.d_classification_status_id and element_subnational.sname_id=sname.scientific_name_id and element_subnational.subnation_id=subnation.subnation_id and element_subnational.element_national_id=element_national.element_national_id and d_classification_level.d_class_framework_id=d_classification_framework.d_classification_framework_id and sname.d_classification_level_id=d_classification_level.d_classification_level_id and element_national.nation_id=nation.nation_id and element_national.element_global_id=element_global.element_global_id) ORDER BY DisplayOrder, Id