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 characteristics of each of these views are shown in .
A general template for defining 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.
Name1 | Purpose | Specific Settings | |
{typeId} | {acceptabilityId} | ||
snap_fsn | Selecting the fully specified name of a concept | = 900000000000003001 | = 900000000000548007 |
snap_pref | Selecting the preferred synonym of a concept | = 900000000000013009 | = 900000000000548007 |
snap_syn | Selecting other acceptable synonyms of a concept | = 900000000000013009 | = 900000000000549004 |
snap_synall | Selecting all synonyms of a concept | = 900000000000013009 | IN (900000000000548007, 900000000000549004)2 |
.
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);
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.
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;
Views that Facilitate Searching for Concepts
There are two views in this set for each snapshot and the characteristics of these views are shown in .
A general template for defining 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.
Name1 | 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)3 |
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);
Feedback