[Koha] [EXTERNAL] Re: Can't include itype in report (was Bulk search/delete by ISBNs?)
Katrin Fischer
katrin.fischer.83 at web.de
Mon Sep 9 18:22:24 NZST 2019
Hi Fred,
sorry, you lost me a little bit here :( But maybe these hints can still
be helpful:
When exporting records without items, make sure you uncheck the
"libraries" at the top section of the form. They act as a filter on the
library information in the items and will filter out all record without
items, when there is one or more selected.
To find your e-books, you could do something like:
SELECT
biblionumber
FROM biblio_metadata
WHERE ExtractValue(metadata,'//datafield[@tag="942"]/subfield[@code="c"]') = "EBOOK"
Hope this helps,
Katrin
On 03.09.19 19:17, King, Fred wrote:
> 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