[OPEN-ILS-GENERAL] Creating a Report Listing Authority Terms andAuthority Control Number

[Whalen, Liam]

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