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