[Koha] [EXTERNAL] Re: Can't include itype in report (was Bulk search/delete by ISBNs?)
King, Fred
Fred.King at Medstar.net
Wed Sep 4 05:17:41 NZST 2019
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
fred.king at medstar.net
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 <koha-bounces at lists.katipo.co.nz> On Behalf Of Katrin Fischer
Sent: Sunday, September 01, 2019 2:41 PM
To: koha at lists.katipo.co.nz
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:fred.king at medstar.net<mailto:fred.king at medstar.net>
> 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:chrisc at catalyst.net.nz>
> Sent: Thursday, August 29, 2019 5:23 PM
> To: mailto:koha at lists.katipo.co.nz; King, Fred <mailto:Fred.King at Medstar.net>
> 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:Fred.King at Medstar.net<mailto:Fred.King at Medstar.net>> 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:fred.king at medstar.net<mailto:fred.king at medstar.net%3E<mailto:fred.king
> @medstar.net%3cmailto:fred.king at medstar.net%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:Koha at lists.katipo.co.nz<mailto:Koha at lists.katipo.co.nz>
> 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:Koha at lists.katipo.co.nz
> 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:Koha at lists.katipo.co.nz
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=
More information about the Koha
mailing list