Versions Compared

Version Old Version 17 New Version 18
Changes made by

Rick Wood

Rick Wood

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 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='NCI'
group by a.id having count(distinct c.conceptId)>1

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

    Code Block
    TBD: 
select distinct 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 in (select terminology from root_terminologies where family = 'NCI' and terminology != 'NCI')
group by a.id having count(distinct c.conceptId)>1

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

    Code Block
    -- ATOM Terminology = SNOMEDCT_US?  Because only project-terminology concepts are visible through UI...
 
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--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 c1ca1.terminologyatoms_id = :terminologya1.id
        AND a.terminology = 'SNOMEDCT_USNCI'
        AND a1.terminology = 'SNOMEDCT_US'IN (SELECT 
        AND c.id = ca.concepts_id terminology
       AND ca.atoms_idFROM
= a.id         AND c1.id = ca1.concepts_id root_terminologies
        ANDWHERE
ca1.atoms_id = a1.id         AND a.termTypefamily = 'FNNCI'         AND a1.termTypeterminology != 'PTNCI')
        AND a.conceptId = a1.conceptId
        AND c.id != c1.id;

  • cdstysct_coc: Find concepts with Clinical Drug STY and any other STYsepfnpt: SNOMED concept clusters where the FN and PT terms are separated

    Code Block
    SELECT DISTINCT
    c.id conceptId1, c1.id conceptIdconceptId2
FROM
    concepts c,
    concepts_semantic_type_components cs
WHEREatoms ca,
    atoms c.terminologya,
= :terminology   concepts c1,
    AND c.id = cs.concepts_idconcepts_atoms ca1,
    atoms a1
WHERE
   AND cs.concepts_id IN (SELECTc.terminology = :terminology
        AND c1.terminology = :terminology
  cs.concepts_id      AND a.terminology = FROM'SNOMEDCT_US'
        AND a1.terminology = 'SNOMEDCT_US'
concepts c,       AND c.id =    ca.concepts_semantic_type_components cs,id
        AND ca.atoms_id = a.id
 semantic_type_components s      AND c1.id = WHEREca1.concepts_id
         AND   c.terminologyca1.atoms_id = :terminologya1.id
        AND a.termType = 'FN'
    AND c.id = cs.concepts_id AND a1.termType = 'PT'
            AND csa.semanticTypes_idconceptId = sa1.idconceptId
                AND sc.semanticTypeid = 'Clinical Drug')
GROUP BY cs.concepts_id
HAVING COUNT(cs.concepts_id) > 1
    multsty:concepts with more than 3 STYs|
    != c1.id

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

    Code Block
    SELECT DISTINCT
    csc.concepts_id conceptId
FROM
	    concepts c,
    concepts_semantic_type_components cs,
WHERE
   semantic_type_components s
WHERE
	c.terminology = :terminology
	
        AND c.id = cs.concepts_id
     AND   AND cs.semanticTypesconcepts_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
    SemanticType relationships? 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 of what object?

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

    Code Block
     
unpublishable NCIMTH concepts?

...