...

For each snapshot view the SNOMED CT example database includes two views that select the

Gloss

t	supertype parents

of a specified concept and two views that select the

Gloss

t	subtype children

of a specified concept. These views select the id and either the fully specified name or preferred synonym of each parent or child concept. The characteristics of each of these views are shown in

Caption reference

CapRefId	parent-child-view
CapRefType	Table

and a general template for the SQL definitions of these views is shown in

Caption reference

CapRefId	parent-child-view
CapRefType	Template

. To create each of the views named in the table, the placeholders for {termtype} need to be replaced with values in the Specific Settings column of the table.

...

Caption label

CapId	parent-child-view
CapType	Table

Composite Views of Supertype Parents and Subtype Children

Name

Footnote Macro
The prefix snap is replaced by snap1 or snap2 for retrospective views.

Description

Specific Settings

{termtype}

snap_rel_parent_fsn

Selects the id and fully specified

name

name of each supertype parent of a concept specified by conceptId.	fsn
snap_rel_parent_pref	Selects the id and preferred

synonym

synonym of each supertype parent of a concept specified by conceptId.	pref
snap_rel_child_fsn	Selects the id and fully specified name of each subtype child of a concept specified by conceptId.	fsn
snap_rel_child_pref	Selects the id and preferred synonym of each subtype child of a concept specified by conceptId.	pref

Caption label

CapId	parent-child-view
CapType	Template

SQL Templates for Composite Views of Supertype Parents and Subtype Children

...

Caption label

CapId	parent-child-view
CapType	Example

Selecting Supertype Parents and Subtype Children

SQL Query

Code Block

language	sql
theme	Confluence

Select "Concept", conceptid, term from snap_pref where conceptId=6025007
UNION
Select "Supertype Parent", id, term from snap_rel_parent_pref where conceptId=6025007
UNION
Select "Subtype Child", id, term from snap_rel_child_pref where conceptId=6025007;

Result

Concept

id

term

Concept

6025007

Laparoscopic appendectomy

Supertype Parent

51316009

Laparoscopic procedure

Supertype Parent

80146002

Appendectomy

Supertype Parent

264274002

Endoscopic operation

Supertype Parent

440588003

Endoscopic procedure on appendix

Subtype Child

174041007

Laparoscopic emergency appendectomy

Subtype Child

307581005

Laparoscopic interval appendectomy

Subtype Child

708876004

Robot assisted laparoscopic appendectomy

Transitive Closure Views of Supertype Ancestors and Subtype Descendants

For each snapshot view the SNOMED CT example database includes two views that select the

Gloss

t	supertype ancestors

of a specified concept and two views that select the

Gloss

t	subtype descendants

of a specified concept. These views select the id and either the fully specified name or preferred synonym of each ancestor or descendant concept. The characteristics of each of these views are shown in

Caption reference

CapRefId	transitive-closure-view
CapRefType	Table

and a general template for the SQL definitions of these views is shown in

Caption reference

CapRefId	transitive-closure-view
CapRefType	Template

. To create each of the views named in the table, the placeholders for {termtype} need to be replaced with values in the Specific Settings column of the table.

...

Caption label

CapId	transitive-closure-view
CapType	Table

Transitive Closure Views of Supertype Ancestors and Subtype Descendants

Name

Footnote Macro
Transitive closure and proximal primitive views are only available for the current snapshot

.

Description

Specific Settings

{

myph

termtype}
snap_tc_ancestor_fsn	Selects the id and fully specified name of each supertype ancestor of a concept specified by conceptId.	fsn
snap_tc_ancestor_pref	Selects the id and preferred synonym of each supertype ancestor of a concept specified by conceptId.	pref
snap_tc_descendant_fsn	Selects the id and fully specified name of each subtype descendant of a concept specified by conceptId.	fsn
snap_tc_descendant_pref	Selects the id and preferred synonym of each subtype descendant of a concept specified by conceptId.	pref

Caption label

CapId	transitive-closure-view
CapType	Template

SQL Templates for Composite Views of Supertype Ancestors and Subtype Descendants

...

Caption label

CapId	transitive-closure-view
CapType	Example

Selecting Supertype Ancestors and Subtype Descendants

SQL Query

Code Block

language	sql
theme	Confluence

Select "Concept", conceptid, term from snap_pref where conceptId=

6025007

16001004
UNION
Select "Ancestor", id, term from snap_tc_ancestor_pref where conceptId=

6025007

16001004
UNION
Select "Descendant", id, term from snap_tc_descendant_pref where conceptId=

6025007

16001004;

Result

Concept

conceptId

term

Concept

16001004

Otalgia

Ancestor

22253000

Pain

Ancestor

102957003

Neurological finding

Ancestor

106147001

Sensory nervous system finding

Ancestor

118234003

Finding by site

Ancestor

118236001

Ear and auditory finding

Ancestor

118254002

Finding of head and neck region

Ancestor

138875005

SNOMED CT Concept

Ancestor

247234006

Ear finding

Ancestor

276435006

Pain / sensation finding

Ancestor

279001004

Pain finding at anatomical site

Ancestor

297268004

Ear, nose and throat finding

Ancestor

301354004

Pain of ear structure

Ancestor

301857004

Finding of body region

Ancestor

404684003

Clinical finding

Ancestor

406122000

Head finding

Ancestor

699697007

Finding of sensation by site

Descendant

12336008

Referred otalgia

Descendant

74123003

Otogenic otalgia

Descendant

162356005

Earache symptoms

Descendant

162359003

Bilateral earache

Descendant

430879002

Posterior auricular pain

Descendant

1084561000119106

Bilateral referred otalgia of ears

Descendant

1089561000119107

Referred otalgia of left ear

Descendant

1092171000119100

Referred otalgia of right ear

Proximal Primitive Parent Views

For each snapshot view the SNOMED CT example database includes two views that select the

Gloss

t	proximal primitive parents

of a specified concept and two views that select the

Gloss

t	subtype children

of a specified concept. These views select the id and either the fully specified name or preferred synonym of each parent or child concept. The characteristics of each of these views are shown in

Caption reference

CapRefId	parentproximal-childprimitive-view
CapRefType	Table

and a general template for the SQL definitions of these views is shown in

Caption reference

CapRefId	parentproximal-childprimitive-view
CapRefType	Template

. To create each of the views named in the table, the placeholders for {termtypeviewtype} need to be replaced with values in the Specific Settings column of the table.

Caption reference

CapRefId	parentproximal-childprimitive-view
CapRefType	Example

and

Caption reference

CapRefId	parent-child-view-2
CapRefType	Example

demonstrate demonstrates the use of these views to select the id and preferred term for a specified concept and all of its supertype parents and subtype children.

For each snapshot view the SNOMED CT example database includes ... . The characteristics of each of these views are shown in

Caption reference

CapRefId	proximal-primitive-view
CapRefType	Table

and a general template for the SQL definitions of these views is shown in

Caption reference

CapRefId	proximal-primitive-view
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	proximal-primitive-view
CapRefType	Example

demonstrates the use of these views to select the id and preferred term for a specified concept and its proximal primitive its proximal primitive parents.

Caption reference

CapRefId	proximal-primitive-view-2
CapRefType	Example

demonstrates the use of these views to select the id and preferred term for a specified concept and all the concepts that have this concept as a proximal primitive parent.

Caption label

CapId	proximal-primitive-view
CapType	Table

Views of Proximal Primitive Supertype Ancestors and Concepts with a Specific Proximal Primitive Ancestor

Name

Footnote Macro
Transitive closure and proximal primitive views are only available for the current snapshot.

Description

Specific Settings

{viewtype}

snap_pp_parent_fsn

Selects the id and fully specified name of each proximal primitive parent of a concept specified by conceptId.

fsn

snap_pp_parent_pref

Selects the id and preferred synonym of each proximal primitive parent of a concept specified by conceptId.

pref

snap_pp_child_fsn

Selects the id and fully specified name of each concept with a proximal primitive parent specified by conceptId.

fsn

snap_pp_child_pref

Selects the id and preferred

synonym

synonym of each concept with a proximal primitive parent specified by conceptId.

pref

Caption label

CapId	proximal-primitive-view
CapType	Template

SQL Templates for Proximal Primitive Supertype Views

...

Caption label

CapId	proximal-primitive-view
CapType	Example

Selecting Proximal Primitive Parents of a Concept

SQL Query

Code Block

language	sql
theme	Confluence

--
Select "Concept", conceptid, term from snap_pref where conceptId=21522001
UNION
Select "Proximal Primitive Parent", id, term from snap_pp_parent_pref where conceptId=21522001;

Result

Concept

conceptId

term

Concept

21522001

Abdominal pain

Proximal Primitive Parent

22253000

Pain

Caption label

CapId	proximal-primitive-view-2
CapType	Example

Selecting Concepts with a Specified Proximal Primitive Parent

Code Block

language	sql
theme	Confluence

Select "Concept", conceptid, term from snap_pref where conceptId=22253000
UNION
Select "Concept with PP-Parent: 21522001|Pain|", id, term from snap_pp_child_pref where conceptId=22253000;

Result

Concept

conceptId

term

Concept

22253000

Pain