> -----Original Message-----
> From: open-ils-general-boun...@list.georgialibraries.org 
> [mailto:open-ils-general-boun...@list.georgialibraries.org] 
> 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
lwha...@nrcan-rncan.gc.ca
Web: http://www.nrcan.gc.ca/library/
 

Reply via email to