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

[Linda Jansova]

Thank you, Dan! Your hint with the bib record with two versions of the 
same call number has helped us find the right way to proceed (four 
remaining troublesome records will be dealt with manually :-):

evergreen=# UPDATE asset.call_number SET label = replace(label, '.', '-');
UPDATE 10355
evergreen=# UPDATE asset.call_number SET label = replace(label, ' ', '.');
UPDATE 10355
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)=(5651, 4, 
248.84, -1, -1) already exists.

Linda

On 04/28/2014 03:26 PM, Dan Wells wrote:
> 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
>