cde-harmonization/db/cadsr-views.sql at main · monarch-initiative/cde-harmonization · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
CREATE VIEW
  de_flat
AS
 SELECT
   DataElement.*,
   DataElementConcept.preferredName AS dec_preferredName,
   DataElementConcept.preferredDefinition AS dec_preferredDefinition,
   DataElementConcept.longName AS dec_longName,
   DataElementConcept.context AS dec_context,


   ObjectClass.preferredName AS oc_preferredName,
   ObjectClass.preferredDefinition AS oc_preferredDefinition,
   ObjectClass.longName AS oc_longName,
   ObjectClass.context AS oc_context,

   ClassConcept.conceptCode AS cc_conceptCode,
   ClassConcept.longName AS cc_longName,
   ClassConcept.definition AS cc_definition,
   ClassConcept.primaryIndicator AS cc_primaryIndicator,

   Property.preferredName AS p_preferredName,
   Property.preferredDefinition AS p_preferredDefinition,
   Property.longName AS p_longName,
   Property.context AS p_context,

   PropertyConcept.conceptCode AS pc_conceptCode,
   PropertyConcept.longName AS pc_longName,
   PropertyConcept.definition AS pc_definition,
   PropertyConcept.primaryIndicator AS pc_primaryIndicator

 FROM
   DataElement

   INNER JOIN DataElementConcept ON (DataElement.DataElementConcept_uid = DataElementConcept.uid)

   INNER JOIN ObjectClass ON (DataElementConcept.ObjectClass_uid = ObjectClass.uid)
   INNER JOIN ObjectClass_Concepts ON (ObjectClass.uid = ObjectClass_Concepts.ObjectClass_uid)
   INNER JOIN Concept AS ClassConcept ON (ObjectClass_Concepts.Concepts_id = ClassConcept.id)

   INNER JOIN Property ON (DataElementConcept.Property_uid = Property.uid)
   INNER JOIN Property_Concepts ON (Property.uid = Property_Concepts.Property_uid)
   INNER JOIN Concept AS PropertyConcept ON (Property_Concepts.Concepts_id = PropertyConcept.id);

CREATE VIEW
  de_flat_slim
AS
 SELECT
   preferredName,
   longName,
   context,
   dec_preferredName,
   dec_longName,
   dec_context,

   oc_preferredName,
   oc_longName,
   oc_context,

   cc_conceptCode,
   cc_longName,
   cc_primaryIndicator,

   p_preferredName,
   p_longName,
   p_context,

   pc_conceptCode,
   pc_longName,
   pc_primaryIndicator

 FROM
   de_flat;

CREATE VIEW
  de_flat_slim_primary
AS
 SELECT
  *
 FROM
  de_flat_slim
 WHERE
  cc_primaryIndicator = "Yes" AND pc_primaryIndicator = "Yes";


CREATE VIEW
  dce_flat
AS
 SELECT
   DataElementConcept.*,

   ObjectClass.preferredName AS oc_preferredName,
   ObjectClass.preferredDefinition AS oc_preferredDefinition,
   ObjectClass.longName AS oc_longName,
   ObjectClass.context AS oc_context,

   ClassConcept.conceptCode AS cc_conceptCode,
   ClassConcept.longName AS cc_longName,
   ClassConcept.definition AS cc_definition,
   ClassConcept.primaryIndicator AS cc_primaryIndicator,

   Property.preferredName AS p_preferredName,
   Property.preferredDefinition AS p_preferredDefinition,
   Property.longName AS p_longName,
   Property.context AS p_context,

   PropertyConcept.conceptCode AS pc_conceptCode,
   PropertyConcept.longName AS pc_longName,
   PropertyConcept.definition AS pc_definition,
   PropertyConcept.primaryIndicator AS pc_primaryIndicator

 FROM
   DataElementConcept

   INNER JOIN ObjectClass ON (DataElementConcept.ObjectClass_uid = ObjectClass.uid)
   INNER JOIN ObjectClass_Concepts ON (ObjectClass.uid = ObjectClass_Concepts.ObjectClass_uid)
   INNER JOIN Concept AS ClassConcept ON (ObjectClass_Concepts.Concepts_id = ClassConcept.id)

   INNER JOIN Property ON (DataElementConcept.Property_uid = Property.uid)
   INNER JOIN Property_Concepts ON (Property.uid = Property_Concepts.Property_uid)
   INNER JOIN Concept AS PropertyConcept ON (Property_Concepts.Concepts_id = PropertyConcept.id);