The attached screenshot below shows an advanced query for disabled specimens, which returned zero results. When querying the database directly, we do find records of disabled specimens. We looked at the corresponding SQL for the disabled specimens query represented by the screenshot, which can be found below:
select count(distinct t1.PARTICIPANT_ID) as c0
, count(distinct t0.IDENTIFIER) as c1
from CPR_VIEW t1
left join CATISSUE_SPECIMEN_COLL_GROUP t2
on t2.COLLECTION_PROTOCOL_REG_ID = t1.CPR_ID
left join CATISSUE_SPECIMEN t0
on t0.SPECIMEN_COLLECTION_GROUP_ID = t2.IDENTIFIER
where (upper(t0.ACTIVITY_STATUS) = ‘DISABLED’)
AND (t1.ACTIVITY_STATUS != ‘Disabled’
AND t0.ACTIVITY_STATUS != ‘Disabled’)
Based on the SQL, is appears OpenSpecimen by default is querying for disabled and not disabled specimen activity status, which would result in a count of zero. Is this a bug in the advanced query?
