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=
_______________________________________________
Koha mailing list http://koha-community.org
[email protected]
https://lists.katipo.co.nz/mailman/listinfo/koha
