Ad-hoc Query: NIH Closed Trials with No Special Category
--------------------------------------------------------

Old Version
===========
select distinct (q.doc_id) AS "CDR ID", q1.value AS "Current Protocol Status"
FROM query_Term q,
           query_term q1,
           docs_with_pub_status d,
           query_term s
WHERE q.doc_id=q1.doc_id
AND      q.doc_id=d.doc_id
AND s.doc_id = d.doc_id
AND s.path = '/InScopeProtocol/ProtocolSpecialCategory/SpecialCategory'
AND s.value = 'NIH Clinical Center trial'
AND   q.path LIKE  '/InScopeProtocol/ProtocolAdminInfo/ProtocolLeadOrg/ProtocolSites/OrgSite/OrgSiteID/@cdr:ref'
AND  q.int_val=32457
AND  q1.path LIKE '/InScopeProtocol/ProtocolAdminInfo/CurrentProtocolStatus'
AND q1.value IN  ('Closed', 'Temporarily closed')
AND d.pv='Y'
/* AND q.doc_id IN
(SELECT doc_id from query_Term
WHERE path like '/InScopeProtocol/ProtocolSpecialCategory/SpecialCategory'); */



New Version
===========
SELECT DISTINCT (q.doc_id) AS "CDR ID", 
                  q1.value AS "Current Protocol Status"
  FROM query_Term q
  JOIN query_term q1
    ON q.doc_id = q1.doc_id
   AND (q1.path = '/InScopeProtocol/ProtocolAdminInfo' +
                  '/CurrentProtocolStatus'
        OR
        q1.path = '/CTGovProtocol/OverallStatus'
       )
  JOIN docs_with_pub_status d
    ON q.doc_id = d.doc_id
  JOIN query_term s
    ON s.doc_id = d.doc_id
   AND (s.path = '/InScopeProtocol/ProtocolSpecialCategory' +
                 '/SpecialCategory'
        OR
        s.path = '/CTGovProtocol/PDQAdminInfo/ProtocolSpecialCategory' +
                 '/SpecialCategory'
       )
 WHERE (q.path = '/InScopeProtocol/ProtocolAdminInfo' +
                 '/ProtocolLeadOrg/ProtocolSites'     +
                 '/OrgSite/OrgSiteID/@cdr:ref'
        OR
        q.path = '/CTGovProtocol/Location' +
                 '/Facility/Name/@cdr:ref'
       )
   AND q.int_val=32457
   AND s.value = 'NIH Clinical Center trial'
   AND q1.value IN ('Closed', 'Temporarily closed')
   AND d.pv    = 'Y'