...
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 and in
| Caption reference |
|---|
| CapRefId | description-selection |
|---|
| CapRefType | Table |
|---|
|
and a general template for the SQL definitions of these views is shown in
| Caption reference |
|---|
| CapRefId | description-selection |
|---|
| CapRefType | Template |
|---|
|
. 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.
| Caption reference |
|---|
| CapRefId | description-selection |
|---|
| 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 | description-selection |
|---|
| CapType | Table |
|---|
|
Composite Description Views for Selecting Concept Descriptions |
Name | Footnote Macro |
|---|
The prefix snap is replaced by snap1 or snap2 for retrospective views. |
| Description | Specific Settings |
| {typeId} | { acceptabilityId } |
snap_fsn | This view selects the fully specified name of a concept (identified by conceptId) | = 900000000000003001 | = 900000000000548007 |
snap_pref | This view selects the preferred synonym of a concept (identified by conceptId) | = 900000000000013009 | = 900000000000548007 |
snap_syn | This view selects other acceptable synonyms of a concept (identified by conceptId) | = 900000000000013009 | = 900000000000549004 |
snap_synall | This view selects all synonyms of a concept (identified by conceptId) | = 900000000000013009 | IN (900000000000548007, 900000000000549004) | Footnote Macro |
|---|
An alternative way to represent snap_synall is to remove the acceptability condition. The link to the language refset and the test for the `rs`.`active` condition must retained to ensure only descriptions in the relevant language refset are returned. |
|
| Caption label |
|---|
| CapId | description-selection |
|---|
| CapType | Template |
|---|
|
Description Selection Composite View Template |
| Code Block |
|---|
| language | sql |
|---|
| theme | Confluence |
|---|
|
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); |
| Caption label |
|---|
| CapId | description-selection |
|---|
| CapType | Example |
|---|
|
Selecting Description Data for a Concept |
| SQL Query |
| Code Block |
|---|
| language | sql |
|---|
| theme | Confluence |
|---|
| Select conceptId,'FSN',id,term from snap_fsn where conceptId=95570007
UNION
Select conceptId,'Pref',id,term from snap_pref where conceptId=95570007
UNION
Select conceptId,'Syn',id,term from snap_syn where conceptId=95570007; |
|
| 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
...
. The specific characteristics of these views is shown in and in
| Caption reference |
|---|
| CapRefId | description-search |
|---|
| CapRefType | Table |
|---|
|
and a general template for the SQL definitions of these views is shown in
| Caption reference |
|---|
| CapRefId | description-search |
|---|
| CapRefType | Template |
|---|
|
. 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.
| Caption reference |
|---|
| CapRefId | description-search |
|---|
| CapRefType | Example |
|---|
|
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.
| Caption label |
|---|
| CapId | description-search |
|---|
| CapType | Table |
|---|
|
Composite Description Views that Facilitate Searching for Concepts |
Name | Footnote Macro |
|---|
The prefix snap is replaced by snap1 or snap2 for retrospective views. |
| 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) | Footnote Macro |
|---|
Alternatively remove the typeId condition to permit all types to be searched. |
|
| Caption label |
|---|
| CapId | description-search |
|---|
| CapType | Template |
|---|
|
Description Search Composite View Template |
| Code Block |
|---|
| language | sql |
|---|
| theme | Confluence |
|---|
|
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
| Caption label |
|---|
| CapId | description-search |
|---|
| CapType | Example |
|---|
|
Searching Terms to Find Concepts |
| SQL Query |
| Code Block |
|---|
| language | sql |
|---|
| theme | Confluence |
|---|
| SELECT `s`.`conceptId`,`s`.`term` 'matching term',`f`.`term` `FSN` FROM `snap_syn_search_active` `s`
JOIN `snap_fsn` `f` ON `f`.`conceptId`=`s`.`conceptId`
WHERE MATCH (`s`.`term`)
AGAINST ('+acute +anterior +myocardial +infarction -ecg -old -ekg' IN BOOLEAN MODE) ORDER BY length(`f`.`term`),length(`s`.`term`); |
|
| 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) |
...