omoponfhir_v5.3_f_observation_ddl.txt

create view f_observation_view
            (observation_id, person_id, observation_concept_id, observation_date, 
             observation_datetime, observation_time,
             observation_type_concept_id, observation_operator_concept_id,
             value_as_number,
             value_as_string,
             value_as_concept_id,
             qualifier_concept_id,
             unit_concept_id,
             range_low,
             range_high,
             provider_id,
             visit_occurrence_id,
             visit_detail_id,
             observation_source_value,
             observation_source_concept_id,
             unit_source_value,
             qualifier_source_value,
             value_source_value)
as

SELECT measurement.measurement_id                AS observation_id,
       measurement.person_id,
       measurement.measurement_concept_id        AS observation_concept_id,
       measurement.measurement_date              AS observation_date,
       measurement.measurement_datetime          AS observation_datetime,
       measurement.measurement_time              AS observation_time,
       measurement.measurement_type_concept_id   AS observation_type_concept_id,
       measurement.operator_concept_id           AS observation_operator_concept_id,
       measurement.value_as_number,
       NULL                                      AS value_as_string,
       measurement.value_as_concept_id,
       NULL                                      AS qualifier_concept_id,
       measurement.unit_concept_id,
       measurement.range_low,
       measurement.range_high,
       measurement.provider_id,
       measurement.visit_occurrence_id,
       measurement.visit_detail_id,
       measurement.measurement_source_value      AS observation_source_value,
       measurement.measurement_source_concept_id AS observation_source_concept_id,
       measurement.unit_source_value,
       NULL                                      AS qualifier_source_value,
       measurement.value_source_value
       
FROM measurement
UNION ALL
SELECT (-observation.observation_id)             AS observation_id,
       observation.person_id,
       observation.observation_concept_id,
       observation.observation_date,
       observation.observation_datetime,
       NULL                                      AS observation_time,
       observation.observation_type_concept_id,
       NULL                                      AS observation_operator_concept_id,
       observation.value_as_number,
       observation.value_as_string,
       observation.value_as_concept_id,
       observation.qualifier_concept_id,
       observation.unit_concept_id,
       NULL                                      AS range_low,
       NULL                                      AS range_high,
       observation.provider_id,
       observation.visit_occurrence_id,
       observation.visit_detail_id,
       observation.observation_source_value,
       observation.observation_source_concept_id,
       observation.unit_source_value,
       observation.qualifier_source_value,
       NULL					 AS value_source_value
FROM observation
;