Inactive Concept Views

For each delta and snapshot view the SNOMED CT example database includes a view of inactive concepts. The characteristics of each of these views are shown in  Table 4.8.5-1 and a general template for the SQL definitions of these views is shown in  Template 4.8.5-1. To create each of the views named in the table, the placeholders for {myph} need to be replaced with values in the Specific Settings column of the table. 

Example 4.8.5-1 demonstrates the use of these views to show all the active descriptions of a specified concept that are acceptable or preferred according to the language reference set referenced by the configuration file.

Table 4.8.5-1: Composite Views of Inactive Concepts with Related Concept Inactivation and Historical Association Refset Data

Template 4.8.5-1: SQL Definition of the Inactive Concepts View

CREATE VIEW delta_inactive_concepts AS 
select `c`.`id`, `c`.`effectiveTime`, `c`.`active`, `c`.`definitionStatusId`, `cf`.`term` 'FSN',
	`vp`.`term` 'reason', `arp`.`term` 'assoc_type', `atf`.`id` 'ref_conceptId', `atf`.`term` 'ref_concept_FSN' 
from `delta_concept` `c`
left join `snap_fsn` `cf` ON `cf`.`conceptid`=`c`.`id`
left outer join `snap_refset_attributevalue` `v` on `v`.`referencedComponentId`=`c`.`id`
	 and `v`.`refsetId`=900000000000489007 and `v`.`active`=1
left outer join `snap_pref` `vp` on `vp`.`conceptid`=`v`.`valueid` 
left outer join `snap_refset_association` `a` on `a`.`referencedComponentId`=`c`.`id` and `a`.`refsetId` IN 
	(900000000000528000, 900000000000523009, 900000000000527005, 900000000000526001,
	 900000000000525002, 900000000000531004, 900000000000524003, 900000000000530003) and `a`.`active`=1
left outer join `snap_pref` `arp` on `arp`.`conceptid`=`a`.`refsetId`
left outer join `snap_fsn` `atf` on `atf`.`conceptid`=`a`.`targetComponentId`
where `c`.`active`=0
order by `c`.`id`;

Example 4.8.5-1: Selecting Inactive Concepts with Related Concepts Inactivation and Historical Association Refset Data

SELECT * FROM delta_inactive_concepts;

Inactive Descriptions

For each delta and snapshot view the SNOMED CT example database includes a view of inactive descriptions. The characteristics of each of these views are shown in  Table 4.8.5-2 and a general template for the SQL definitions of these views is shown in  Template 4.8.5-2. To create each of the views named in the table, the placeholders for {myph} need to be replaced with values in the Specific Settings column of the table. 

Example 4.8.5-2 demonstrates the use of these views to show all the active descriptions of a specified concept that are acceptable or preferred according to the language reference set referenced by the configuration file.

Table 4.8.5-2: Composite Views of Inactive Descriptions with Related Description Inactivation and Historical Association Refset Data

Template 4.8.5-2: SQL Definition of the Inactive Descriptions View

CREATE VIEW delta_inactive_descriptions AS 
select `d`.`id`, `d`.`effectiveTime`, `d`.`active`, `d`.`conceptid`, `d`.`term` 'term',
	`df`.`term` 'concept_fsn', `c`.`active` 'concept_active' ,`vp`.`term` 'reason' 
from `delta_description` `d`
left outer join `snap_fsn` `df` ON `df`.`conceptid`=`d`.`conceptid`
join `snap_concept` `c` ON `c`.`id`=`d`.`conceptid`
left outer join `snap_refset_attributevalue` `v` on `v`.`referencedComponentId`=`d`.`id` 
	and `v`.`refsetId`=900000000000490003 and `v`.`active`=1
left outer join `snap_pref` `vp` on `vp`.`conceptid`=`v`.`valueid` 
where `d`.`active`=0
order by `d`.`id`;

Example 4.8.5-2: Selecting Inactive Descriptions with Related Description Inactivation Refset Data

SELECT * FROM delta_inactive_descriptions;