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
2

To tack onto this great query… in case someone is looking for a more generic “Find a URL in a 856 tag”:

select bt.BibliographicRecordID
from polaris.Polaris.BibliographicTags bt
join polaris.Polaris.BibliographicSubfields bs
        on bt.BibliographicTagID = bs.BibliographicTagID
where bt.TagNumber = 856 and bs.Data like '%ENTER-YOUR-URL.COM%'

This works so long as what you’re searching for is within ONE subfield (in most cases it is).

For example:

If the bib has multiple 856s that match the search pattern, then the Bib ID would be returned for each matching instance. These duplicate Bib IDs would get filtered out if you add the records to a record set.

PS Both this query and the original one are designed to work in the Bib Find Tool as a SQL search.

3

If you need to find records where all the 856 tags exclusively match a specific string, for example, when you need to run a bib bulk change and delete ALL those tags, then you can run the following:

select distinct bt.BibliographicRecordID
from polaris.Polaris.BibliographicTags bt
where bt.TagNumber = 856
    and exists (
        select 1
        from polaris.Polaris.BibliographicSubfields bs
        where bs.BibliographicTagID = bt.BibliographicTagID
          and bs.Data like '%ENTER-YOUR-URL.COM%'
    )
    and not exists (
        select 1
        from polaris.Polaris.BibliographicTags bt2
        where bt2.BibliographicRecordID = bt.BibliographicRecordID
          and bt2.TagNumber = 856
          and not exists (
              select 1
              from polaris.Polaris.BibliographicSubfields bs2
              where bs2.BibliographicTagID = bt2.BibliographicTagID
                and bs2.Data like '%ENTER-YOUR-URL.COM%'
          )
    )