[Koha] Help with SQL report

Caroline Cyr-La-Rose caroline.cyr-la-rose at inlibro.com
Sat Jan 26 03:49:06 NZDT 2019

Hi Christopher,

Do you use the acquisitions module? If not, what MARC field is your 
vendor information in?

If you use the acquisitions module, you can try this

     biblionumber AS ""
LEFT JOIN aqbasket USING (basketno)
LEFT JOIN aqbooksellers ON (aqbasket.booksellerid=aqbooksellers.id)
WHERE aqbooksellers.name LIKE <<Vendor name (use % as wildcard)>>

If your vendor information is in a MARC field, you need to change the 
table biblioitems in the FROM and JOIN sections to biblio_metadata (as 
well as changing the marcxml to metadata). Your query should look like

AS biblionumber, biblio.title, biblio.author
FROM biblio_metadata
JOIN biblio ON (biblio_metadata.biblionumber = biblio.biblionumber)
WHERE ExtractValue(metadata, "//datafield[@tag=<< Field 
LIKE <<Search Term (USE % AS wildcard)>>

Finally, if you only need a list of biblionumbers for batch delete, you 
can remove the superfluous information in the SELECT section :

SELECT biblio.biblionumber AS ""
FROM biblio_metadata
JOIN biblio ON (biblio_metadata.biblionumber = biblio.biblionumber)
WHERE ExtractValue(metadata, "//datafield[@tag=<< Field 
LIKE <<Search Term (USE % AS wildcard)>>

N.B. When I want a list for batch modification/delete, I always put 
SELECT biblionumber AS "". That way, you don't have the pesky error 
message saying Koha didn't find the biblionumber "biblionumber"...

Good luck!

Caroline Cyr La Rose, M.L.I.S.
Product Manager, inLibro

Le 19-01-24 à 16 h 58, Christopher Davis a écrit :
> Greetings Colleagues,
> I have been attempting to use the "Biblios with like data in a 
> subfield of a field" report which is found within the Koha reports 
> library at 
> https://wiki.koha-community.org/wiki/SQL_Reports_Library#Biblios_with_like_data_in_a_subfield_of_a_field; 
> however, no matter what values I input into the report dialog boxes, I 
> still get a long list of my system's complete holdings instead of a 
> much shorter list of records from a certain vendor. I did notice that 
> I needed to change the "marcxml" on line four of the SQL code to 
> "metadata" or else the report failed to run. What am I doing wrong?
> I am just trying to get a list of bibliographic records from a certain 
> vendor so that I can then feed the record numbers into the Batch 
> Record Deletion tool. Maybe there's a better report to use than this one?
> Many thanks,
> *
> *
> *Christopher Davis, MLS*
> Systems & E-Services Librarian
> Uintah County Library
> 204 E 100 N
> Vernal, UT 84078
> phone: (435) 789-0091 ext. 261 <tel:+14357890091>
> email: cgdavis at uintah.utah.gov <mailto:cgdavis at uintah.utah.gov>
> web: uintahlibrary.org <https://uintahlibrary.org>
> catalog: basinlibraries.org <https://basinlibraries.org>
> facebook.com/uintahcountylibrary 
> <https://facebook.com/uintahcountylibrary>
> instagram.com/uintahcountylibrary 
> <https://instagram.com/uintahcountylibrary>
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha

More information about the Koha mailing list