Page Comparison

select
    pa.lexicon_id as legacy_id,
    pa.date_entered as attribute_date,
    pa.agent_id as recorded_by_id,
    rb.name as recorded_by_name,
    pa.plant_height as height,
    pa.plant_width as width,
    pa.plant_dbh as dbh,
    pa.plant_habit as habitat,
    pa.climate_rating as climate_rating,
    ca.agent_id as conservation_agent_id,
    ca.name as conservation_agent_name,
    cc.conserv_cat as convservation_code,
    case
     when ca.name = 'California Native Plant Society' then 'CNPS'
     when ca.name = 'not applicable' then ''
     when ca.name = 'International Union for Conservation of Nature and Natural Resources - 1997' then 'IUCN-1997'
    --   when ca.name = 'Convention on International Trade in Endangered Species' then 'CNPS'
     when ca.name = 'Convention on International Trade in Endangered Species' then 'CITES'
     when ca.name = 'Rhododendron Species Foundation' then 'RSF'
    --   when ca.name = 'International Union for Conservation of Nature and Natural Resources - 2006' then 'CNPS'
     when ca.name = 'International Union for Conservation of Nature and Natural Resources - 2006' then 'IUCN-2006'
     when ca.name = 'South African National Biodiversity Institute' then 'SANBI'
     when ca.name = 'UNEP World Conservation Monitoring Center - CITES' then 'UNEP-CITES'
     when ca.name = 'New York Botanical Garden' then 'NYBG'
     when ca.name = 'US Fish & Wildlife Service' then 'USFWS'
     when ca.name = 'California Department of Fish & Game' then 'CADFG'
     when substring(cc.comp_key, 1, 5) = '60801' or ca.name = 'IUCN/SSC Global Tree Specialist Group, Red List of Rhododendrons - 2011' then 'IUCN-Rhododendrons-2011'
     when substring(cc.comp_key, 1, 5) = '60804' or ca.name = 'International Union for Conservation of Nature and Natural Resources - 2011.1' then 'IUCN-2011.1'
     when substring(cc.comp_key, 1, 5) = '60849' or ca.name = 'International Union for Conservation of Nature and Natural Resources - 2011.2' then 'IUCN-2011.2'
     when substring(cc.comp_key, 1, 5) = '60905' or ca.name = 'International Union for Conservation of Nature and Natural Resources - 2012.1' then 'IUCN-2012.1'
     when substring(cc.comp_key, 1, 5) = '60917' or ca.name = 'International Union for Conservation of Nature and Natural Resources - 2012.2' then 'IUCN-2012.2'
     when ca.name = 'Non-conservation publication' then 'Non-conservation pub'
     when cc.conserv_id in (38, 39, 41, 42, 47) then  ''
     end as conservCode_append,
    pa.frost_sensitive as frost_sensitive,
    pa.medicinal_flag as medicinal_use,
    pa.economic_flag as economic_use
from
    plant_attr pa,
    agent_name rb,
    agent_name ca,
    conserv_code cc,
    agent_name_role anr
where pa.agent_id = rb.agent_id
and rb.name_type = 'pref'
and pa.conserv_id = cc.conserv_id
and cc.comp_key *= anr.comp_key
and cc.role_type *= anr.role_type
and cc.role_type = 'conserv'
and anr.agent_id *= ca.agent_id
and anr.agent_name_id *= ca.agent_name_id
and anr.name_type *= ca.name_type
and ca.name_type = 'pref'