Page Comparison

...

nci_merge: Merged SCUI current version NCI atoms

Code Block

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

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

Code Block

TBD: 
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 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

-- What put as clusterId/conceptId?
 
SELECT DISTINCT--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.id conceptId,terminology = :terminology
    a.id,    AND a.termType,c1.terminology = :terminology
    a.conceptId,    AND c1c.id conceptId1,
= ca.concepts_id
   a1.id,     a1.termType,
    a1.conceptId
FROMAND ca.atoms_id = a.id
     concepts c,  AND c1.id = ca1.concepts_atomsid
ca,     atoms a,     concepts c1,
    concepts_atoms ca1,
AND ca1.atoms_id = a1.id
   atoms a1 WHERE   AND  ca.terminology = 'SNOMEDCT_USNCI'
        AND c1a1.terminology = 'SNOMEDCT_US'IN (SELECT 
       AND c.id = ca.concepts_id  terminology
      AND ca.atoms_id =FROM
a.id         AND c1.id = ca1.conceptsroot_idterminologies
        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 conceptId conceptId1, c1.id conceptId2
FROM
    concepts c,
    concepts_semantic_type_components cs
WHEREatoms ca,
    atoms c.terminologya,
= :terminology   concepts c1,
    AND c.id = cs.concepts_idatoms ca1,
    atoms a1
WHERE
AND cs.concepts_id IN (SELECT c.terminology = :terminology
        AND  cs.concepts_id
 c1.terminology = :terminology
      FROM  AND a.terminology =  'SNOMEDCT_US'
      concepts c, AND a1.terminology = 'SNOMEDCT_US'
        concepts_semantic_type_components cs,
 AND c.id = ca.concepts_id
        AND  semantic_type_components sca.atoms_id = a.id
        WHEREAND c1.id = ca1.concepts_id
        AND cca1.terminologyatoms_id = :terminologya1.id
        AND a.termType = 'FN'
     AND c.id  AND a1.termType = cs.concepts_id'PT'
        AND a.conceptId = a1.conceptId
        AND csc.semanticTypes_id != sc1.id

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

Code Block

SELECT DISTINCT
    c.id conceptId
FROM
    concepts c,
   AND s.semanticType = 'Clinical Drug')
GROUP BY cs.concepts_id
HAVING COUNT( concepts_semantic_type_components cs
WHERE
    c.terminology = :terminology
        AND c.id = cs.concepts_id)
  > 1

multsty:concepts with more than 3 STYs|

Code Block

SELECT DISTINCT    AND cs.concepts_id conceptIdIN (SELECT FROM
	concepts c,     concepts_semantic_type_components cs,     semantic_type_components s
WHERE
	c.terminology = :terminology
	AND c.id = cs.concepts_idcs.concepts_id
        FROM
       AND cs.semanticTypes_id = s.id GROUP BYconcepts c.id,
HAVING COUNT(c.id) > 3

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

Code Block
SemanticType relationships?

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?

NON-required bins

rxnorm_merge: RXCUI Merges
Same as nci_merge but with RXNORM
Code Blockselect 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
concepts_semantic_type_components cs, semantic_type_components s WHERE
and
c.terminology =
'RXNORM'
:terminology
group
by
a.id
having
count(distinct
c.conceptId)>1cbo_merge: Merged SCUI current version CBO atoms
Same as nci_merge but with CBO
Code Blockselect
a.id
clusterId,
a.id
conceptId
from
concepts
a,
concepts_atoms b, atoms c where a.terminology = :terminology
AND c.id = cs.concepts_id
and
a.id
=
b.concepts_id
and
AND
b
cs.
atoms
semanticTypes_id =
c
s.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
SELECT AND s.semanticType = c.id clusterId, c.id conceptId FROM '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_atoms casemantic_type_components cs,
    atoms asemantic_type_components s
WHERE
    	c.terminology = :terminology
        	AND c.id = cacs.concepts_id
        AND ca.atomsAND cs.semanticTypes_id = as.id
   
    AND a.terminology = 'MDR'
GROUP BY c.id
HAVING COUNT(DISTINCT ac.descriptorIdid) > 13

pdq_merge: Merged SDUI current version PDQ atomsstyisa:One STY is an ancestor of another in the STY isa hierarchy

Code Block

SELECTselect t1.conceptId from 
(select  c.id clusterIdconceptId, cst.id conceptId
FROM
    styId, st.treeNumber from concepts c, concepts_semantic_type_components cstc, (select  concepts_atoms ca,
    atoms a
WHERE
   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 = cacstc.concepts_id
      
 AND cacstc.atomssemanticTypes_id = ast.id) t1
JOIN 
(select c.id conceptId, st.id  AND a.terminology = '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 PDQ 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_idstyId, 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 
       AND ca1ar.atomsfrom_id, = a1ar.to_id
    FROM
   AND  a.terminology = 'NCI' atom_relationships ar
    WHERE
 AND a.terminology = t.terminology    publishable = TRUE
  AND a.version = t.version       AND relationshipType = AND t.current = TRUE'SY'
            AND a1.terminology(additionalRelationshipType = 'PDQexpanded_form_of'
        AND a1.terminology = t1.terminology OR additionalRelationshipType LIKE 'mth_%_form_of')) sfoLfoRels
WHERE
  AND  a1c1.versionterminology = t1.version'NCIMTH'
        AND t1c2.currentterminology = TRUE'NCIMTH'
        AND cc1.id = c1ca1.id

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

Code Block

SELECT DISTINCTconcepts_id
        AND cca1.atoms_id conceptId= FROMa1.id
    atoms a,   AND  atoms a1,c2.id = ca2.concepts_id
     concepts c,  AND ca2.atoms_id = conceptsa2.id
c1,     concepts_atoms ca,  AND a1.id = conceptssfoLfoRels.from_atomsid
ca1,     terminologies t,     terminologies t1
WHERE
    c.terminologyAND a2.id = :terminologysfoLfoRels.to_id
        AND c1.terminologyid != :terminologyc2.id

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

Code Block

SELECT
  DISTINCT c.id conceptId
FROM
  ANDconcepts c.id,
 = ca.concepts_idatoms ca,
  atoms a
WHERE
  AND ca.atoms_idc.terminology = a.id'NCIMTH'
  AND c.id != c.terminologyId
  AND c1c.id = ca1ca.concepts_id
   
    AND ca1ca.atoms_id = a1a.id
  AND a.publishable = FALSE
  AND a.terminology = 'NCI'NOT c.id IN (
    SELECT
    AND a.terminology =DISTINCT tc.terminologyid conceptId
    FROM
   AND a.version = t.versionconcepts c,
      concepts_atoms ANDca,
t.current = TRUE    atoms a
   AND a1.terminologyWHERE
= 'SNOMEDCT_US'
       c.terminology = 'NCIMTH'
      AND a1c.terminologyid = t1ca.terminologyconcepts_id
        AND a1ca.versionatoms_id = t1.version
   a.id
      AND a.publishable = TRUE
  )
  AND t1.current = TRUE
 NOT c.id IN (
    SELECT
      ANDDISTINCT c.id = c1.id

ambig_no_pn: Ambiguous concepts where at least one has no MTH/PN nor NCIMTH/PN (sepstring)

Code Block

--Ambiguous concept = concepts that share atoms that have the same case-insensitive name
-- This is used by several of the below queries, so it should be created as a VIEW
 
CREATE VIEW ambig_concepts AS
    SELECT DISTINCT
        c1.id conceptId1, c2.id conceptId2
    FROM
        concepts c1,
        concepts_atoms ca1,
        atoms a1,
 conceptId
    FROM
      concepts c,
      concept_relationships cr
    WHERE
      c.terminology = 'NCIMTH'
      AND c.id = cr.from_id
      AND cr.relationshipType like 'B%'
  )
  AND NOT c.id IN (
    SELECT
      c.id conceptId
    FROM
      concepts c2c,
 
      concepts_atoms ca2,
ca
       atoms a2
    WHERE
        c1c.terminology = 'NCIMTH'
            AND c2.terminologyAND c.id = 'NCIMTH'ca.concepts_id
      AND ca.concepts_id IN (
  AND c1.id = ca1.concepts_id   SELECT
         AND ca1ca.atomsconcepts_id
=   a1.id     FROM
       AND c2.id = ca2.concepts_idatoms ca,
          atoms a
  AND ca2.atoms_id = a2.id   WHERE
         AND c1ca.atoms_id != c2a.id
          AND  AND a1.lowerNameHash = a2.lowerNameHash
a.terminology IN ('MTH', 'NCIMTH')
           AND a1a.idtermType != a2.id'PN'
      )
    GROUP ANDBY
a1.publishable = TRUE    ca.concepts_id
    HAVING
   AND a2.publishable = TRUE

Code Block

--Use Pre-programmed View ambig_concepts
 
SELECT 
    conceptId1, conceptId2
FROM
    ambig_concepts
WHERE
    NOT ambig_concepts.conceptId1 COUNT(DISTINCT ca.atoms_id) = 1
  )
  AND NOT c.id IN (SELECT
    SELECT
        ca.concepts_id conceptId
    FROM
  FROM    mrcui mr,
       conceptsatomjpa_atomsconceptterminologyids caac,
      concepts_atoms ca,
    atoms  concepts acpt
    WHERE
   WHERE   mr.cui1 = ac.conceptTerminologyIds
      AND ca.atoms_id = aac.AtomJpa_id
      AND cpt.id = ca.concepts_id
      AND acpt.terminology IN= ('NCIMTH'
, 'MTH')     AND ac.conceptTerminologyIds_KEY =  'NCIMTH'
       AND amr.termTyperel = 'PNDEL')
  )

NON-required bins

rxnorm_merge: RXCUI Merges
Same as nci_merge but with RXNORM
Code Block
select distinct c.id conceptId from
AND
concepts
NOT
c,
ambig
concepts_
concepts.conceptId2 IN (SELECT
atoms ca, atoms a where c.terminology = :terminology and c.id = ca.concepts_id

and ca.
concepts
atoms_id = a.id and a.publishable = true
FROM
and a.terminology='RXNORM' group by a.conceptId having count(distinct c.id)>1

cbo_merge: Merged SCUI current version CBO atoms
Same as nci_merge but with CBO
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
atoms a WHERE
and ca.atoms_id = a.id and a.publishable = true
AND
and a.terminology
IN ('NCIMTH
='CBO'
, 'MTH') AND a.termType = 'PN')

ambig_no_rel: Ambiguous concepts that lack an approved REL

Code Block

--Use Pre-programmed View ambig_concepts

SELECT 
    conceptId1, conceptId2
FROM
    ambig_concepts
WHERE
    NOT (conceptId1 , conceptId2) IN (SELECT 
            cr.from_id, cr.to_id
        FROM
            concept_Relationships cr
        WHERE
            cr.publishable = TRUE
            AND cr.workflowStatus in ('READY_FOR_PUBLICATION','PUBLISHED'))

pn_pn_ambig: Identical (same SUI) PN's in multiple concepts
multiple_pn: Concepts with multiple MTH/PN atoms
Code BlockSELECT DISTINCT
group by c.id having count(distinct a.conceptId)>1

mdr_merge: Merged SDUI current version MDR 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='MDR'
group by c.id having count(distinct a.descriptorId)>1

pdq_merge: Merged SDUI current version PDQ 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='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

Code Block

select distinct c.id conceptId FROM
from    concepts c,     atoms a,
    concepts_atoms ca, WHEREatoms a
where   c.terminology = :terminology
  and      AND c.id = ca.concepts_id         AND and ca.atoms_id = a.id and a.publishable = true
    ANDand a.terminology in ('MTH', 'NCIMTH')
        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

Code Block
--Concepts whose only publishable atoms are MTH/PN or NCIMTH/PN

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
SELECT DISTINCT c.id conceptId FROM concepts c WHERE c.terminology = :terminology='RXNORM' group by a.conceptId having count(distinct c.id)>1

nci_pdq_merge: Concepts containing current version NCI and current version PDQ 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 = 'PDQ'
        AND 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_pn: Ambiguous concepts where at least one has no MTH/PN nor NCIMTH/PN (sepstring)

Code Block

--Ambiguous concept = concepts that share atoms that have the same case-insensitive name
-- This is used by several of the below queries, so it should be created as a VIEW
 
CREATE VIEW ambig_concepts AS
    SELECT DISTINCT
        c1.id conceptId1, c2.id conceptId2
    FROM
        concepts c1,
        concepts_atoms ca1,
        atoms a1,
        concepts c2,
        concepts_atoms ca2,
        atoms a2
    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 c1.id < c2.id
            AND a1.lowerNameHash = a2.lowerNameHash
            AND a1.id != a2.id
            AND a1.publishable = TRUE
            AND a2.publishable = TRUE

Code Block

--Use Pre-programmed View ambig_concepts
 
SELECT 
    conceptId1, conceptId2
FROM
    ambig_concepts
WHERE
    NOT ambig_concepts.conceptId1 IN (SELECT 
            ca.concepts_id
        FROM
            concepts_atoms ca,
            atoms a
        WHERE
            ca.atoms_id = a.id
                AND a.terminology IN ('NCIMTH' , 'MTH')
                AND a.termType = 'PN')
        AND NOT ambig_concepts.conceptId2 IN (SELECT 
            ca.concepts_id
        FROM
            concepts_atoms ca,
            atoms a
        WHERE
            ca.atoms_id = a.id
                AND a.terminology IN ('NCIMTH' , 'MTH')
                AND a.termType = 'PN')

ambig_no_rel: Ambiguous concepts that lack an approved REL

Code Block

--Use Pre-programmed View ambig_concepts

SELECT 
    conceptId1, conceptId2
FROM
    ambig_concepts
WHERE
    NOT (conceptId1 , conceptId2) IN (SELECT 
            cr.from_id, cr.to_id
        FROM
            concept_Relationships cr
        WHERE
            cr.publishable = TRUE
            AND cr.workflowStatus in ('READY_FOR_PUBLICATION','PUBLISHED'))

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

Code Block

SELECT 
    c1.id conceptId1, c2.id conceptId2
FROM
    concepts c1,
    concepts c2,
    concepts_atoms ca1,
    concepts_atoms ca2,
    (SELECT 
        a1.id atomId1, a2.id atomId2
    FROM
        atoms a1, atoms a2
    WHERE
        a1.termType = 'PN'
            AND a2.termType = 'PN'
            AND a1.stringClassId = a2.stringClassId
            AND a1.id != a2.id) identicalPNAtoms
WHERE
    c1.terminology = :terminology
        AND c2.terminology = :terminology
        AND c1.id = ca1.concepts_id
        AND c2.id = ca2.concepts_id
        AND ca1.atoms_id = identicalPNAtoms.atomId1
        AND ca2.atoms_id = identicalPNAtoms.atomId2
        AND c1.id != c2.id

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 in ('MTH', 'NCIMTH')
        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

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 in ('NCIMTH','MTH')
  and a.termType = 'PN'
  and not c.id in (select conceptId1 from ambig_concepts)

ambig_pn: MTH/PN atom is ambiguous but has no matching ambiguous string
Drop. No longer useful.

pn_orphan: MTH/PNs on their own

Code Block

--Concepts whose only publishable atoms are MTH/PN or NCIMTH/PN
 
SELECT 
    c.id conceptId
FROM
    concepts c,
    concepts_atoms ca
WHERE
    c.terminology = :terminology
        AND c.id = ca.concepts_id
        AND ca.concepts_id IN (SELECT 
            ca.concepts_id
        FROM
            concepts_atoms ca,
            atoms a
        WHERE
            ca.atoms_id = a.id
                AND a.terminology IN ('MTH' , 'NCIMTH')
                AND a.termType = 'PN'
                AND a.publishable = true)
GROUP BY ca.concepts_id
HAVING COUNT(DISTINCT ca.atoms_id) = 1

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 c.publishable = TRUE
    AND cs.semanticTypes_id IS NULL

missing_sty: Reviewed concepts without releasable Semantic Types

Code Block

SELECT DISTINCT
    c.id conceptId
FROM
    concepts c
WHERE
    c.terminology = :terminology
        AND c.publishable = TRUE
        AND c.workflowStatus IN ('READY_FOR_PUBLICATION' , 'PUBLISHED')
        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)

Code Block

SELECT DISTINCT
    c.id conceptId
FROM
    concepts c,
    concepts_semantic_type_components cs,
    semantic_type_components s,
    terminologies t
WHERE
    c.terminology = 'CBO'
        AND c.terminology = t.terminology
        AND c.version = t.version
        AND t.current = TRUE
        AND c.id = cs.concepts_id
        AND cs.semanticTypes_id = s.id
        AND s.semanticType IN ((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,
    terminologies t
WHERE
    c.terminology = 'GO'
        AND c.terminology = t.terminology
        AND c.version = t.version
        AND t.current = TRUE
        AND c.id = cs.concepts_id
        AND cs.semanticTypes_id = s.id
        AND s.semanticType IN ((SELECT DISTINCT
            semanticTypeCategoryMap_KEY sty
        FROM
            projects a,
            ProjectJpa_semanticTypeCategoryMap b
        ANDWHERE
c.publishable = TRUE         AND ca.workflowStatusid IN ('READY_FOR_PUBLICATION' , 'PUBLISHED')= b.ProjectJpa_id
          AND NOT c.id IN (SELECT  AND semanticTypeCategoryMap = 'chem'
         c.id       AND  FROM
a.terminology = :terminology))

mdr_chem: Current version MDR concepts with CHEM STYs

Code Block

SELECT DISTINCT
    c.id conceptId
FROM
    concepts c,
 
    concepts_semantic_type_components cs,
    concepts_semantic_type_components css,
    terminologies t
WHERE
     semantic_type_components sc.terminology = 'MDR'
        WHEREAND c.terminology = t.terminology
        AND c.terminologyversion = :terminologyt.version
        AND t.current = TRUE
        AND c.id = cs.concepts_id
        AND cs.semanticTypes_id = s.id
        AND cs.semanticTypes_id = s.ids.semanticType IN ((SELECT DISTINCT
            semanticTypeCategoryMap_KEY sty
      AND  s.publishableFROM
= 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 ProjectJpa_semanticTypeCategoryMap_KEY =b
'chemical'   and a.terminology = :terminology);   -- RickWHERE
update 3/2/2017 (select distinct semanticTypeCategoryMap_KEY sty from projects a, ProjectJpa_semanticTypeCategoryMap b where a.id = b.ProjectJpa_id
   and             AND semanticTypeCategoryMap = 'chem'
  and              AND a.terminology = :terminology)

go_chem: Current version GO concepts with CHEM STYs
)

true_orphan: Concepts with no releasable relationships to any other concept
Uses the "deep relationships" query; only needs to search FROM role since rels are bidirectional

Code Block

SELECT DISTINCT
    c.id conceptId
FROM concepts c
WHERE  concepts c,
    concepts_semantic_type_components cs,
    semantic_type_components s,c.terminology = :terminolgy
AND c.publishable=TRUE
AND NOT EXISTS
    (SELECT * FROM deep_concept_relationships dcr
    terminologies t
WHERE     c.terminology = 'GO'
   role='FROM' AND
    AND c.terminologyid = t.terminology
 dcr.concepts_id)

deleted_cui_split: Complex split/merge case CUIs that are going away - will need bequeathal
Drop
split_demotions: Demotions overlapping with a bad merge that was split
Drop. No longer useful.

nci_pt_mrg: Merged current version NCI/PT atoms

Code Block

SELECT DISTINCT
      AND c.versionid =conceptId
t.versionFROM
    concepts c,
  AND t.current =atoms TRUEa,
    concepts_atoms ca
WHERE
   AND c.idterminology = cs.concepts_id:terminology
        AND csc.semanticTypes_id = sca.concepts_id
        AND s.semanticType IN ((SELECT DISTINCT
            semanticTypeCategoryMap_KEY sty
        FROM
 ca.atoms_id = a.id
        AND a.terminology projects a,in ('NCI')
        AND a.termType =  ProjectJpa_semanticTypeCategoryMap b
        WHERE
   'PT'
GROUP BY c.id
HAVING COUNT(c.id) > 1

mxsuppr: Concepts with identical LUI atoms that have mixed suppressibility

Code Block

 SELECT DISTINCT
    c.id conceptId
FROM
    concepts c,
  a.id = b.ProjectJpaconcepts_idatoms ca1,
     concepts_atoms ca2,
    (SELECT 
  AND semanticTypeCategoryMap = 'chem'   a1.id atomId1, a2.id atomId2
    FROM
     AND a.terminology = :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 atoms a1, atoms a2 WHERE concepts c WHERE a1.lexicalClassId = c.terminology = :terminologya2.lexicalClassId AND NOT ca1.id IN (SELECT!= a2.id AND ca1.idpublishable = TRUE FROM AND a2.publishable = TRUE concepts c, AND a1.suppressible != concept_relationships cra2.suppressible) mixedSuppresLuiAtoms WHERE c.terminology = :terminology WHEREAND c.id = ca1.concepts_id AND crca1.fromatoms_id = c.idmixedSuppresLuiAtoms.atomId1 AND ca2.atoms_id = mixedSuppresLuiAtoms.atomId2 AND crca1.publishableconcepts_id = 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

...

ca2.concepts_id

~ Mixed status concepts 196 Sat Jan 7 09:19:47 2017
~ Suppressible preferred name (level 0,9) 0 Sat Jan 7 09:19:58 2017

Version	Old Version 16	New Version Current
Changes made by	Rick Wood	Deborah Shapiro
Saved on	Mar 02, 2017	Mar 18, 2019

Versions Compared

Key

NON-required bins

NON-required bins