Hi Katrin and the rest of the Koha world,
Curiouser and curiouser...
Possible clue: I started out using 3.12 and I've upgraded the same database
since then. Eventually when I can replace the server I'll probably start with a
fresh installation.
Pretty obvious: I don't know SQL very well. On to the puzzle:
Here's one of the records, biblio 13098 (below my signature). I downloaded the
whole batch from Rittenhouse and added Koha-specific things, including EBOOK in
the 942$c field.
If I put the ISBN-10, ISBN-13, or the biblio into the search box, I get the
record.
Here's the original script George sent me, which worked on a few of the ISBNs.
Unfortunately, I didn't export the records before I deleted them, so I can't
compare those with the ones that didn't delete. I replaced ISBN-1, ISBN-2,
etc., with the real ISBNs.
SELECT
biblioitems.biblionumber,
biblioitems.isbn
FROM
biblioitems
WHERE
(biblioitems.isbn LIKE "ISBN-1")
OR (biblioitems.isbn LIKE "ISBN-2") . . . etc.
When I run this report I get the titles and a URL with the bib number,
including 13098:
SELECT CONCAT('<a href="http://',
(SELECT value FROM systempreferences WHERE variable='staffClientBaseURL'),
'/cgi-bin/koha/catalogue/detail.pl?biblionumber=',
biblionumber,
'">',
biblionumber,
'</a>') AS 'biblionumber', title
FROM biblio
WHERE biblionumber NOT IN (SELECT biblionumber FROM items)
I went to the Query MARC section and tried this script, which brought up the
ISBN for biblio 13098:
SELECT
ExtractValue((
SELECT metadata
FROM biblio_metadata
WHERE
biblionumber=13098),
'//datafield[@tag="020"]/subfield[@code>="a"]') AS ITEM
HOWEVER.....
This script from the reports library doesn't bring up EBOOKs. Is it because it
starts out with select from items?
SELECT
items.price,items.replacementprice,biblio.title,biblio.author,items.itemcallnumber
FROM items
LEFT JOIN biblioitems ON
(items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
WHERE items.homebranch=<<Home branch|branches>>
ORDER BY items.itemcallnumber ASC
And when I go to Tools, Export Data there doesn't seem to be any way to export
biblio 13098. I've tried From biblio number 13098 to 13098, Item type EBOOK, I
uploaded a file with 13098 on one line--each time it comes up with a MARC file
with zero bytes. If I export without any restrictions, it includes everything
except the ebooks.
What I'm probably going to do is undo the import of all the 5000 or so
R2library MARC records, download the current set, and start fresh. That will
work, but it's not an especially elegant solution. Still, if someone can figure
out why these records aren't showing up, I'd love to know.
Fred King, AHIP
Medical Librarian, MedStar Washington Hospital Center
[email protected]
202-877-6670
ORCID 0000-0001-5266-0279
MedStar Authors Catalog: http://medstarauthors.org
A library in the middle of a community is a cross between an emergency exit, a
life-raft and a festival. They are cathedrals of the mind; hospitals of the
soul; theme parks of the imagination. On a cold rainy island, they are the only
sheltered public spaces where you are not a consumer, but a citizen instead.
--Caitlin Moran
=LDR 01275cam a2200409 i 4500
=001 1678481
=005 20170321084421.0
=008 150722s2017\\\\maua\\\\\b\\\\001\0\eng\d
=010 \\$a 2015943888
=035 \\$9101678481
=020 \\$a9781111542795
=020 \\$a1111542791
=040 \\$aBTCTA$beng$erda$cBTCTA$dYDXCP$dOCLCQ$dIPL
=041 09$aeng
=042 \\$anlmcopyc
=044 \\$9United States
=050 \4$aSF745$b.L45 2017
=060 00$a2016 B-261
=060 10$aSF 745
=100 1\$aLawhead, James B.,$eauthor.
=245 10$aIntroduction to veterinary science /$cJames B. Lawhead, Meecee Baker.
=250 \\$aThird edition.
=264 \1$aBoston, MA :$bCengage Learning$c[2017]
=336 \\$atext$btxt$2rdacontent
=337 \\$aunmediated$bn$2rdamedia
=338 \\$avolume$bnc$2rdacarrier
=504 \\$aIncludes bibliographical references (page 370-374) and index.
=650 12$aVeterinary Medicine
=650 22$aAnimal Diseases
=650 22$aAnimals, Domestic
=700 1\$aBaker, MeeCee,$eauthor.
=300 \\$a online resource
=538 \\$a Mode of Access: World Wide Web
=655 \4$a Electronic books
=942 \\$cEBOOK
=856 4\$zConnect to this resource
online$uhttp://www.r2library.com/Resource/Title/1111542791
=999 \\$c13098$d13098
-----Original Message-----
From: Koha <[email protected]> On Behalf Of Katrin Fischer
Sent: Sunday, September 01, 2019 2:41 PM
To: [email protected]
Subject: [EXTERNAL] Re: [Koha] Can't include itype in report (was Bulk
search/delete by ISBNs?)
** ATTENTION: This email originated from outside the MedStar network. ** DO
NOT CLICK links or attachments unless you recognize the sender and know the
content is safe.
Hi Fred,
did you set the item type EBOOK in 942$c by chance? If yes, then the itemtype
search will find them even if they are no items and we can also craft a SQL
query to find them.
isbn.biblioitems shoudl be set from the data in your bibliographic record
independent of existing items. Can you tell us what you tried?
The 'Query MARC' section of the reports library might give you some
pointers:
https://urldefense.proofpoint.com/v2/url?u=https-3A__wiki.koha-2Dcommunity.org_wiki_SQL-5FReports-5FLibrary-23Query-5FMARC&d=DwIGaQ&c=RvBXVp2Kc-itN3g6r3sN0QK_zL4whPpndVxj8-bJ04M&r=vKh6XwOmjyC51IkP1OfsdjQZoWT2vpi6VZl8El8EPRI&m=PyB66W6hWy8SfdIcAXTzwY-mYfpOQmsCsT3zjci0LJ0&s=QS-gvkNeaydpkDejg4ytJ9iYYV58FUEKwDSLhggIQ_E&e=
Hope this helps,
katrin
On 30.08.19 19:19, King, Fred wrote:
Thanks to Chris and a script from George Williams, it worked like a charm.
Almost. With a few of them.
After a lot of trial and error, I think I might have found the
problem: my SQL queries can’t find the item type EBOOK, or any data
associated with bib records of that type. Since there aren’t any items
associated with these records, that makes sense, but…
Is there any way to run an SQL report looking for something in the
biblioitems.isbn field when there aren’t any items attached? The 020 field has
them, and I can put the ISBN in the search box, so the system knows they’re
there.
Thanks! And if I’m totally confused, could someone point me in the right
direction?
I’m about to leave for the day, and Monday is a holiday here in the States, so
I might not be checking e-mail for a while. Have a great weekend everybody.
Fred King, AHIP
Medical Librarian, MedStar Washington Hospital Center
mailto:[email protected]<mailto:[email protected]>
202-877-6670
ORCID 0000-0001-5266-0279
MedStar Authors Catalog:
https://urldefense.proofpoint.com/v2/url?u=http-3A__medstarauthors.org
&d=DwIGaQ&c=RvBXVp2Kc-itN3g6r3sN0QK_zL4whPpndVxj8-bJ04M&r=vKh6XwOmjyC5
1IkP1OfsdjQZoWT2vpi6VZl8El8EPRI&m=PyB66W6hWy8SfdIcAXTzwY-mYfpOQmsCsT3z
jci0LJ0&s=dgbosaKixmkLdEItpvumGZe1WUOArJbwvUAdfnStmJo&e=
Rest is not idleness, and to lie sometimes on the grass under trees on a
summer's day, listening to the murmur of the water, or watching the clouds
float across the sky, is by no means a waste of time.
--Sir John Lubbock, The Use of Life (1894)
From: Chris Cormack <mailto:[email protected]>
Sent: Thursday, August 29, 2019 5:23 PM
To: mailto:[email protected]; King, Fred <mailto:[email protected]>
Subject: [EXTERNAL] Re: [Koha] Bulk search/delete by ISBNs?
** ATTENTION: This email originated from outside the MedStar network.
** DO NOT CLICK links or attachments unless you recognize the sender and know
the content is safe.
Hi Fred
You might be able to write a report to get all biblio with no items attached,
of a certain type and then use that list of biblionumbers in the bulk biblio
delete.
Hope this helps
Chris
On 30 August 2019 1:51:31 AM NZST, "King, Fred"
<mailto:[email protected]<mailto:[email protected]>> wrote:
Esteemed Colleagues,
My library has a collection of about 5000 online books that are in Koha with
bib records and no items. The publisher periodically archives titles so they're
no longer available, and I have a list of them that I'd like to remove from
Koha. The problem is that my list consists of ISBNs and that there are over 450
of them. I could search and delete one-by-one, but that's an awful lot of
searching and deleting. Is there an easier way?
We're on Koha 17.05* and Ubuntu 16.04.
*I know, I know, but I'm hoping to get a new server before I upgrade.
Fred King, AHIP
Medical Librarian, MedStar Washington Hospital Center
mailto:[email protected]<mailto:[email protected]%3E<mailto:fred.king
@medstar.net%3cmailto:[email protected]%3E>
202-877-6670
ORCID 0000-0001-5266-0279
MedStar Authors Catalog:
https://urldefense.proofpoint.com/v2/url?u=http-3A__medstarauthors.org
&d=DwIGaQ&c=RvBXVp2Kc-itN3g6r3sN0QK_zL4whPpndVxj8-bJ04M&r=vKh6XwOmjyC5
1IkP1OfsdjQZoWT2vpi6VZl8El8EPRI&m=PyB66W6hWy8SfdIcAXTzwY-mYfpOQmsCsT3z
jci0LJ0&s=dgbosaKixmkLdEItpvumGZe1WUOArJbwvUAdfnStmJo&e=
<https://urldefense.proofpoint.com/v2/url?u=http-3A__medstarauthors.or
g&d=DwMFaQ&c=RvBXVp2Kc-itN3g6r3sN0QK_zL4whPpndVxj8-bJ04M&r=vKh6XwOmjyC
51IkP1OfsdjQZoWT2vpi6VZl8El8EPRI&m=E4eNiVIJtiO21Ua5DyrKj9CVOqS-pVLhHLX
rzdtSCxA&s=nxjZBoMiTfLJ423yCYGyfFHjNanefLzmsCqbKH1soE0&e=>
Rest is not idleness, and to lie sometimes on the grass under trees on a
summer's day, listening to the murmur of the water, or watching the clouds
float across the sky, is by no means a waste of time.
--Sir John Lubbock, The Use of Life (1894)
________________________________
MedStar Health is a not-for-profit, integrated healthcare delivery system, the
largest in Maryland and the Washington, D.C., region. Nationally recognized for
clinical quality in heart, orthopaedics, cancer and GI.
IMPORTANT: This e-mail (including any attachments) may contain information that
is private, confidential, or protected by attorney-client or other privilege.
If you received this e-mail in error, please delete it from your system without
copying it and notify sender by reply e-mail, so that our records can be
corrected... Thank you.
Help conserve valuable resources - only print this email if necessary.
________________________________
Koha mailing list
https://urldefense.proofpoint.com/v2/url?u=http-3A__koha-2Dcommunity.o
rg&d=DwIGaQ&c=RvBXVp2Kc-itN3g6r3sN0QK_zL4whPpndVxj8-bJ04M&r=vKh6XwOmjy
C51IkP1OfsdjQZoWT2vpi6VZl8El8EPRI&m=PyB66W6hWy8SfdIcAXTzwY-mYfpOQmsCsT
3zjci0LJ0&s=EGh9Zx7lU1KNDeVVh24O_8nNFzrhJBv3ZZ46fXoubo8&e=
<https://urldefense.proofpoint.com/v2/url?u=http-3A__koha-2Dcommunity.
org&d=DwMFaQ&c=RvBXVp2Kc-itN3g6r3sN0QK_zL4whPpndVxj8-bJ04M&r=vKh6XwOmj
yC51IkP1OfsdjQZoWT2vpi6VZl8El8EPRI&m=E4eNiVIJtiO21Ua5DyrKj9CVOqS-pVLhH
LXrzdtSCxA&s=6sMFUxsF0xaQemqufEI6aYJ45LI1E5iAfi3JhLkRFC8&e=>
mailto:[email protected]<mailto:[email protected]>
https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.katipo.co.n
z_mailman_listinfo_koha&d=DwIGaQ&c=RvBXVp2Kc-itN3g6r3sN0QK_zL4whPpndVx
j8-bJ04M&r=vKh6XwOmjyC51IkP1OfsdjQZoWT2vpi6VZl8El8EPRI&m=PyB66W6hWy8Sf
dIcAXTzwY-mYfpOQmsCsT3zjci0LJ0&s=55x7b41_Q-1BpXJY8T5PZIjrCv8Z3gLMMAjLH
d9vUpg&e=
<https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.katipo.co.
nz_mailman_listinfo_koha&d=DwMFaQ&c=RvBXVp2Kc-itN3g6r3sN0QK_zL4whPpndV
xj8-bJ04M&r=vKh6XwOmjyC51IkP1OfsdjQZoWT2vpi6VZl8El8EPRI&m=E4eNiVIJtiO2
1Ua5DyrKj9CVOqS-pVLhHLXrzdtSCxA&s=16UQvxVDiNyisn80ywDdWsKJ0Skgz1BnItHU
hoVZrxU&e=>
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
_______________________________________________
Koha mailing list
https://urldefense.proofpoint.com/v2/url?u=http-3A__koha-2Dcommunity.o
rg&d=DwIGaQ&c=RvBXVp2Kc-itN3g6r3sN0QK_zL4whPpndVxj8-bJ04M&r=vKh6XwOmjy
C51IkP1OfsdjQZoWT2vpi6VZl8El8EPRI&m=PyB66W6hWy8SfdIcAXTzwY-mYfpOQmsCsT
3zjci0LJ0&s=EGh9Zx7lU1KNDeVVh24O_8nNFzrhJBv3ZZ46fXoubo8&e=
mailto:[email protected]
https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.katipo.co.n
z_mailman_listinfo_koha&d=DwIGaQ&c=RvBXVp2Kc-itN3g6r3sN0QK_zL4whPpndVx
j8-bJ04M&r=vKh6XwOmjyC51IkP1OfsdjQZoWT2vpi6VZl8El8EPRI&m=PyB66W6hWy8Sf
dIcAXTzwY-mYfpOQmsCsT3zjci0LJ0&s=55x7b41_Q-1BpXJY8T5PZIjrCv8Z3gLMMAjLH
d9vUpg&e=
_______________________________________________
Koha mailing list
https://urldefense.proofpoint.com/v2/url?u=http-3A__koha-2Dcommunity.org&d=DwIGaQ&c=RvBXVp2Kc-itN3g6r3sN0QK_zL4whPpndVxj8-bJ04M&r=vKh6XwOmjyC51IkP1OfsdjQZoWT2vpi6VZl8El8EPRI&m=PyB66W6hWy8SfdIcAXTzwY-mYfpOQmsCsT3zjci0LJ0&s=EGh9Zx7lU1KNDeVVh24O_8nNFzrhJBv3ZZ46fXoubo8&e=
mailto:[email protected]
https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.katipo.co.nz_mailman_listinfo_koha&d=DwIGaQ&c=RvBXVp2Kc-itN3g6r3sN0QK_zL4whPpndVxj8-bJ04M&r=vKh6XwOmjyC51IkP1OfsdjQZoWT2vpi6VZl8El8EPRI&m=PyB66W6hWy8SfdIcAXTzwY-mYfpOQmsCsT3zjci0LJ0&s=55x7b41_Q-1BpXJY8T5PZIjrCv8Z3gLMMAjLHd9vUpg&e=
