· 5 years ago · Oct 09, 2020, 11:36 AM
1--1. Update latest_update field to new date
2 -- doesn't work
3DO $_$
4BEGIN
5 PERFORM VOCABULARY_PACK.SetLatestUpdate(
6 pVocabularyName => 'CIM10',
7 pVocabularyDate => (SELECT vocabulary_date FROM dev_cim10.CIM10_2020 LIMIT 1),
8 pVocabularyVersion => (SELECT vocabulary_version FROM dev_cim10.CIM10_2020 LIMIT 1),
9 pVocabularyDevSchema => 'DEV_CIM10'
10);
11END $_$;
12
13--2. Truncate all working tables
14TRUNCATE TABLE concept_stage;
15TRUNCATE TABLE concept_relationship_stage;
16TRUNCATE TABLE concept_synonym_stage;
17TRUNCATE TABLE pack_content_stage;
18TRUNCATE TABLE drug_strength_stage;
19
20--3. Create temporary tables with classes and modifiers from XML source
21--modifier classes
22DROP TABLE IF EXISTS modifier_classes;
23CREATE UNLOGGED TABLE modifier_classes AS
24SELECT s1.modifierclass_code,
25 s1.modifierclass_modifier,
26 s1.superclass_code,
27 s1.rubric_id,
28 s1.rubric_kind,
29 l.rubric_label
30FROM (
31 SELECT *
32 FROM (
33 SELECT (xpath('./@code', i.xmlfield))[1]::VARCHAR modifierclass_code,
34 (xpath('./@modifier', i.xmlfield))[1]::VARCHAR modifierclass_modifier,
35 (xpath('./SuperClass/@code', i.xmlfield))[1]::VARCHAR superclass_code,
36 UNNEST(xpath('./Rubric/@id', i.xmlfield))::VARCHAR rubric_id,
37 UNNEST(xpath('./Rubric/@kind', i.xmlfield))::VARCHAR rubric_kind,
38 UNNEST(xpath('./Rubric', i.xmlfield)) rubric_label
39 FROM (
40 SELECT UNNEST(xpath('/ClaML/ModifierClass', i.xmlfield)) xmlfield
41 FROM dev_cim10.CIM10_2020 i
42 ) AS i
43 ) AS s0
44 ) AS s1
45LEFT JOIN LATERAL(SELECT STRING_AGG(LTRIM(REGEXP_REPLACE(rubric_label, '\t', '', 'g')), '') AS rubric_label FROM (
46 SELECT UNNEST(xpath('//text()', s1.rubric_label))::VARCHAR rubric_label
47 ) AS s0) l ON TRUE;
48
49--classes
50DROP TABLE IF EXISTS classes;
51CREATE UNLOGGED TABLE classes AS
52WITH classes
53AS (
54 SELECT s1.class_code,
55 s1.rubric_kind,
56 s1.superclass_code,
57 s1.modifiedby_code,
58 l.rubric_label
59 FROM (
60 SELECT *
61 FROM (
62 SELECT (xpath('./@code', i.xmlfield))[1]::VARCHAR class_code,
63 l.superclass_code,
64 l1.modifiedby_code,
65 UNNEST(xpath('./Rubric/@kind', i.xmlfield))::VARCHAR rubric_kind,
66 UNNEST(xpath('./Rubric', i.xmlfield)) rubric_label
67 FROM (
68 SELECT UNNEST(xpath('/ClaML/Class', i.xmlfield)) xmlfield
69 FROM dev_cim10.CIM10_2020 i
70 ) AS i
71 LEFT JOIN LATERAL(SELECT UNNEST(xpath('./SuperClass/@code', i.xmlfield))::VARCHAR superclass_code) l ON true
72 LEFT JOIN LATERAL(SELECT UNNEST(xpath('./ModifiedBy/@code', i.xmlfield))::VARCHAR modifiedby_code) l1 ON true
73 ) AS s0
74 ) AS s1
75 LEFT JOIN LATERAL(SELECT string_agg(LTRIM(REGEXP_REPLACE(rubric_label, '\t', '', 'g')), '') AS rubric_label FROM (
76 SELECT UNNEST(xpath('//text()', s1.rubric_label))::VARCHAR rubric_label
77 ) AS s0) l ON TRUE
78 )
79--modify classes_table replacing preferred name to preferredLong where it's possible
80SELECT a.class_code,
81 a.rubric_kind,
82 a.superclass_code,
83 a.modifiedby_code,
84 COALESCE(b.rubric_label, a.rubric_label) AS rubric_label
85FROM classes a
86LEFT JOIN classes b ON a.class_code = b.class_code
87 AND a.rubric_kind = 'preferred'
88 AND b.rubric_kind = 'preferredLong'
89WHERE a.rubric_kind != 'preferredLong';
90
91--4. Fill the concept_stage
92
93create table c34 as (
94select c.class_code as start_code,
95c.rubric_label as start_name,
96 (c.class_code||m.modifierclass_code) as combo_code_34,
97 (c.rubric_label||', '||m.rubric_label) as combo_name_34,
98 c.modifiedby_code as mid
99from classes c join
100 modifier_classes m on m.modifierclass_modifier = c.modifiedby_code
101 where c.modifiedby_code like '%4' and c.rubric_kind='preferred'
102and m.rubric_kind='preferred' and length (class_code)=3
103);
104
105CREATE TABLE codes AS (
106WITH c1 AS (
107 SELECT
108SUBSTRING (class_code, 1, 3) AS c1_code,
109 class_code,
110 rubric_label AS name_4,
111 modifiedby_code AS mid1
112FROM classes
113WHERE length (class_code)=5 AND rubric_kind='preferred' )
114
115SELECT c2.class_code AS start_code,
116c2.rubric_label AS name_2,
117c1.class_code AS code_4m,
118c1.name_4, c1.mid1,
119c2.modifiedby_code AS mid2
120 FROM classes c2
121 JOIN c1 ON c1_code=c2.class_code
122 WHERE c2.rubric_kind='preferred')
123;
124-- insert concepts with modifiedby like '%4'
125INSERT INTO codes
126SELECT start_code, start_name, combo_code_34, combo_name_34, mid FROM c34
127;
128
129SELECT * FROM classes WHERE class_code ='F00';
130
131--inserts of E11
132--E110 Type 2 diabetes mellitus, with coma
133
134insert into codes
135values ('E11',
136'Type 2 diabetes mellitus',
137'E11.0',
138'Type 2 diabetes mellitus, with coma',
139null,
140null
141)
142;
143-- E111 Diabète sucré de type 2, avec acidocétose
144insert into codes
145values ('E11',
146'Type 2 diabetes mellitus',
147'E11.1',
148'Diabète sucré de type 2, avec acidocétose',
149null,
150null
151)
152;
153-- E112 Diabète sucré de type 2, avec complications rénales
154insert into codes
155values ('E11',
156'Type 2 diabetes mellitus',
157'E11.2',
158'Diabète sucré de type 2, avec complications rénales',
159null,
160null
161)
162;
163--E113 Diabète sucré de type 2, avec complications oculaires
164insert into codes
165values ('E11',
166'Type 2 diabetes mellitus',
167'E11.3',
168'Diabète sucré de type 2, avec complications oculaires',
169null,
170null
171)
172;
173--E114 Diabète sucré de type 2, avec complications neurologiques
174insert into codes
175values (
176'E11',
177'Type 2 diabetes mellitus',
178'E11.4',
179'Diabète sucré de type 2, avec complications neurologiques',
180null,
181null
182)
183;
184--E115 Diabète sucré de type 2, avec complications vasculaires périphériques
185insert into codes
186values ('E11',
187'Type 2 diabetes mellitus',
188'E11.5',
189'Diabète sucré de type 2, avec complications vasculaires périphériques',
190null,
191null
192)
193;
194-- E116 Diabète sucré de type 2, avec autres complications précisées
195insert into codes
196values ('E11',
197'Type 2 diabetes mellitus',
198'E11.6',
199'Diabète sucré de type 2, avec autres complications précisées',
200null,
201null
202)
203;
204-- E117 Diabète sucré de type 2, avec complications multiples
205insert into codes
206values ('E11',
207'Type 2 diabetes mellitus',
208'E11.7',
209'Diabète sucré de type 2, avec complications multiples',
210null,
211null
212)
213;
214
215-- E118 Diabète sucré de type 2, avec complications non précisées
216insert into codes
217values ('E11',
218'Type 2 diabetes mellitus',
219'E11.8',
220'Diabète sucré de type 2, avec complications non précisées',
221null,
222null
223)
224;
225
226--E119 Diabète sucré de type 2, sans complication
227insert into codes
228values ('E11',
229'Type 2 diabetes mellitus',
230'E11.9',
231'Diabète sucré de type 2, sans complication',
232null,
233null
234)
235;
236
237-- check for doubles
238
239SELECT * FROM codes
240WHERE CTID NOT IN
241(SELECT min (CTID)
242FROM codes
243GROUP BY code_4m, start_code, name_2, name_4, mid2) ;
244
245Insert into codes (
246select class_code, rubric_label, (class_code||'+'), null as name_4 from classes
247where class_code like 'F03' and rubric_kind='preferred');
248
249select * from codes;
250
251create table codes_3456 as (
252select distinct a.*, code_4m||m.modifierclass_code as code_5m,name_4||', '|| m.rubric_label as name_5,
253code_4m||m.modifierclass_code ||m6.modifierclass_code as code_6m,
254name_4||', '|| m.rubric_label || ', ' || m6.rubric_label as name_6
255 from codes a
256join classes b on a.start_code = b.class_code
257join modifier_classes m on m.modifierclass_modifier = b.modifiedby_code and m.modifierclass_modifier like '%5'
258left join classes b6 on a.start_code = b6.class_code
259left join modifier_classes m6 on m6.modifierclass_modifier = b6.modifiedby_code and m6.modifierclass_modifier like '%6'
260where b.rubric_kind = 'preferred' and m.rubric_kind = 'preferred' and m6.rubric_kind = 'preferred');
261-- from 3 to 5 and 6 lvl
262
263insert into codes_3456 (
264select c.*,(code_4m||m.modifierclass_code) as code_5, (name_4||', '||m.rubric_label) as name_5
265 from codes c join classes a on code_4m=a.class_code
266join modifier_classes m on a.modifiedby_code=m.modifierclass_modifier
267where a.modifiedby_code like '%5' and a.rubric_kind='preferred' and m.rubric_kind='preferred');
268-- from 4 to 5th lvl
269
270-- there are no modifiers from 4th sign codes to 6th lvl^ select * from codes c join classes a on code_4m=a.class_code join modifier_classes m on a.modifiedby_code=m.modifierclass_modifier where a.modifiedby_code like '%6' ;
271
272insert into codes_3456
273(
274select start_code, name_2, code_4m, name_4, class_code, rubric_label from classes a join codes c on c.code_4m=a.superclass_code
275where length(a.class_code) =6
276and modifiedby_code is null and a.rubric_kind='preferred' and class_code not like '%+%'
277);
278-- native 5th
279
280/*insert into codes_3456(
281select distinct a.*,
282code_4m||m.modifierclass_code as code_5m,
283name_2||', '|| m.rubric_label as name_5,
284code_4m||m.modifierclass_code ||m6.modifierclass_code as code_6m,
285name_2||', '|| m.rubric_label || ', ' || m6.rubric_label as name_6
286 from codes a
287join classes b on a.start_code = b.class_code
288join modifier_classes m on m.modifierclass_modifier = b.modifiedby_code and m.modifierclass_modifier like '%5'
289left join classes b6 on a.start_code = b6.class_code
290left join modifier_classes m6 on m6.modifierclass_modifier = b6.modifiedby_code and m6.modifierclass_modifier like '%6'
291where b.rubric_kind = 'preferred' and m.rubric_kind = 'preferred' and m6.rubric_kind = 'preferred'
292and start_code='F03' );*/
293-- for F003
294with a as (select distinct a.*,
295code_4m||m.modifierclass_code as code_5m,
296name_2||', '|| m.rubric_label as name_5,
297code_4m||m.modifierclass_code ||m6.modifierclass_code as code_6m,
298name_2||', '|| m.rubric_label || ', ' || m6.rubric_label as name_6
299 from codes a
300join classes b on a.start_code = b.class_code
301join modifier_classes m on m.modifierclass_modifier = b.modifiedby_code and m.modifierclass_modifier like '%5'
302left join classes b6 on a.start_code = b6.class_code
303left join modifier_classes m6 on m6.modifierclass_modifier = b6.modifiedby_code and m6.modifierclass_modifier like '%6'
304where b.rubric_kind = 'preferred' and m.rubric_kind = 'preferred' and m6.rubric_kind = 'preferred'
305and start_code='F03' )
306update codes_3456
307set start_code=a.start_code,
308name_2=a.name_2,
309code_4m=a.code_4m,
310name_4=a.name_4,
311mid1=a.mid1,
312mid2=a.mid2,
313code_5m=a.code_5m,
314name_5=a.name_5,
315code_6m=a.code_6m,
316name_6=a.name_6
317from a where codes_3456.start_code='F03' and codes_3456.start_code =a.start_code;
318
319insert into codes_3456
320(
321select substring (c.class_code,1,3),a.rubric_label, c.class_code, c.rubric_label, (c.class_code||modifierclass_code), (c.rubric_label||', '||m.rubric_label)
322from classes c
323join modifier_classes m on m.modifierclass_modifier = c.modifiedby_code
324join classes a on substring (c.class_code,1,3)=a.class_code
325 where modifierclass_modifier like '%1'
326 and length (modifierclass_code)=1
327 and a.rubric_kind='preferred' ) ;
328-- for where modifierclass_modifier like '%1'
329
330insert into codes_3456
331select a.*,
332(code_4m||m.modifierclass_code) as code_5,
333(name_4 || ', '|| m.rubric_label) as name_5, null, null
334 from codes a
335join classes c on c.class_code=a.start_code
336join modifier_classes m on m.modifierclass_modifier=c.modifiedby_code
337where start_code~'E11'
338and c.rubric_kind='preferred'
339and m.rubric_kind='preferred';
340;
341-- for E11 concepts
342create table prestage
343(
344concept_name varchar,
345domain_id varchar,
346vocabulary_id varchar,
347concept_class_id varchar,
348standard_concept varchar,
349concept_code varchar,
350valid_start_date date,
351valid_end_date date,
352invalid_reason varchar);
353
354 Insert into prestage
355(
356concept_name,
357domain_id,
358vocabulary_id,
359concept_class_id,
360standard_concept,
361concept_code,
362valid_start_date,
363valid_end_date,
364invalid_reason
365)
366with t1 as (select distinct name_2, start_code from codes_3456 )
367select
368name_2 as concept_name,
369'Condition',
370'CIM10',
371' ICD10 code' as concept_class_id,
372'Non-standard' as standard_concept,
373start_code as concept_code,
374CURRENT_DATE -1 AS valid_start_date,
375 TO_DATE('20991231','yyyymmdd') AS valid_end_date,
376 null as invalid_reason
377 from t1 ;
378-- from codes_3456
379
380 Insert into prestage
381(
382concept_name,
383domain_id,
384vocabulary_id,
385concept_class_id,
386standard_concept,
387concept_code,
388valid_start_date,
389valid_end_date,
390invalid_reason
391)
392with t1 as (select distinct name_4, code_4m from codes_3456 )
393select
394name_4 as concept_name,
395'Condition',
396'CIM10',
397' ICD10 code' as concept_class_id,
398'Non-standard' as standard_concept,
399code_4m as concept_code,
400CURRENT_DATE -1 AS valid_start_date,
401 TO_DATE('20991231','yyyymmdd') AS valid_end_date,
402 null as invalid_reason
403 from t1 ;
404;
405-- 4lvl codes
406 Insert into prestage
407(
408concept_name,
409domain_id,
410vocabulary_id,
411concept_class_id,
412standard_concept,
413concept_code,
414valid_start_date,
415valid_end_date,
416invalid_reason
417)
418with t1 as (select distinct name_5, code_5m from codes_3456 )
419select
420name_5 as concept_name,
421'Condition',
422'CIM10',
423' ICD10 code' as concept_class_id,
424'Non-standard' as standard_concept,
425code_5m as concept_code,
426CURRENT_DATE -1 AS valid_start_date,
427 TO_DATE('20991231','yyyymmdd') AS valid_end_date,
428 null as invalid_reason
429 from t1 ;
430-- 5lvl codes
431 Insert into prestage
432(
433concept_name,
434domain_id,
435vocabulary_id,
436concept_class_id,
437standard_concept,
438concept_code,
439valid_start_date,
440valid_end_date,
441invalid_reason
442)
443with t1 as (select distinct name_6, code_6m from codes_3456 )
444select
445name_6 as concept_name,
446'Condition',
447'CIM10',
448' ICD10 code' as concept_class_id,
449'Non-standard' as standard_concept,
450code_6m as concept_code,
451CURRENT_DATE -1 AS valid_start_date,
452 TO_DATE('20991231','yyyymmdd') AS valid_end_date,
453 null as invalid_reason
454 from t1 ;
455-- 6lvl concepts
456
457Insert into prestage
458(
459concept_name,
460domain_id,
461vocabulary_id,
462concept_class_id,
463standard_concept,
464concept_code,
465valid_start_date,
466valid_end_date,
467invalid_reason
468)
469with t1 as (select distinct name_2 as name, start_code as code from codes_3456 )
470select
471name as concept_name,
472'Condition',
473'CIM10',
474' ICD10 code' as concept_class_id,
475'Non-standard' as standard_concept,
476code as concept_code,
477CURRENT_DATE -1 AS valid_start_date,
478 TO_DATE('20991231','yyyymmdd') AS valid_end_date,
479 null as invalid_reason
480 from t1 ;
481-- 3lvls
482
483
484Insert into prestage
485(
486concept_name,
487domain_id,
488vocabulary_id,
489concept_class_id,
490standard_concept,
491concept_code,
492valid_start_date,
493valid_end_date,
494invalid_reason
495)
496with t1 as (select distinct name_4 as name, code_4m as code from codes )
497select
498name as concept_name,
499'Condition',
500'CIM10',
501' ICD10 code' as concept_class_id,
502'Non-standard' as standard_concept,
503code as concept_code,
504CURRENT_DATE -1 AS valid_start_date,
505 TO_DATE('20991231','yyyymmdd') AS valid_end_date,
506 null as invalid_reason
507 from t1 ;
508
509Insert into prestage
510(
511concept_name,
512domain_id,
513vocabulary_id,
514concept_class_id,
515standard_concept,
516concept_code,
517valid_start_date,
518valid_end_date,
519invalid_reason
520)
521with t1 as (select distinct rubric_label as name, class_code as code from classes where rubric_kind='preferred'
522and class_code not in (select concept_code from prestage) )
523select
524name as concept_name,
525'Condition',
526'CIM10',
527' ICD10 code' as concept_class_id,
528'Non-standard' as standard_concept,
529code as concept_code,
530CURRENT_DATE -1 AS valid_start_date,
531 TO_DATE('20991231','yyyymmdd') AS valid_end_date,
532 null as invalid_reason
533 from t1 ;
534
535Insert into prestage
536(
537concept_name,
538domain_id,
539vocabulary_id,
540concept_class_id,
541standard_concept,
542concept_code,
543valid_start_date,
544valid_end_date,
545invalid_reason
546)
547with t1 as (select distinct combo_name_34 as name, combo_code_34 as code from c34 )
548select
549name as concept_name,
550'Condition',
551'CIM10',
552' ICD10 code' as concept_class_id,
553'Non-standard' as standard_concept,
554code as concept_code,
555CURRENT_DATE -1 AS valid_start_date,
556 TO_DATE('20991231','yyyymmdd') AS valid_end_date,
557 null as invalid_reason
558 from t1 ;
559
560delete from prestage where concept_code like 'F03+';
561delete from prestage where concept_name is null;
562select * from codes_3456 where start_code ~ 'F03+%';
563
564delete FROM prestage
565WHERE CTID NOT IN
566(SELECT min (CTID)
567FROM prestage
568GROUP BY concept_name, concept_code) ;
569INSERT INTO concept_stage (
570 concept_name,
571 domain_id,
572 vocabulary_id,
573 concept_class_id,
574 standard_concept,
575 concept_code,
576 valid_start_date,
577 valid_end_date,
578 invalid_reason
579 )
580select * from prestage;
581--select * from prestage;
582
583--select * from concept where vocabulary_id = 'CIM10' and concept_code not in (
584--select concept_code from dev_cim10.prestage);
585
586
587--5. Inherit external relations from international ICD10 whenever possible
588INSERT INTO concept_relationship_stage (
589 concept_code_1,
590 concept_code_2,
591 vocabulary_id_1,
592 vocabulary_id_2,
593 relationship_id,
594 valid_start_date,
595 valid_end_date
596 )
597SELECT distinct -- there are duplicates in "classes" table
598c.concept_code AS concept_code_1,
599 c2.concept_code AS concept_code_2,
600 'CIM10' AS vocabulary_id_1,
601 c2.vocabulary_id AS vocabulary_id_2,
602 r.relationship_id AS relationship_id,
603 (
604 SELECT latest_update
605 FROM vocabulary
606 WHERE vocabulary_id = 'CIM10'
607 ) AS valid_start_date,
608 TO_DATE('20991231', 'yyyymmdd') AS valid_end_date
609FROM concept_stage cs
610 JOIN concept c ON
611 c.vocabulary_id = 'ICD10' and
612 cs.concept_code = c.concept_code
613 JOIN concept_relationship r ON r.concept_id_1 = c.concept_id
614 AND r.invalid_reason IS NULL
615 AND r.relationship_id IN (
616 'Maps to',
617 'Maps to value'
618 )
619JOIN concept c2 ON c2.concept_id = r.concept_id_2;
620
621
622--6. Append result to concept_relationship_stage table
623DO $_$
624BEGIN
625 PERFORM VOCABULARY_PACK.ProcessManualRelationships();
626END $_$;
627
628--7. Working with replacement mappings
629DO $_$
630BEGIN
631 PERFORM VOCABULARY_PACK.CheckReplacementMappings();
632END $_$;
633
634--8. Add mapping from deprecated to fresh concepts
635DO $_$
636BEGIN
637 PERFORM VOCABULARY_PACK.AddFreshMAPSTO();
638END $_$;
639
640--9. Add mapping from deprecated to fresh concepts for 'Maps to value'
641DO $_$
642BEGIN
643 PERFORM VOCABULARY_PACK.AddFreshMapsToValue();
644END $_$;
645
646--10. Deprecate 'Maps to' mappings to deprecated and upgraded concepts
647DO $_$
648BEGIN
649 PERFORM VOCABULARY_PACK.DeprecateWrongMAPSTO();
650END $_$;
651
652--11. Delete ambiguous 'Maps to' mappings
653DO $_$
654BEGIN
655 PERFORM VOCABULARY_PACK.DeleteAmbiguousMAPSTO();
656END $_$;
657
658--12. Add "subsumes" relationship between concepts where the concept_code is like of another
659CREATE INDEX IF NOT EXISTS trgm_idx ON concept_stage USING GIN (concept_code devv5.gin_trgm_ops); --for LIKE patterns
660ANALYZE concept_stage;
661INSERT INTO concept_relationship_stage (
662 concept_code_1,
663 concept_code_2,
664 vocabulary_id_1,
665 vocabulary_id_2,
666 relationship_id,
667 valid_start_date,
668 valid_end_date,
669 invalid_reason
670 )
671SELECT distinct c1.concept_code AS concept_code_1,
672 c2.concept_code AS concept_code_2,
673 c1.vocabulary_id AS vocabulary_id_1,
674 c1.vocabulary_id AS vocabulary_id_2,
675 'Subsumes' AS relationship_id,
676 CURRENT_DATE AS valid_start_date,
677 TO_DATE('20991231', 'yyyymmdd') AS valid_end_date,
678 NULL AS invalid_reason
679FROM concept_stage c1,
680 concept_stage c2
681WHERE c2.concept_code LIKE c1.concept_code || '%'
682 AND c1.concept_code <> c2.concept_code
683 AND NOT EXISTS (
684 SELECT 1
685 FROM concept_relationship_stage r_int
686 WHERE r_int.concept_code_1 = c1.concept_code
687 AND r_int.concept_code_2 = c2.concept_code
688 AND r_int.relationship_id = 'Subsumes'
689 );
690DROP INDEX trgm_idx;
691
692--13. Update domain_id for ICD10 from target concepts domains
693UPDATE concept_stage cs
694SET domain_id = i.domain_id
695FROM (
696 SELECT DISTINCT cs1.concept_code,
697 first_value(c2.domain_id) OVER (
698 PARTITION BY cs1.concept_code ORDER BY CASE c2.domain_id
699 WHEN 'Condition'
700 THEN 1
701 WHEN 'Observation'
702 THEN 2
703 WHEN 'Procedure'
704 THEN 3
705 WHEN 'Measurement'
706 THEN 4
707 WHEN 'Device'
708 THEN 5
709 ELSE 6
710 END
711 ) AS domain_id
712 FROM concept_relationship_stage crs
713 JOIN concept_stage cs1 ON cs1.concept_code = crs.concept_code_1
714 AND cs1.vocabulary_id = crs.vocabulary_id_1
715 AND cs1.vocabulary_id = 'CIM10'
716 JOIN concept c2 ON c2.concept_code = crs.concept_code_2
717 AND c2.vocabulary_id = crs.vocabulary_id_2
718
719 WHERE crs.relationship_id = 'Maps to'
720 AND crs.invalid_reason IS NULL
721
722 UNION ALL
723
724 SELECT DISTINCT cs1.concept_code,
725 first_value(c2.domain_id) OVER (
726 PARTITION BY cs1.concept_code ORDER BY CASE c2.domain_id
727 WHEN 'Condition'
728 THEN 1
729 WHEN 'Observation'
730 THEN 2
731 WHEN 'Procedure'
732 THEN 3
733 WHEN 'Measurement'
734 THEN 4
735 WHEN 'Device'
736 THEN 5
737 ELSE 6
738 END
739 )
740 FROM concept_relationship cr
741 JOIN concept c1 ON c1.concept_id = cr.concept_id_1
742 AND c1.vocabulary_id = 'CIM10'
743 JOIN concept c2 ON c2.concept_id = cr.concept_id_2
744
745 JOIN concept_stage cs1 ON cs1.concept_code = c1.concept_code
746 AND cs1.vocabulary_id = c1.vocabulary_id
747 WHERE cr.relationship_id = 'Maps to'
748 AND cr.invalid_reason IS NULL
749 AND NOT EXISTS (
750 SELECT 1
751 FROM concept_relationship_stage crs_int
752 WHERE crs_int.concept_code_1 = cs1.concept_code
753 AND crs_int.vocabulary_id_1 = cs1.vocabulary_id
754 AND crs_int.relationship_id = cr.relationship_id
755 )
756 ) i
757WHERE i.concept_code = cs.concept_code
758 AND cs.vocabulary_id = 'CIM10';
759
760
761--Manual fix for concepts without mapping
762update concept_stage
763set domain_id = 'Observation'
764where domain_id is null
765;
766
767
768
769--14. Clean up
770DROP TABLE modifier_classes;
771DROP TABLE classes;
772
773
774--additional French work
775-- Preserve original names in concept_synonym_stage
776insert into concept_synonym_stage (synonym_name,synonym_concept_code,synonym_vocabulary_id,language_concept_id)
777select
778 concept_name,
779 concept_code,
780 'CIM10',
781 4180190 -- French language
782from concept_stage
783;
784--update concept_Stage, set english names from ICD10
785update concept_stage cs set concept_name=c.concept_name
786from concept c
787where c.concept_code=cs.concept_code and c.vocabulary_id='ICD10';
788-- 11427 rows affected in 2019 version
789
790DO $_$
791BEGIN
792 PERFORM VOCABULARY_PACK.ProcessManualConcepts();
793END $_$;
794
795--remove duplicates
796DELETE FROM concept_stage cs WHERE EXISTS (SELECT 1 FROM concept_stage cs_int WHERE cs_int.concept_code = cs.concept_code AND cs_int.ctid > cs.ctid);
797
798-- At the end, the three tables concept_stage, concept_relationship_stage and concept_synonym_stage should be ready to be fed into the generic_update.sql script
799