...
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 and in
| Caption reference |
|---|
| CapRefId | inactive-concept |
|---|
| CapRefType | Table |
|---|
|
and a general template for the SQL definitions of these views is shown in
| Caption reference |
|---|
| CapRefId | inactive-concept |
|---|
| CapRefType | Template |
|---|
|
. 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.
| Caption reference |
|---|
| CapRefId | inactive-concept |
|---|
| CapRefType | Example |
|---|
|
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.
| Caption label |
|---|
| CapId | inactive-concept |
|---|
| CapType | Table |
|---|
|
Composite Views of Inactive Concepts with Related Concept Inactivation and Historical Association Refset Data |
Name | Footnote Macro |
|---|
The prefix delta can be replaced by delta1 or delta2 for earlier delta views. The prefix delta can also be replaced by snap, snap1 or snap2 to view all concepts that were inactive in a particular snapshot. Note that snapshot versions of this view will return a very large number or rows as it includes every inactive description in the snapshot whereas the delta views only return those descriptions that were inactivated in that release. |
| 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). |
| Caption label |
|---|
| CapId | inactive-concept |
|---|
| CapType | Template |
|---|
|
SQL Definition of the Inactive Concepts View |
| Code Block |
|---|
| language | sql |
|---|
| theme | Confluence |
|---|
|
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`; |
| Caption label |
|---|
| CapId | inactive-concept |
|---|
| CapType | Example |
|---|
|
Selecting Inactive Concepts with Related Concepts Inactivation and Historical Association Refset Data |
| SQL Query |
| Code Block |
|---|
| language | sql |
|---|
| theme | Confluence |
|---|
| SELECT * FROM delta_inactive_concepts; |
|
| Result (example rows only) |
| id | effectiveTime | active | definitionStatusId | FSN | reason | assoc_type | ref_conceptId | ref_concept_FSN |
| 1192004 | 20190731 | 0 | 900000000000074008 | Familial amyloid neuropathy, Finnish type (disorder) | Outdated | REPLACED BY | 3757892013 | Hereditary gelsolin amyloidosis (disorder) |
| 1230003 | 20190731 | 0 | 900000000000074008 | No diagnosis on Axis I (finding) | Outdated | REPLACED BY | 677781011 | Psychological finding (finding) |
| 1427008 | 20190731 | 0 | 900000000000074008 | Intraspinal abscess (disorder) | Duplicate | SAME AS | 743297013 | Spinal cord abscess (disorder) |
| 2461007 | 20190731 | 0 | 900000000000074008 | Tennis elbow test (procedure) | Ambiguous | POSSIBLY EQUIVALENT TO | 3777085015 | Lateral epicondylitis test (procedure) |
| 2900003 | 20190731 | 0 | 900000000000074008 | Hyperplasia of renal artery (disorder) | Ambiguous | POSSIBLY EQUIVALENT TO | 3760067011 | Fibromuscular dysplasia of wall of renal artery (disorder) |
| 3105002 | 20190731 | 0 | 900000000000074008 | Intron (finding) | Outdated | REPLACED BY | 697643016 | Finding related to molecular sequence data (finding) |
| 3221003 | 20190731 | 0 | 900000000000074008 | Ringer's solution (product) | Nonconformance to editorial policy component |
|
|
|
| 3734003 | 20190731 | 0 | 900000000000074008 | Split thickness skin graft (procedure) | Ambiguous | POSSIBLY EQUIVALENT TO | 3758568011 | Split thickness graft of skin to skin (procedure) |
| 4101004 | 20190731 | 0 | 900000000000074008 | Revision of spinal pleurothecal shunt (procedure) | Ambiguous | POSSIBLY EQUIVALENT TO | 618942015 | Revision of spinal subarachnoid shunt (procedure) |
| 4101004 | 20190731 | 0 | 900000000000074008 | Revision of spinal pleurothecal shunt (procedure) | Ambiguous | POSSIBLY EQUIVALENT TO | 618681017 | Revision of subdural-pleural shunt (procedure) |
| 4131005 | 20190731 | 0 | 900000000000074008 | Implantation into pelvic region (procedure) | Ambiguous | POSSIBLY EQUIVALENT TO | 2968044014 | Procedure on pelvic region of trunk (procedure) |
| 4131005 | 20190731 | 0 | 900000000000074008 | Implantation into pelvic region (procedure) | Ambiguous | POSSIBLY EQUIVALENT TO | 3756616019 | Implantation procedure (procedure) |
| 4518006 | 20190731 | 0 | 900000000000074008 | Buthenal (substance) | Ambiguous | POSSIBLY EQUIVALENT TO | 796984014 | Crotonaldehyde (substance) |
| 4919007 | 20190731 | 0 | 900000000000074008 | Congenital protrusion (morphologic abnormality) | Duplicate | SAME AS | 642112018 | Protrusion (morphologic abnormality) |
| 5034009 | 20190731 | 0 | 900000000000074008 | Graft to hair-bearing skin (procedure) | Duplicate | SAME AS | 3757739014 | Hair 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 and in
| Caption reference |
|---|
| CapRefId | inactive-description |
|---|
| CapRefType | Table |
|---|
|
and a general template for the SQL definitions of these views is shown in
| Caption reference |
|---|
| CapRefId | inactive-description |
|---|
| CapRefType | Template |
|---|
|
. 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.
| Caption reference |
|---|
| CapRefId | inactive-description |
|---|
| CapRefType | Example |
|---|
|
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.
| Caption label |
|---|
| CapId | inactive-description |
|---|
| CapType | Table |
|---|
|
Composite Views of Inactive Descriptions with Related Description Inactivation and Historical Association Refset Data |
Name | Footnote Macro |
|---|
The prefix delta can be replaced by delta1 or delta2 for earlier delta views. The prefix delta can also be replaced by snap, snap1 or snap2 to view all concepts that were inactive in a particular snapshot. Note that snapshot versions of this view will return a very large number or rows as it includes every inactive concept in the snapshot whereas the delta views only return those concepts that were inactivated in that release. |
| 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. |
| Caption label |
|---|
| CapId | inactive-description |
|---|
| CapType | Template |
|---|
|
SQL Definition of the Inactive Descriptions View |
| Code Block |
|---|
| language | sql |
|---|
| theme | Confluence |
|---|
|
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`; |
| Caption label |
|---|
| CapId | inactive-description |
|---|
| CapType | Example |
|---|
|
Selecting Inactive Descriptions with Related Description Inactivation Refset Data |
| SQL Query |
| Code Block |
|---|
| language | sql |
|---|
| theme | Confluence |
|---|
| SELECT * FROM delta_inactive_descriptions; |
|
| Result (example rows only) |
| id | effectiveTime | active | conceptid | term | concept_fsn | concept_active | reason |
| 14132019 | 20190731 | 0 | 7938006 | D-Arabinitol dehydrogenase | D-arabinitol 4-dehydrogenase (substance) | 1 | Nonconformance to editorial policy component |
| 16101018 | 20190731 | 0 | 9156001 | Embryo stage 1 | Structure of embryo at stage 1 (body structure) | 1 | Nonconformance to editorial policy component |
| 16837014 | 20190731 | 0 | 9631008 | Rheumatoid spondylitis | Ankylosing spondylitis (disorder) | 1 | Not semantically equivalent component |
| 17234017 | 20190731 | 0 | 9871000 | D-Amino-acid acetyltransferase | D-amino-acid N-acetyltransferase (substance) | 1 | Nonconformance to editorial policy component |
| 17525014 | 20190731 | 0 | 10043003 | D-Alanine-alanyl-poly(glycerolphosphate) ligase | D-alanine-alanyl-poly(glycerolphosphate) ligase (substance) | 1 | Nonconformance to editorial policy component |
| 17526010 | 20190731 | 0 | 10043003 | D-Alanyl-alanyl-poly(glycerolphosphate)synthetase | D-alanine-alanyl-poly(glycerolphosphate) ligase (substance) | 1 | Nonconformance to editorial policy component |
| 17527018 | 20190731 | 0 | 10043003 | D-Alanine:membrane-acceptor ligase | D-alanine-alanyl-poly(glycerolphosphate) ligase (substance) | 1 | Nonconformance to editorial policy component |
| 17615010 | 20190731 | 0 | 10093004 | Anisakiasis due to Anisakis simplex | Anisakiasis caused by larva of Anisakis simplex (disorder) | 1 | Erroneous |
| 20220015 | 20190731 | 0 | 11702002 | bis-(p-Chlorophenyl) ethanol | Bis-(p-chlorophenyl) ethanol (substance) | 1 | Nonconformance to editorial policy component |
| 20469015 | 20190731 | 0 | 11860003 | Nannizzia | Genus Arthroderma (organism) | 1 | Not semantically equivalent component |