Versions Compared

Version Old Version 13 New Version 14
Changes made by

Rick Wood

Rick Wood

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  • rxnorm_merge: RXCUI Merges

    • Same as nci_merge but with RXNORM

      Code Block
      select a.id clusterId, a.id conceptId 
from concepts a, concepts_atoms b, atoms c 
where a.terminology = :terminology   
and a.id = b.concepts_id   and b.atoms_id = c.id    
and c.terminology='RXNORM'  
group by a.id 
having count(distinct c.conceptId)>1



  • cbo_merge: Merged SCUI current version CBO atoms

    • Same as nci_merge but with CBO

      Code Block
      select a.id clusterId, a.id conceptId 
from concepts a, concepts_atoms b, atoms c 
where a.terminology = :terminology   
and a.id = b.concepts_id   and b.atoms_id = c.id    
and c.terminology='CBO'  
group by a.id 
having count(distinct c.conceptId)>1

  • mdr_merge: Merged SDUI current version MDR atoms

    Code Block
    If SDUI, return descriptor ID instead of conceptId?
  • pdq_merge: Merged SDUI current version PDQ atoms
  • sct_sepfnpt: SNOMED concept clusters where the FN and PT terms are separated
    • DUPLICATE of above

  • rxnorm_split: RXCUI splits

    Code Block
    see split query above

  • nci_pdq_merge: Concepts containing current version NCI and current version PDQ atoms

    Code Block
    SELECT DISTINCT 
    ca.concepts_id conceptId
FROMSELECT 
    c.id clusterId, c.id conceptId
FROM
    concepts c,
    concepts_atoms ca,
    atoms a
WHERE
    c.terminology = :terminology
       atoms a,AND c.id    atoms a1,
    concepts_atoms ca,
    concepts_atoms ca1,= ca.concepts_id
     terminologies t,     terminologies t1
WHERE
    AND ca.atoms_id = a.id
        AND ca1.atoms_id = a1a.terminology = 'MDR'
GROUP BY c.id
HAVING COUNT(DISTINCT a.descriptorId) > 1

  • pdq_merge: Merged SDUI current version PDQ atoms

    Code Block
    SELECT 
  AND  a.terminology = 'NCI'
c.id clusterId, c.id conceptId
FROM
    concepts c,
 AND a.terminology = t.terminology   concepts_atoms ca,
    atoms a
WHERE
  AND  ac.versionterminology = t.version:terminology
        AND tc.currentid = trueca.concepts_id
        AND a1ca.terminologyatoms_id = 'PDQ'a.id
        AND a1a.terminology = t1.terminology
        AND a1.version = t1.version
        AND t1.current = true        
        AND ca.concepts_id = ca1.concepts_id
    nci_sct
    'PDQ'
GROUP BY c.id
HAVING COUNT(DISTINCT a.descriptorId) > 1
  • sct_sepfnpt: SNOMED concept clusters where the FN and PT terms are separated
    • DUPLICATE of above

  • rxnorm_split: RXCUI splits

     

  • nci_pdq_merge: Concepts containing current version NCI and current version SNOMEDCT PDQ atoms

    Code Block
    SELECT DISTINCT
     cac.concepts_id conceptId
FROM
    atoms a,
    atoms a1,
    concepts_atoms cac,
    concepts_atoms ca1c1,
    terminologiesconcepts_atoms tca,
    terminologies t1
WHEREconcepts_atoms ca1,
     ca.atoms_id = a.idterminologies t,
    terminologies t1
WHERE
   AND ca1c.atoms_idterminology = a1.id:terminology
        AND ac1.terminology = 'NCI':terminology
        AND ac.terminologyid = tca.terminologyconcepts_id
        AND aca.versionatoms_id = ta.versionid
        AND tc1.currentid = trueca1.concepts_id
        AND ca1.atoms_id = a1.id
        AND a.terminology = 'SNOMEDCT_USNCI'
        AND a1a.terminology = t1t.terminology
        AND a1a.version = t1t.version
        AND t1t.current = TRUE
true        AND a1.terminology = 'PDQ'
        AND ca.concepts_id = ca1.concepts_id

  • ambig_no_ncimth_pn: Ambiguous concepts where at least one has no PN (sepstring)

    Code Block
    Ambiguous?
    • ambig_no_mth_pn: Ambiguous concepts where at least one has no PN (sepstring)
    ambig_no_rel: Ambiguous concepts where at least one pair lacks an approved REL
    pn_pn_ambig: Identical (same SUI) PN's in multiple concepts

  • multiple_pn: Concepts with multiple MTH/PN atoms

    Code Block
    SELECT DISTINCT
    ca.concepts_id conceptId
FROM
    atoms a,
    concepts_atoms ca
WHERE
    ca.atoms_id = a.id
        AND a.terminology = 'MTH'
        AND a.termType = 'PN'
GROUP BY ca.concepts_id
HAVING COUNT(ca.concepts_id) > 1
    • pn_no_ambig: Concept has MTH/PN atom but no ambiguous string
    ambig_pn: MTH/PN atom is ambiguous but has no matching ambiguous string
    pn_orphan: MTH/PNs on their own

  • nosty: No STY

    Code Block
    SELECT DISTINCT
    c.id conceptId
FROM
    concepts c
        LEFT JOIN
    concepts_semantic_type_components cs ON (c.id = cs.concepts_id)
WHERE
    cs.semanticTypes_id IS NULL

  • missing_sty: Reviewed concepts without releasable Semantic Types

    Code Block
    -- Is 'Reviewed' =  workflowStatus.READY_FOR_PUBLICATION?  How about PUBLISHED?
 
SELECT DISTINCT
    c.id conceptId
FROM
    concepts c
WHERE
    c.workflowStatus = 'READY_FOR_PUBLICATION'
        AND NOT c.id IN (SELECT 
            c.id
        FROM
       a1.terminology = t1.terminology
        AND a1.version = t1.version
        AND t1.current = TRUE
        AND c.id = c1.id

  • nci_sct_merge: Concepts containing current version NCI and current version SNOMEDCT atoms

    Code Block
    SELECT DISTINCT
    c.id conceptId
FROM
    atoms a,
    atoms a1,
    concepts c,
    concepts c1,
    concepts_atoms ca,
    concepts_atoms ca1,
    terminologies t,
    terminologies t1
WHERE
    c.terminology = :terminology
        AND c1.terminology = :terminology
        AND c.id = ca.concepts_id
        AND ca.atoms_id = a.id
        AND c1.id = ca1.concepts_id
        AND ca1.atoms_id = a1.id
        AND a.terminology = 'NCI'
        AND a.terminology = t.terminology
        AND a.version = t.version
        AND t.current = TRUE
        AND a1.terminology = 'SNOMEDCT_US'
        AND a1.terminology = t1.terminology
        AND a1.version = t1.version
        AND t1.current = TRUE
        AND c.id = c1.id

  • ambig_no_ncimth_pn: Ambiguous concepts where at least one has no PN (sepstring)

    Code Block
    Ambiguous?

  • ambig_no_mth_pn: Ambiguous concepts where at least one has no PN (sepstring)

  • ambig_no_rel: Ambiguous concepts where at least one pair lacks an approved REL

  • pn_pn_ambig: Identical (same SUI) PN's in multiple concepts

  • multiple_pn: Concepts with multiple MTH/PN atoms

    Code Block
    SELECT DISTINCT
    c.id conceptId
FROM
    concepts c,
    atoms a,
    concepts_atoms ca
WHERE
    c.terminology = :terminology
        AND c.id = ca.concepts_id
        AND ca.atoms_id = a.id
        AND a.terminology = 'MTH'
        AND a.termType = 'PN'
GROUP BY c.id
HAVING COUNT(c.id) > 1

  • pn_no_ambig: Concept has MTH/PN atom but no ambiguous string

  • ambig_pn: MTH/PN atom is ambiguous but has no matching ambiguous string

  • pn_orphan: MTH/PNs on their own

  • nosty: No STY

    Code Block
    SELECT DISTINCT
    c.id conceptId
FROM
    concepts c
        LEFT JOIN
    concepts_semantic_type_components cs ON (c.id = cs.concepts_id)
WHERE
	c.terminology= :terminology
    AND cs.semanticTypes_id IS NULL

  • missing_sty: Reviewed concepts without releasable Semantic Types

    Code Block
    -- Is 'Reviewed' =  workflowStatus.READY_FOR_PUBLICATION?  How about PUBLISHED?
 SELECT DISTINCT
    c.id conceptId
FROM
    concepts c
WHERE
	c.terminology= :terminology
    AND c.workflowStatus = 'READY_FOR_PUBLICATION'
        AND NOT c.id IN (SELECT 
            c.id
        FROM
            concepts c,
            concepts_semantic_type_components cs,
            semantic_type_components s
        WHERE
            c.terminology = :terminology
                AND c.id = cs.concepts_id
                AND cs.semanticTypes_id = s.id
                AND s.publishable = TRUE)

  • cbo_chem: Current version CBO concepts with CHEM STYs

  • Code Block
    -- to find chemical semantic types
(select distinct semanticTypeCategoryMap sty
from projects a, ProjectJpa_semanticTypeCategoryMap b
where a.id = b.ProjectJpa_id
  and semanticTypeCategoryMap_KEY = 'chemical'
  and a.terminology = :terminology);
 
-- Rick update 3/2/2017
(select distinct semanticTypeCategoryMap_KEY sty
from projects a, ProjectJpa_semanticTypeCategoryMap b
where a.id = b.ProjectJpa_id
  and semanticTypeCategoryMap = 'chem'
  and a.terminology = :terminology)
 
 



  • go_chem: Current version GO concepts with CHEM STYs

    Code Block
    SELECT DISTINCT
    c.id conceptId
FROM
    concepts c,
    
       concepts_semantic_type_components cs,
       
    semantic_type_components s
        WHERE
components s,
    terminologies t
WHERE
    c.idterminology = cs.concepts_id
     'GO'
          AND csc.semanticTypes_idterminology = s.id
  t.terminology
             AND sc.publishableversion = TRUE)

  • cbo_chem: Current version CBO concepts with CHEM STYs

  • Code Block
    -- to find chemical semantic types
(select distinct semanticTypeCategoryMap sty
from projects a, ProjectJpa_semanticTypeCategoryMap b
where at.version
        AND t.current = TRUE
        AND c.id = bcs.ProjectJpaconcepts_id
      and semanticTypeCategoryMap_KEY = 'chemical'
  and a.terminology = :terminology);
 
 

    go_chem: Current version GO concepts with CHEM STYs

    Code Block
    --Not sure about final line.
 
AND cs.semanticTypes_id = s.id
        AND s.semanticType IN ((SELECT DISTINCT
    c.id conceptId FROM     concepts c,semanticTypeCategoryMap_KEY sty
   concepts_semantic_type_components cs,     FROM
  semantic_type_components s,     terminologies t WHERE  projects a,
 c.terminology = 'GO'         AND c.terminology = t.terminologyProjectJpa_semanticTypeCategoryMap b
        WHERE
 AND c.version = t.version        a.id AND= t.current = trueb.ProjectJpa_id
            AND c.id = cs.concepts_id AND semanticTypeCategoryMap = 'chem'
    AND cs.semanticTypes_id = s.id         AND sa.semanticTypeterminology LIKE 'Chem%'= :terminology))

  • mdr_chem: Current version MDR concepts with CHEM STYs

    • See above

  • true_orphan: Concepts with no releaseable relationships to any other concept

    Code Block
    SELECT 
    c.id conceptId
FROM
    concepts c
WHERE
    c.terminology = :terminology
        AND NOT c.id IN (SELECT 
            c.id
        FROM
            concepts c,
            concept_relationships cr
        WHERE
            cr.from_id = c.id
                AND cr.publishable = TRUE)

  • deleted_cui_split: Complex split/merge case CUIs that are going away - will need bequeathal

  • split_demotions: Demotions overlapping with a bad merge that was split
  • nci_pt_mrg: Merged current version NCI/PT atoms
  • mxsuppr: Concepts with identical LUI atoms that have mixed suppressibility

...