SQL Code Examples from the OHPUG 2014 Session – Holds in Polaris

Below is the SQL code for the troubleshooting sections presented during the Holds in Polaris Session at OHPUG 2014.

--Will display any hold requests in Polaris that are associated with BIB records that have no item records linked to them
select shr.SysHoldRequestID
from polaris.polaris.BibliographicISBNIndex bii
inner join polaris.polaris.SysHoldRequests shr on
 ( bii.BibliographicRecordID = shr.BibliographicRecordID )
inner join polaris.polaris.PatronRegistration pr on
 ( shr.PatronID = pr.PatronID )
inner join polaris.polaris.Patrons p on
 ( shr.PatronID = p.PatronID )
inner join polaris.polaris.Organizations o on
 ( p.OrganizationID = o.OrganizationID )
inner join polaris.polaris.Organizations parent on
 ( o.ParentOrganizationID = parent.OrganizationID ) 
where shr.SysHoldStatusID not in ( 8,9,16 )
and bii.BibliographicRecordID not in
 select cir.AssociatedBibRecordID from polaris.polaris.CircItemRecords cir
--Will display any hold requests in Polaris that have item records that Polaris has Denied. AKA Blue X of Death records
select distinct shr.SysHoldRequestID, shr.ActivationDate 
from polaris.polaris.SysHoldItemRecords shi 
join Polaris.Polaris.SysHoldRequests shr on shr.SysHoldRequestID = shi.SysHoldRequestID
left join 
select distinct on_order.AssociatedBibRecordID
from polaris.polaris.CircItemRecords on_order
left join
select * from polaris.polaris.CircItemRecords cir where ItemStatusID != 13
) not_on_order on on_order.AssociatedBibRecordID = not_on_order.AssociatedBibRecordID
where not_on_order.ItemRecordID is null and on_order.ItemStatusID = 13
) only_on_order on only_on_order.AssociatedBibRecordID = shr.BibliographicRecordID
where shi.ReasonID = -8 and shr.SysHoldStatusID in (3) and only_on_order.AssociatedBibRecordID is null