Hoopla ID mismatches: Find where 037$a and ID in hoopla URLs don't match

Clearinghouse Repository Polaris Repository
,
1

This query find bibs where the 037$a tag does not match the hoopla id in the 856 tag URL. An incorrect 037 can cause hoopla titles to be unable to be checked out in Vega.

SELECT DISTINCT BT.BibliographicRecordID AS recordid
FROM BibliographicTags BT WITH (NOLOCK)
JOIN BibliographicSubfields BS WITH (NOLOCK)
    ON BS.BibliographicTagID = BT.BibliographicTagID
JOIN BibliographicTags BT2 WITH (NOLOCK)
    ON BT2.BibliographicRecordID = BT.BibliographicRecordID
JOIN BibliographicSubfields BS2 WITH (NOLOCK)
    ON BS2.BibliographicTagID = BT2.BibliographicTagID
WHERE BT.TagNumber = 856
  AND BT.INDICATOROne = '4'
  AND BT.INDICATORTwo = '0'
  AND BS.Subfield = 'u'
  AND BS.Data LIKE 'https://www.hoopladigital.com/title/%'
  AND BT2.TagNumber = 037
  AND BS2.Subfield = 'a'
  AND BS.Data NOT LIKE 'https://www.hoopladigital.com/title/' + BS2.Data + '%'
1 Like