Duplicated CONCEPT_CODEs in MESH

[ericaVoss]

for v5.0 2014-10-15

SELECT CONCEPT_CODE, VOCABULARY_ID, DOMAIN_ID, COUNT(*)
FROM CONCEPT
WHERE INVALID_REASON IS NULL
AND VOCABULARY_ID NOT IN (
    'MedDRA','SMQ','DRG'
)
AND CONCEPT_CODE NOT IN (
    'OMOP generated'
)
GROUP BY CONCEPT_CODE, VOCABULARY_ID, DOMAIN_ID
HAVING COUNT(*) > 1;

CONCEPT_CODE  VOCABULARY_ID       DOMAIN_ID              COUNT(*)
D014508 Mesh    Drug/Measurement    2
D014364 Mesh    Condition/Drug  2
D001646 Mesh    Condition/Drug  2

These Mesh CONCEPT_CODES get duplicated because they have this multi-domain (like "Drug/Measurement") but then they further break down later into their separate CONCEPT_CLASS_IDs. It just feels odd and there are only 3 of them.

[ericaVoss]

VERSION v5.0 21-Mar-2015

Did we every decide if this is okay or not? Still exists.

SELECT c.CONCEPT_CODE COLLATE SQL_Latin1_General_CP1_CS_AS AS CONCEPT_CODE, c.VOCABULARY_ID, COUNT(*) AS NUM_OR_ROWS
FROM CONCEPT c
WHERE GETDATE() BETWEEN c.VALID_START_DATE AND c.VALID_END_DATE
AND c.INVALID_REASON IS NULL
AND c.VOCABULARY_ID NOT IN (
'MedDRA' /*MedDRA*/, 'DRG' /*DRG*/, 'SMQ' /*SMQ*/, 
'Drug Type' /*OMOP Drug Exposure Type*/, 'Condition Type' /*OMOP Condition Occurrence Type*/, 
'Procedure Type' /*OMOP Procedure Occurrence Type*/, 'Observation Type' /*OMOP Observation Type*/, 
'Death Type' /*OMOP Death Type*/, 'CCS' /*CCS*/, 'Visit Type','Note Type','Meas Type','Device Type','Obs Period Type','ABMS'
)
GROUP BY c.CONCEPT_CODE COLLATE SQL_Latin1_General_CP1_CS_AS, c.VOCABULARY_ID
HAVING COUNT(*) > 1

[cgreich]

Going to fix it

[ericaVoss]

Just making a note that this still exists in v5.0 3-Apr-2015 - it is not high priority.

[cgreich]

Understood. Mesh will be refactored entirely. Stand by please.

[ericaVoss]

This is additionally discussed in this issue so we may want to just close this one: https://github.com/OHDSI/Vocabulary-v5.0/issues/38

[cgreich]

Fixed.