SQL Query to Find MARC Subfields That Are Out of Order

Polaris
1

I’m looking for a way to find bib records that have subfields in the wrong order. Specifically, ‡e is at the beginning of the 100 field instead of at the end (ex: 100 1 ‡eauthor.‡aWilkinson, Toby,‡d1969-)

Is there a find tool sql query that can show me the records that are like this? Thanks!

2 Likes
2

Hi, Kristen:

While I don’t have a query that will find this, I’m glad to see that my library is not the only library that is dealing with this issue! Do you know what is causing the $e to get moved to the front of the 100 field for your library? We just noticed this happening a few months ago, and the only explanation that I can find for this problem is that we are importing new/updated authority records which, when connected to the bib records that use the new/updated headings, causes the 100 in the bib record to list the subheadings out of place. Is it possible that this is what is causing this to happen to you as well? If so, I will note that we are not the only library where this is happening with our importing of authority records when I report this to Innovative.

3

We have no idea why it’s happening. I don’t think it’s due to updating authority records, but I can’t be sure since I can’t easily find more of them to see if there’s a pattern, thus the whole problem! :laughing:

4

What version of Polaris are you running?

We had this problem with earlier versions, but I don’t think we’ve had it recently and we process the weekly ZMARC authority updates.

5

We’re still on version 7.2

6

Ok, I found our ticket for this #732079 Importing authorities puts subfields out of order. I can’t tell for sure if it ever actually got fixed or if we might have just given up on it. You might want to open a support ticket and reference our ticket number in case that provides any additional “backstory” to your support representative.

At the time we seemed to see it happen mostly with authorities linked to eBooks and mostly with names that have a date term.

As for SQL to find these. That is going to be difficult, I think. I fairly sure the table is: [Polaris].[Polaris].[BibliographicSubfields] but then things get weird from there. I’ll see if any of our SQL gurus want to take a crack at it.

7

Props to @mhammermeister for the following SQL that you should be able to use in your bib find tool to help find these:

Select BR.BibliographicRecordID
From polaris.Polaris.BibliographicRecords BR (nolock)
JOIN polaris.Polaris.BibliographicTags BT (nolock)
    ON BR.BibliographicRecordID = BT.BibliographicRecordID
    AND BT.TagNumber = 100
JOIN polaris.Polaris.BibliographicSubfields BSa (nolock)
    ON BT.BibliographicTagID = BSa.BibliographicTagID
    AND BSa.Subfield = 'a'
JOIN polaris.Polaris.BibliographicSubfields BSe (nolock)
    ON BT.BibliographicTagID = BSe.BibliographicTagID
    AND BSe.Subfield = 'e'
WHERE BSa.SubfieldSequence > BSe.SubfieldSequence
3 Likes
8

Perfect, thank you!

It looks like most, but not all, of the titles with problems have authors with date terms. Those that don’t, though, have another subfield as well, though, like $c or $q.

I’ll put in a support ticket, but at least it’s not a whole lot of items–only 320 out of a collection of a whooole lot more than that.

2 Likes
9

Thanks for sharing, Wes and Matt!

Kirsten: You may also want to update the SQL query to change the 100 field to 700 and run that as well and see what comes up. I ran it on our database here and found over 300 added name headings that need to be updated as well!

2 Likes
10

Oooh, good call. We’ve got about 300 of those too.

1 Like