> -----Original Message-----
> From: [email protected]
> [mailto:[email protected]]
> On Behalf Of Geoff Brown
> Sent: December 20, 2011 14:06
> To: Evergreen Discussion Group
> Subject: [OPEN-ILS-GENERAL] Creating a Report Listing
> Authority Terms andAuthority Control Number
>
> Hi All,
>
> I am trying to generate a report that will allow us to create
> a list of all 650 and all 655 authority terms and their
> corresponding $0 (Authority Control Number). I am having an
> issue with the display because the list is so (obviously)
> huge. Is there a way to limit the display so duplicate terms
> will not display in this list?
...
> Or is there any other way to extract
> this information from the Authorities table?
Here is some SQL that might give you what your are looking for. I'm not sure
how you wanted the 650 information displayed. This query separates each
subfield by a ':'. I tested this with a limit of 10 records, and it worked.
I'm running it against our entire database and it is taking a long time. So,
I'll post this here for you to try.
This will give you every 650 in every bib record that has a $0.
SELECT id AS BibID, array_to_string(array_agg(Field_650_data), ':') AS
Field_650_data, AuthorityID FROM
(SELECT id, array_to_string(regexp_matches(Field_650, E'<subfield
code=.*?>(.*?)</subfield>', 'g'), '') AS Field_650_data, substring(Field_650
from E'[0-9]+$') AS AuthorityID
FROM (SELECT id, array_to_string(regexp_matches(bre.marc,
E'<datafield tag="650".*?>((?:(?!datafield).)*)<subfield
code="0">\\(.*?\\)(.*?)</subfield></datafield>', 'g'), '') AS Field_650
FROM biblio.record_entry as bre) AS list1
GROUP BY id, AuthorityID, Field_650)list2
GROUP BY id, AuthorityID
This should give you a list of unique 650s that have $0. I imagine the DISTINCT
will slow it down a lot more.
SELECT DISTINCT array_to_string(array_agg(Field_650_data), ':') AS
Field_650_data, AuthorityID FROM
(SELECT id, array_to_string(regexp_matches(Field_650, E'<subfield
code=.*?>(.*?)</subfield>', 'g'), '') AS Field_650_data, substring(Field_650
from E'[0-9]+$') AS AuthorityID
FROM (SELECT id, array_to_string(regexp_matches(bre.marc,
E'<datafield tag="650".*?>((?:(?!datafield).)*)<subfield
code="0">\\(.*?\\)(.*?)</subfield></datafield>', 'g'), '') AS Field_650
FROM biblio.record_entry as bre) AS list1
GROUP BY id, AuthorityID, Field_650)list2
GROUP BY AuthorityID
Here is the second query with a limit of 10 records, so you can see if the
output is what you need:
SELECT DISTINCT array_to_string(array_agg(Field_650_data), ':') AS
Field_650_data, AuthorityID FROM
(SELECT id, array_to_string(regexp_matches(Field_650, E'<subfield
code=.*?>(.*?)</subfield>', 'g'), '') AS Field_650_data, substring(Field_650
from E'[0-9]+$') AS AuthorityID
FROM (SELECT id, array_to_string(regexp_matches(bre.marc,
E'<datafield tag="650".*?>((?:(?!datafield).)*)<subfield
code="0">\\(.*?\\)(.*?)</subfield></datafield>', 'g'), '') AS Field_650
FROM biblio.record_entry as bre LIMIT 10) AS list1
GROUP BY id, AuthorityID, Field_650)list2
GROUP BY AuthorityID
Liam
Natural Resources Canada Library / Bibliothèque de Ressources naturelles Canada
615 rue Booth Street, 121, Ottawa, ON
Canada K1A 0E9
Telephone / Téléphone : 613-995-4180
[email protected]
Web: http://www.nrcan.gc.ca/library/
