Versions Compared

Version Old Version 22 New Version 23
Changes made by

jo (Unlicensed)

jo (Unlicensed)

Saved on

Key

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

...

  • nci_merge: Merged SCUI current version NCI atoms

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

  • nci_sub_split: Split SCUI current version NCI (or sub-source) atoms

    Code Block
    --Identify Sub-source atoms that aren't in the same concept as the NCI atom with the same conceptId
 
SELECT DISTINCT
    c.id conceptId1, c1.id conceptId2
FROM
    concepts c,
    concepts_atoms ca,
    atoms a,
    concepts c1,
    concepts_atoms ca1,
    atoms a1
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 a1.terminology IN (SELECT 
            terminology
        FROM
            root_terminologies
        WHERE
            family = 'NCI' AND terminology != 'NCI')
        AND a.conceptId = a1.conceptId
        AND c.id != c1.id

  • sct_sepfnpt: SNOMED concept clusters where the FN and PT terms are separated

    Code Block
    SELECT DISTINCT
    c.id conceptId1, c1.id conceptId2
FROM
    concepts c,
    concepts_atoms ca,
    atoms a,
    concepts c1,
    concepts_atoms ca1,
    atoms a1
WHERE
    c.terminology = :terminology
        AND c1.terminology = :terminology
        AND a.terminology = 'SNOMEDCT_US'
        AND a1.terminology = 'SNOMEDCT_US'
        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.termType = 'FN'
        AND a1.termType = 'PT'
        AND a.conceptId = a1.conceptId
        AND c.id != c1.id

  • cdsty_coc: Find concepts with Clinical Drug STY and any other STY

    Code Block
    SELECT DISTINCT
    c.id conceptId
FROM
    concepts c,
    concepts_semantic_type_components cs
WHERE
    c.terminology = :terminology
        AND c.id = cs.concepts_id
        AND cs.concepts_id IN (SELECT 
            cs.concepts_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.semanticType = 'Clinical Drug')
GROUP BY cs.concepts_id
HAVING COUNT(cs.concepts_id) > 1

  • multsty:concepts with more than 3 STYs|

    Code Block
    SELECT DISTINCT
    cs.concepts_id conceptId
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
GROUP BY c.id
HAVING COUNT(c.id) > 3

  • styisa:One STY is an ancestor of another in the STY isa hierarchy

    Code Block
    select t1.conceptId from 
(select c.id conceptId, st.id styId, st.treeNumber from concepts c, concepts_semantic_type_components cstc, (select stc.id, st.treeNumber from semantic_type_components stc join semantic_types st on (stc.semanticType = st.expandedForm)) st
WHERE c.terminology = :terminology
AND c.id = cstc.concepts_id
AND cstc.semanticTypes_id = st.id) t1
JOIN 
(select c.id conceptId, st.id styId, st.treeNumber from concepts c, concepts_semantic_type_components cstc, (select stc.id, st.treeNumber from semantic_type_components stc join semantic_types st on (stc.semanticType = st.expandedForm)) st
WHERE c.terminology = :terminology
AND c.id = cstc.concepts_id
AND cstc.semanticTypes_id = st.id) t2
on  (t1.conceptId = t2.conceptId and t1.styId != t2.styId and t1.treeNumber != t2.treeNumber)
WHERE t2.treeNumber like concat(t1.treeNumber, '.%')

  • sfo_lfo: Short form in one concept, long form in another

    Code Block
    --ShortForm/LongForm are related atoms
  -- These live in the DB as "SY" atom relationships with RELA value either equal to "expanded_form_of" or starting with "mth_" and ending with "_form_of"
 
SELECT 
    c1.id conceptId1, c2.id conceptId2
FROM
    concepts c1,
    concepts_atoms ca1,
    atoms a1,
    concepts c2,
    concepts_atoms ca2,
    atoms a2,
    (SELECT 
        ar.from_id, ar.to_id
    FROM
        atom_relationships ar
    WHERE
        publishable = TRUE
            AND relationshipType = 'SY'
            AND (additionalRelationshipType = 'expanded_form_of'
            OR additionalRelationshipType LIKE 'mth_%_form_of')) sfoLfoRels
WHERE
    c1.terminology = 'NCIMTH'
        AND c2.terminology = 'NCIMTH'
        AND c1.id = ca1.concepts_id
        AND ca1.atoms_id = a1.id
        AND c2.id = ca2.concepts_id
        AND ca2.atoms_id = a2.id
        AND a1.id = sfoLfoRels.from_id
        AND a2.id = sfoLfoRels.to_id
        AND c1.id != c2.id

  • deleted_cui: CUIs that are going away - will need bequeathal rel

    Code Block
    SELECT DISTINCT
    c.id conceptId
FROM
    concepts c,
    concepts_atoms ca,
    atoms a
WHERE
    c.terminology = :terminology
        AND c.id = ca.concepts_id
        AND ca.atoms_id = a.id
        AND a.suppressiblepublishable = TRUE
        AND NOT c.id IN (SELECT DISTINCT
            c.id conceptId
        FROM
            concepts c,
            concepts_atoms ca,
            atoms a
        WHERE
            c.terminology = :terminology
                AND c.id = ca.concepts_id
                AND ca.atoms_id = a.id
                AND a.suppressiblepublishable = FALSE)

     

     

  • deleted_cui_nomth - requested by Lori, not tested yet

    Code Block
    SELECT DISTINCT
    c.id conceptId
FROM
    concepts c,
    concepts_atoms ca,
    atoms a
WHERE
    c.terminology = :terminology
        AND c.id = ca.concepts_id
        AND ca.atoms_id = a.id
        AND a.suppressible = TRUE
        AND NOT c.id IN (SELECT DISTINCT
            c.id conceptId
        FROM
            concepts c,
            concepts_atoms ca,
            atoms a
        WHERE
            c.terminology = :terminology
                AND c.id = ca.concepts_id
                AND ca.atoms_id = a.id
                AND a.suppressible = FALSE)
        AND NOT c.id IN (
        SELECT c.id conceptId
        FROM
            concepts c,
            concepts_atoms ca,
            atoms a
        WHERE
            c.terminology=:terminology
                AND a.terminology='MTH'
                AND c.id = ca.concepts_id
                AND ca.atoms_id = a.id
                AND a.publishable = TRUE
        MINUS
        SELECT c.id conceptId
        FROM
            concepts c,
            concepts_atoms ca,
            atoms a
        WHERE
            c.terminology=:terminology
                AND a.terminology!='MTH'
                AND c.id = ca.concepts_id
                AND ca.atoms_id = a.id
                AND a.publishable = TRUE
                )

...