Page History
Inpage_linkfix |
---|
Purpose
Composite description views enable access to appropriate individual descriptions or sets of descriptions in a specific language or dialect. There are two sets of composite description views. Views in the first of these sets are designed to facilitate selecting descriptions associated with one or more identified concepts. Views in the other set are designed to enable searching descriptions to find concepts.
Views that Facilitate Selecting Concept Descriptions
There are four views in this set for each snapshot and the For each snapshot view the SNOMED CT example database includes four views designed for selecting specific sets of descriptions for one or more specified concepts. The characteristics of each of these views are shown in . A general in and a general template for defining the SQL definitions of these views is shown in . To create each of the views named in the table, the placeholders for {typeId} and {acceptabilityId} need to be replaced with values in the Specific Settings column of the table.
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.
Name
| PurposeDescription | Specific Settings | |||
{typeId} | {acceptabilityId} | ||||
snap_fsn | Selecting This view selects the fully specified name of a concept (identified by conceptId) | = 900000000000003001 | = 900000000000548007 | ||
snap_pref | Selecting the This view selects the preferred synonym of a concept (identified by conceptId) | = 900000000000013009 | = 900000000000548007 | ||
snap_syn | Selecting other This view selects other acceptable synonyms of a concept (identified by conceptId) | = 900000000000013009 | = 900000000000549004 | ||
snap_synall | Selecting all This view selects all synonyms of a concept (identified by conceptId) | = 900000000000013009 | IN (900000000000548007, 900000000000549004)
|
.
|
Code Block | ||||
---|---|---|---|---|
| ||||
CREATE VIEW `snap_{name}` AS (SELECT `d`.* FROM `snap_description` `d` JOIN `snap_refset_Language` `rs` ON `d`.`id` = `rs`.`referencedComponentId` JOIN `config_settings` `cfg` ON `rs`.`refSetId` = `cfg`.`languageId` WHERE `d`.`active` = 1 AND `d`.`typeId` {typeId} AND `rs`.`active` = 1 AND `rs`.`acceptabilityId` {acceptabilityId} AND `cfg`.`id`=0); |
SQL Query |
Usage Example
The following query can be used to show all the active descriptions of a concept that are acceptable or preferred according to the language reference set referenced by the configuration file.
|
...
| ||||||
Result | ||||||
conceptId | FSN | id | term | |||
95570007 | FSN | 839752010 | Kidney stone (disorder) | |||
95570007 | Pref | 158296018 | Kidney stone | |||
95570007 | Syn | 158297010 | Renal stone | |||
95570007 | Syn | 158298017 | Nephrolith | |||
95570007 | Syn | 158299013 | Renal calculus | |||
95570007 | Syn | 512193015 | Calculus of kidney | |||
95570007 | Syn | 512194014 | Nephrolithiasis | |||
95570007 | Syn | 512195010 | Kidney calculus |
Views that Facilitate Searching for Concepts
For each snapshot view the SNOMED CT example database includes two views that are designed to facilitate searches for concepts associated with terms that match specified criteria. These views require both the description and the associated concept to be active in the chosen snapshot. This avoids the need to filter out inactive concepts from the search results
Footnote Macro |
---|
Requirements for searches that need to include inactive concepts can be run against the concept description selection views. |
. The specific There are two views in this set for each snapshot and the characteristics of these views are shown in . A general template for defining is shown in and a general template for the SQL definitions of these views is shown in . To create the each of the views named in the table, the placeholder for {typeId} needs to be replaced with values in the Specific Settings column of the table.
show a query that searches the snap_syn_search_active view using MySQL's boolean full text search. This search method requires all the words preceded by + (plus) to be included and excludes all words preceded by - (minus). The results are shown together with the fully specified name of the concept (looked up using the snap_fsn view). Although this is not a user-friendly way to specify a searches, the example SQL code illustrates the technical power of this search technique. Other search techniques can also be applied to the search views and additional options for enhancing searches are discussed in 4.9.4. Search Procedures.
Name
| Description | Specific Settings | ||
{typeId} | ||||
snap_syn_search_active | This view includes active preferred and acceptable synonyms of active concepts. It excludes fully specified names and also excludes all descriptions associated with concepts that are inactive in the specified snapshot. | = 900000000000013009 | ||
snap_term_search_active | This view includes active preferred and acceptable synonyms of active concepts. | IN (900000000000003001, 900000000000013009)
|
Code Block | ||||
---|---|---|---|---|
| ||||
CREATE VIEW `snap_syn_search_active` AS
(SELECT `d`.*,`rs`.`acceptabilityId` FROM `snap_description` `d`
JOIN `snap_refset_Language` `rs` ON `d`.`id` = `rs`.`referencedComponentId`
JOIN `snap_concept` `c` ON `c`.`id` = `d`.`conceptId`
JOIN `config_settings` `cfg` ON `rs`.`refSetId` = `cfg`.`languageId`
WHERE `d`.`active` = 1 AND `d`.`typeId` {typeId}
AND `rs`.`active` = 1
AND `c`.`active` = 1
AND `cfg`.`id`=0); |
Usage Example
SQL Query | |||||||||
| |||||||||
Result | |||||||||
conceptId | matching term | FSN | |||||||
54329005 | Acute anterior myocardial infarction | Acute myocardial infarction of anterior wall (disorder) | |||||||
54329005 | Acute myocardial infarction of anterior wall | Acute myocardial infarction of anterior wall (disorder) | |||||||
703164000 | Acute anterior ST segment elevation myocardial infarction | Acute ST segment elevation myocardial infarction of anterior wall (disorder) | |||||||
703164000 | Acute STEMI (ST elevation myocardial infarction) of anterior wall | Acute ST segment elevation myocardial infarction of anterior wall (disorder) | |||||||
703164000 | Acute ST segment elevation myocardial infarction of anterior wall | Acute ST segment elevation myocardial infarction of anterior wall (disorder) | |||||||
703252002 | Acute myocardial infarction of anterior wall involving right ventricle | Acute myocardial infarction of anterior wall involving right ventricle (disorder) | |||||||
703252002 | Acute myocardial infarction of anterior wall with right ventricular involvement | Acute myocardial infarction of anterior wall involving right ventricle (disorder) | |||||||
703165004 | Acute ST segment elevation myocardial infarction of anterior wall involving right ventricle | Acute ST segment elevation myocardial infarction of anterior wall involving right ventricle (disorder) | |||||||
703165004 | Acute anterior ST segment elevation myocardial infarction with right ventricular involvement | Acute ST segment elevation myocardial infarction of anterior wall involving right ventricle (disorder) | |||||||
703165004 | Acute STEMI (ST elevation myocardial infarction) of anterior wall with right ventricular involvement | Acute ST segment elevation myocardial infarction of anterior wall involving right ventricle (disorder) | |||||||
285981000119103 | Acute ST segment elevation myocardial infarction involving left anterior descending coronary artery | Acute ST segment elevation myocardial infarction involving left anterior descending coronary artery (disorder) |
...
Display Footnotes Macro |
---|