Page Comparison

...

nci_merge: Merged SCUI current version NCI atoms

Code Block

select distinct ac.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

--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 Skip for now unpublishable NCIMTH concepts?

NON-required bins

rxnorm_merge: RXCUI Merges
Same as nci_merge but with RXNORM
Code Blockselect a
DISTINCT c.id
clusterId, a.id conceptId
conceptId FROM
from
concepts
a
c, concepts_atoms
b
ca, atoms a
c
WHERE
where

a
c.terminology =
:terminology
'NCIMTH' AND c.id != c.terminologyId
and
AND
a
c.id =
b
ca.concepts_id
and
AND
b
ca.atoms_id =
c
a.id AND a.publishable = FALSE AND
and
NOT c.
terminology='RXNORM'
id IN ( SELECT
group

by

a.id

having

count(distinct
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,
id conceptId FROM concepts c, concepts_atoms
b,
ca, atoms
c
a WHERE
where

a
c.terminology =
:terminology
'NCIMTH'
and
AND
a
c.id =
b
ca.concepts_id
and
AND
b
ca.atoms_id =
c
a.id
and
AND
c
a.
terminology='CBO'
publishable = TRUE )
group
AND
by
NOT
a
c.id IN (
having

count(distinct

c.conceptId)>1
mdr_merge: Merged SDUI current version MDR atoms
Code Block
SELECT c.id clusterId,DISTINCT c.id conceptId FROM concepts c, concepts concept_atomsrelationships ca,cr atomsWHERE a WHERE c.terminology = :terminology 'NCIMTH' AND c.id = cacr.conceptsfrom_id AND cacr.atoms_idrelationshipType = a.idlike 'B%' ) AND a.terminology = 'MDR' GROUP BY NOT c.id HAVINGIN COUNT(DISTINCT a.descriptorId) > 1
pdq_merge: Merged SDUI current version PDQ atoms
Code Block
SELECT c.id conceptId clusterId, c.id conceptId FROM concepts c, concepts_atoms ca, atomsWHERE a WHERE c.terminology = :terminology 'NCIMTH' AND c.id = ca.concepts_id AND ca.atomsconcepts_id =IN a.id( ANDSELECT 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 ca.concepts_id
      c.id conceptId FROM
    atoms a,     concepts_atoms a1ca,
        concepts c, atoms a
  concepts c1,     concepts_atomsWHERE
ca,     concepts_atoms ca1,    ca.atoms_id terminologies t,= a.id
    terminologies t1 WHERE    AND ca.terminology = :terminology IN ('MTH', 'NCIMTH')
          AND c1a.terminologytermType = :terminology'PN'
      )
    GROUP BY
   AND c.id = ca.concepts_id
    HAVING
   AND   COUNT(DISTINCT ca.atoms_id) = a.id1
  )
  AND NOT c.id ANDIN c1.id(
= ca1.concepts_id   SELECT
     AND ca1ca.atomsconcepts_id conceptId
 = a1.id  FROM
      AND a.terminology = 'NCI'mrcui mr,
      atomjpa_conceptterminologyids ac,
  AND a.terminology = t.terminology concepts_atoms ca,
      ANDconcepts a.versioncpt
= t.version   WHERE
     AND tmr.currentcui1 = TRUE
 ac.conceptTerminologyIds
      AND a1ca.terminologyatoms_id = 'PDQ'ac.AtomJpa_id
        AND a1cpt.terminologyid = t1ca.terminologyconcepts_id
        AND a1cpt.versionterminology = t1.version'NCIMTH'
        AND t1ac.currentconceptTerminologyIds_KEY = TRUE
'NCIMTH'
       AND cmr.idrel = c1.id

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

Code Block
SELECT DISTINCT 'DEL' )

NON-required bins

rxnorm_merge: RXCUI Merges
Same as nci_merge but with RXNORM
Code Block
select distinct c.id conceptId
FROM
from concepts c,

concepts_atoms
a
ca,

atoms
a1, concepts
a where c
,
.terminology = :terminology
concepts c1, concepts_atoms ca, concepts_atoms ca1, terminologies t, terminologies t1 WHERE c.terminology = :terminology AND c1
and c.id = ca.concepts_id and ca.atoms_id = a.id and a.publishable = true 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
and c.id = ca.concepts_id
AND ca
and ca.atoms_id = a.id and a.publishable = true and
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
a.terminology='CBO' 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 concepts c, concepts_atoms ca, atoms a where c.terminology = :terminology and c.id = c1ca.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 DISTINCTconcepts_id and ca.atoms_id = a.id and a.publishable = true
  and a.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 
        c1a1.id conceptId1atomId1, c2a2.id conceptId2atomId2
    FROM
        conceptsatoms c1a1, atoms a2
    WHERE
 concepts_atoms ca1,      a1.termType = 'PN'
atoms a1,         concepts c2, AND a2.termType = 'PN'
    concepts_atoms ca2,       AND a1.stringClassId atoms= a2.stringClassId
    WHERE        AND c1a1.terminologyid != 'NCIMTH'
    a2.id) identicalPNAtoms
WHERE
    c1.terminology = :terminology
        AND c2.terminology = 'NCIMTH'
:terminology
           AND c1.id = ca1.concepts_id
        AND c2.id = ca2.concepts_id
        AND ca1.atoms_id = a1.id
   identicalPNAtoms.atomId1
        AND ca2.atoms_id = identicalPNAtoms.atomId2
        AND c2c1.id != ca2c2.concepts_id
 id

multiple_pn: Concepts with multiple MTH/PN atoms

Code Block

SELECT DISTINCT
    c.id conceptId
FROM
    concepts  c,
   AND ca2.atoms_id =a,
a2.id    concepts_atoms ca
WHERE
      AND c1.id != c2.id
   c.terminology = :terminology
        AND a1c.lowerNameHashid = a2.lowerNameHash
   ca.concepts_id
        AND a1ca.atoms_id != a2a.id
        AND a.terminology in  AND a1.publishable = TRUE('MTH', 'NCIMTH')
        AND a.termType = 'PN'
GROUP BY c.id
ANDHAVING 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')
       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 ac.termTypeid = 'PN')ca.concepts_id
        AND NOT ambig_concepts.conceptId2ca.concepts_id IN (SELECT 
            ca.concepts_id
        FROM
            concepts_atoms ca,
            atoms a
        WHERE
            ca.atoms_id = a.id
                AND a.terminology IN ('NCIMTHMTH' , 'MTHNCIMTH')
                AND a.termType = 'PN')

ambig_no_rel: Ambiguous concepts that lack an approved REL

Code Block
--Use Pre-programmed View ambig_concepts SELECT AND a.publishable = true) GROUP BY ca.concepts_id HAVING COUNT(DISTINCT ca.atoms_id) = 1

nosty: No STY

Code Block

SELECT DISTINCT
    conceptId1, conceptId2c.id conceptId
FROM
    ambig_concepts WHERE
  c
 NOT (conceptId1 , conceptId2) IN (SELECT  LEFT JOIN
    concepts_semantic_type_components cs ON (c.id =  crcs.fromconcepts_id, cr.to_id)
WHERE
	c.terminology= :terminology
    AND c.publishable = TRUE
 FROM   AND cs.semanticTypes_id IS NULL

missing_sty: Reviewed concepts without releasable Semantic Types

Code Block

SELECT DISTINCT
    c.id conceptId
FROM
concept_Relationships cr   concepts c
WHERE
   WHERE c.terminology = :terminology
        AND crc.publishable = TRUE
            AND crc.workflowStatus inIN ('READY_FOR_PUBLICATION' , 'PUBLISHED'))

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

Code Block

SELECT
        AND NOT c1c.id conceptId1, c2.id conceptId2
FROMIN (SELECT 
     concepts c1,     concepts c2,c.id
    concepts_atoms ca1,   FROM
 concepts_atoms ca2,     (SELECT     concepts c,
   a1.id atomId1, a2.id atomId2     FROM concepts_semantic_type_components cs,
      atoms a1, atoms a2   semantic_type_components s
WHERE        WHERE
a1.termType = 'PN'          c.terminology = :terminology
AND a2.termType = 'PN'             AND a1c.stringClassIdid = a2.stringClassIdcs.concepts_id
                AND a1cs.semanticTypes_id != a2s.id)
identicalPNAtoms WHERE     c1.terminology = :terminology         AND c2s.terminologypublishable = :terminology
        AND c1.id = ca1.concepts_id
        AND c2TRUE)

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 = ca2b.conceptsProjectJpa_id
  and semanticTypeCategoryMap_KEY = 'chemical'
  ANDand ca1a.atoms_id = identicalPNAtoms.atomId1
        AND ca2.atoms_terminology = :terminology);
 
-- Rick update 3/2/2017
(select distinct semanticTypeCategoryMap_KEY sty
from projects a, ProjectJpa_semanticTypeCategoryMap b
where a.id = identicalPNAtomsb.atomId2ProjectJpa_id
  and semanticTypeCategoryMap = 'chem'
  ANDand c1a.idterminology != c2.id

multiple_pn: Concepts with multiple MTH/PN atoms
:terminology)

Code Block
SELECT DISTINCT c.id conceptId FROM concepts c, atoms aconcepts_semantic_type_components cs, conceptssemantic_type_atomscomponents cas, WHERE terminologies c.terminology = :terminology t WHERE AND c.idterminology = ca.concepts_id'CBO' AND cac.atoms_idterminology = at.idterminology AND ac.terminologyversion in ('MTH', 'NCIMTH')= t.version AND at.termTypecurrent = 'PN' GROUP BY TRUE AND c.id HAVING= COUNT(ccs.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 SELECTconcepts_id AND cs.semanticTypes_id = s.id AND s.semanticType IN ((SELECT DISTINCT semanticTypeCategoryMap_KEY sty c.id conceptId FROM concepts c, projects a, concepts_atoms ca WHEREProjectJpa_semanticTypeCategoryMap b WHERE c.terminology = :terminology AND ca.id = cab.conceptsProjectJpa_id AND ca.concepts_id IN (SELECT AND semanticTypeCategoryMap = 'chem' AND ca.concepts_ida.terminology = :terminology))

go_chem: Current version GO concepts with CHEM STYs

Code Block

SELECT DISTINCT
    c.id conceptId
FROM
 FROM   concepts c,
    concepts_semantic_type_components cs,
    conceptssemantic_type_atomscomponents cas,
    terminologies t
WHERE
    c.terminology atoms= a'GO'
        WHEREAND c.terminology = t.terminology
        AND cac.atoms_idversion = a.idt.version
        AND t.current = TRUE
        AND a.terminology IN ('MTH' , 'NCIMTH') c.id = cs.concepts_id
        AND cs.semanticTypes_id = s.id
        AND a.termType = 'PN'
  s.semanticType IN ((SELECT DISTINCT
            semanticTypeCategoryMap_KEY ANDsty
a.publishable = true) GROUP BY ca.concepts_id HAVING COUNT(DISTINCT ca.atoms_id) = 1

nosty: No STY

Code Block

SELECT DISTINCT FROM
       c.id conceptId FROM   projects a,
concepts c         LEFT JOIN ProjectJpa_semanticTypeCategoryMap b
  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   a.id = cb.ProjectJpa_id
conceptId FROM     concepts c WHERE     c.terminology = :terminology AND semanticTypeCategoryMap = 'chem'
    AND c.publishable = TRUE         AND ca.workflowStatusterminology IN ('READY_FOR_PUBLICATION' , 'PUBLISHED')
    = :terminology))

mdr_chem: Current version MDR concepts with CHEM STYs

Code Block

SELECT DISTINCT
    c.id conceptId
FROM
   AND NOTconcepts c.id,
IN (SELECT   concepts_semantic_type_components cs,
    semantic_type_components s,
    c.idterminologies t
WHERE
    c.terminology = FROM'MDR'
        AND c.terminology = t.terminology
concepts c,       AND c.version = t.version
  concepts_semantic_type_components cs,     AND t.current = TRUE
    semantic_type_components s   AND c.id = cs.concepts_id
  WHERE      AND cs.semanticTypes_id = s.id
   c.terminology = :terminology   AND s.semanticType IN ((SELECT DISTINCT
         AND c.id = cs.concepts_idsemanticTypeCategoryMap_KEY sty
        FROM
       AND cs.semanticTypes_id = s.id  projects a,
            ProjectJpa_semanticTypeCategoryMap ANDb
 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      WHERE
            a.id = b.ProjectJpa_id
  and semanticTypeCategoryMap_KEY = 'chemical'   and a.terminology = :terminology);   -- Rick update 3/2/2017 (select distinctAND semanticTypeCategoryMap_KEY sty= from'chem'
projects a, ProjectJpa_semanticTypeCategoryMap b where a.id = b.ProjectJpa_id   and semanticTypeCategoryMap = 'chem'   andAND 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 c.terminology = concepts:terminolgy
AND c,.publishable=TRUE
AND NOT EXISTS
 concepts_semantic_type_components cs,  (SELECT * FROM semanticdeep_typeconcept_components s,relationships dcr
     WHERE terminologies t
WHERErole='FROM' AND
     c.terminology = 'GO'
    id = 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.terminologyid =conceptId
t.terminologyFROM
    concepts    AND c.version = t.versionc,
    atoms a,
    AND t.current = TRUE
    concepts_atoms ca
WHERE
   AND c.idterminology = cs.concepts_id:terminology
        AND csc.semanticTypes_id = s.id
        AND s.semanticType IN ((SELECT DISTINCT
ca.concepts_id
           semanticTypeCategoryMap_KEY sty
AND ca.atoms_id = a.id
       FROM AND a.terminology in ('NCI')
        projectsAND a,.termType = 'PT'
GROUP BY c.id
HAVING COUNT(c.id) > 1

mxsuppr: Concepts with identical LUI atoms that have mixed suppressibility

Code Block

 SELECT DISTINCT
  ProjectJpa_semanticTypeCategoryMap b c.id conceptId
FROM
    concepts WHEREc,
    concepts_atoms ca1,
      a.id = b.ProjectJpaconcepts_idatoms ca2,
    (SELECT 
        a1.id AND semanticTypeCategoryMap = 'chem'atomId1, a2.id atomId2
    FROM
        atoms a1, atoms  ANDa2
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 WHERE c.id conceptId FROM a1.lexicalClassId = conceptsa2.lexicalClassId c WHERE c.terminology = :terminology AND a1.id != a2.id AND NOT c.id IN (SELECT AND a1.publishable = TRUE c.id FROMAND a2.publishable = TRUE concepts c, AND a1.suppressible != a2.suppressible) mixedSuppresLuiAtoms WHERE concept_relationships crc.terminology = :terminology WHEREAND c.id = ca1.concepts_id AND crca1.from_id = c.idatoms_id = mixedSuppresLuiAtoms.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 19	New Version Current
Changes made by	Rick Wood	Deborah Shapiro
Saved on	Mar 03, 2017	Mar 18, 2019

Versions Compared

Key

NON-required bins

NON-required bins