[OPEN-ILS-GENERAL] Non-unique Dewey call numbers with spaces rather than periods

[Dan Wells]

Hello Linda,

I don't fully understand how you built your holdings, but it looks like you found something that worked for you.

Your update is failing on a record which already has both versions of the call number (correct with '.' and incorrect with 'space').  See here:

http://lib.etspraha.cz/eg/opac/record/4465?query=evangelical;qtype=keyword

If you manually merge those copies onto either call number, there is a chance your listed SQL query can succeed.  If not, you will need to determine how many records you have which already have a correct call number listed:

SELECT record,label FROM asset.call_number WHERE label LIKE '%.%';

That doesn't tell the whole story (i.e whether you have *both* types of call numbers on that record), but it's a quick way to see what you are up against.  If it's just a few, I'd merge them manually.  Otherwise, I'd next do a JOIN to see how many actually have both types of call number.  If it is still a lot, working up a little script or even a more complex query to do the merging would be your best bet.

Sincerely,
Dan


Daniel Wells
Library Programmer/Analyst
Hekman Library, Calvin College
616.526.7133

-----Original Message-----
From: open-ils-general-bounces at list.georgialibraries.org [mailto:open-ils-general-bounces at list.georgialibraries.org] On Behalf Of Linda Jansova
Sent: Monday, April 28, 2014 1:56 AM
To: Evergreen Discussion Group
Subject: [OPEN-ILS-GENERAL] Non-unique Dewey call numbers with spaces rather than periods

Hi all,

We have imported bibliographic and holdings data of the Evangelical Theological Seminary library to Evergreen 2.5.3!

Yet, we have encountered the following problem (maybe a bit related to a recent discussion on Dewey normalization: 
http://comments.gmane.org/gmane.education.libraries.open-ils.general/9661):

The library uses Dewey decimals as call numbers but these call numbers are not unique (no cutters are applied). In the OPAC, these data can be found as correctly imported in subfield "a" of the the 082 field (which means the data in MARCXML have been well preserved). But during the import, they were normalized in metabib.full_rec and call numbers are created from these data. There is no problem with padding zeroes as these have not been added but periods within the Dewey number have been overrided by spaces, e.g., http://lib.etspraha.cz/eg/opac/results?query=evangelical;qtype=keyword.

Therefore we have - unsuccessfully - tried to add the periods back but then the call numbers are expected to be unique which is not our case. 
So an error has occured:

evergreen=# UPDATE asset.call_number SET label = replace(label, ' ', '.');
ERROR:  duplicate key value violates unique constraint "asset_call_number_label_once_per_lib"
DETAIL:  Key (record, owning_lib, label, prefix, suffix)=(4465, 4, 274.3708, -1, -1) already exists.

Is there anything we can do about it (get the periods back to call numbers and have the call numbers that are not unique)?

Any ideas are very welcome :-)!

Linda and Vaclav Jansovi