-- These two view definitions are to transform the hierarchical tables
--   TOPIC and LOCATION into views called "HIERARCHY_TOPIC" and
--   HIERARCHY_LOCATION". This is just for testing - normally TOPIC 
--   and LOCATION are transformed into view HIERARCHY by pressing the 
--   form's button (or by starting the form).
--                                                      ft, 1999.04.30
create or replace view HIERARCHY as
  select TOPIC_ID                         hierarchy_id,
         TOPIC_ACRONYM                    hierarchy_acronym,
         TOPIC_NAME                       hierarchy_name,
         TOPIC_DESCR                      hierarchy_descr,
         TOPIC_POINTER                    hierarchy_pointer,
         TOPIC_LEVEL                      hierarchy_level
    from CERA.TOPIC;
grant SELECT on HIERARCHY to public;
--
create or replace view HIERARCHY_CONNECT as
select topic_id,hierarchy_id from PARAMETER;
--
create or replace view HIERARCHY_TOPIC as
  select TOPIC_ID                         hierarchy_id,
         TOPIC_ACRONYM                    hierarchy_acronym,
         TOPIC_NAME                       hierarchy_name,
         TOPIC_DESCR                      hierarchy_descr,
         TOPIC_POINTER                    hierarchy_pointer,
         TOPIC_LEVEL                      hierarchy_level
    from CERA.TOPIC;
grant SELECT on HIERARCHY_TOPIC to public;
--
create or replace view HIERARCHY_LOCATION_CONNECT as
select location_id,hierarchy_id from LOCATION_CONNECT;
--
create or replace view HIERARCHY_LOCATION as
  select LOCATION_ID                      hierarchy_id,
         substr(LOCATION_NAME,1,31)       hierarchy_acronym,
         LOCATION_NAME                    hierarchy_name,
              'northernmost: ' || max_lat ||
           ';  southernmost: ' || min_lat ||
           ';  easternmost: '  || max_lon ||
           ';  westernmost: '  || min_lon hierarchy_descr,
         LOCATION_POINTER                 hierarchy_pointer,
         LOCATION_LEVEL                   hierarchy_level
    from CERA.LOCATION;
grant SELECT on HIERARCHY_LOCATION to public;
--
create or replace view HIERARCHY_TOPIC_CONNECT as
select topic_id,hierarchy_id from PARAMETER;
