Search



Page tree

  

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.

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.

Name1

Description

delta_inactive_concepts

This view selects details of concepts that are inactive in the chosen delta or snapshot view. In addition to the concept id the fully specified name of the inactive concept is selected. The output of this view also includes the reason for activation and any historical associations between this inactive concept and an active concept. The reason for inactivation is shown as the preferred synonym for the concept representing the reason for inactivation in the concept inactivation reference set. The historical association is represented by the preferred synonym of the association reference set(s) and the fully specified name of the associated target concept. Where a concept has multiple active associations each of these reported as a separate row (the inactive concept and inactivation reason data is duplicated on each of these rows).

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`;

SQL Query
SELECT * FROM delta_inactive_concepts;
Result (example rows only)
ideffectiveTimeactivedefinitionStatusIdFSNreasonassoc_typeref_conceptIdref_concept_FSN
1192004201907310900000000000074008Familial amyloid neuropathy, Finnish type (disorder)OutdatedREPLACED BY3757892013Hereditary gelsolin amyloidosis (disorder)
1230003201907310900000000000074008No diagnosis on Axis I (finding)OutdatedREPLACED BY677781011Psychological finding (finding)
1427008201907310900000000000074008Intraspinal abscess (disorder)DuplicateSAME AS743297013Spinal cord abscess (disorder)
2461007201907310900000000000074008Tennis elbow test (procedure)AmbiguousPOSSIBLY EQUIVALENT TO3777085015Lateral epicondylitis test (procedure)
2900003201907310900000000000074008Hyperplasia of renal artery (disorder)AmbiguousPOSSIBLY EQUIVALENT TO3760067011Fibromuscular dysplasia of wall of renal artery (disorder)
3105002201907310900000000000074008Intron (finding)OutdatedREPLACED BY697643016Finding related to molecular sequence data (finding)
3221003201907310900000000000074008Ringer's solution (product)

Nonconformance to editorial policy component




3734003201907310900000000000074008Split thickness skin graft (procedure)AmbiguousPOSSIBLY EQUIVALENT TO3758568011Split thickness graft of skin to skin (procedure)
4101004201907310900000000000074008Revision of spinal pleurothecal shunt (procedure)AmbiguousPOSSIBLY EQUIVALENT TO618942015Revision of spinal subarachnoid shunt (procedure)
4101004201907310900000000000074008Revision of spinal pleurothecal shunt (procedure)AmbiguousPOSSIBLY EQUIVALENT TO618681017Revision of subdural-pleural shunt (procedure)
4131005201907310900000000000074008Implantation into pelvic region (procedure)AmbiguousPOSSIBLY EQUIVALENT TO2968044014Procedure on pelvic region of trunk (procedure)
4131005201907310900000000000074008Implantation into pelvic region (procedure)AmbiguousPOSSIBLY EQUIVALENT TO3756616019Implantation procedure (procedure)
4518006201907310900000000000074008Buthenal (substance)AmbiguousPOSSIBLY EQUIVALENT TO796984014Crotonaldehyde (substance)
4919007201907310900000000000074008Congenital protrusion (morphologic abnormality)DuplicateSAME AS642112018Protrusion (morphologic abnormality)
5034009201907310900000000000074008Graft to hair-bearing skin (procedure)DuplicateSAME AS3757739014Hair bearing graft of skin to skin (procedure)

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.

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.

Name2

Description

delta_inactive_descriptions

This view selects details of all descriptions that are inactive in the chosen delta or snapshot view. In addition to selecting the description data it also includes the active fully specified name of the related concept and the reason for activation. The reason for inactivation is shown as the preferred synonym for the concept representing the reason for inactivation in the description inactivation reference set.

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`;

SQL Query
SELECT * FROM delta_inactive_descriptions;
Result (example rows only)
ideffectiveTimeactiveconceptidtermconcept_fsnconcept_activereason
141320192019073107938006D-Arabinitol dehydrogenaseD-arabinitol 4-dehydrogenase (substance)1

Nonconformance to editorial policy component

161010182019073109156001Embryo stage 1Structure of embryo at stage 1 (body structure)1

Nonconformance to editorial policy component

168370142019073109631008Rheumatoid spondylitisAnkylosing spondylitis (disorder)1

Not semantically equivalent component

172340172019073109871000D-Amino-acid acetyltransferaseD-amino-acid N-acetyltransferase (substance)1

Nonconformance to editorial policy component

1752501420190731010043003D-Alanine-alanyl-poly(glycerolphosphate) ligaseD-alanine-alanyl-poly(glycerolphosphate) ligase (substance)1

Nonconformance to editorial policy component

1752601020190731010043003D-Alanyl-alanyl-poly(glycerolphosphate)synthetaseD-alanine-alanyl-poly(glycerolphosphate) ligase (substance)1

Nonconformance to editorial policy component

1752701820190731010043003D-Alanine:membrane-acceptor ligaseD-alanine-alanyl-poly(glycerolphosphate) ligase (substance)1

Nonconformance to editorial policy component

1761501020190731010093004Anisakiasis due to Anisakis simplexAnisakiasis caused by larva of Anisakis simplex (disorder)1Erroneous
2022001520190731011702002bis-(p-Chlorophenyl) ethanolBis-(p-chlorophenyl) ethanol (substance)1

Nonconformance to editorial policy component

2046901520190731011860003NannizziaGenus Arthroderma (organism)1

Not semantically equivalent component


Feedback
  • No labels