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

Welcome to the annual OHPUG Conference presented by the Central Library Consortium (CLC) and held at the State Library. We are glad to welcome everyone* in the Midwest who wants to learn more about Polaris and network with your peers during what promises to be another informative conference.