Correct concept representing table and column for your CDM version

[don-torok]

Version 6.0 cost.cost_event_field_concept_id, observation.obs_event_field_concept_id and note.note_event_field_concept_id require the concept that represents the CDM Version/Table/column that the cost, observation or note refers to. This concept is specific to the CDM version. Query input is table.column, output is CDM version, concept_id for table.column and the text for table.column.

SELECT version_concept.concept_name, column_concept.concept_id, column_concept.concept_name
  FROM concept column_concept
  JOIN concept_relationship cr ON column_concept.concept_id = cr.concept_id_1 
      AND cr.invalid_reason IS NULL 
      AND cr.relationship_id = 'Contained in version'
  JOIN concept version_concept ON version_concept.concept_id = cr.concept_id_2 
WHERE column_concept.concept_class_id = 'Field' 
    AND column_concept.standard_concept = 'S' 
    AND column_concept.concept_name = 'drug_exposure.drug_exposure_id'
ORDER BY version_concept.concept_name;