[Koha] Help with SQL report

[Caroline Cyr-La-Rose]

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

SELECT
     biblionumber AS ""
FROM
     aqorders
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

SELECT CONCAT('<a 
href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') 
AS biblionumber, biblio.title, biblio.author
FROM biblio_metadata
JOIN biblio ON (biblio_metadata.biblionumber = biblio.biblionumber)
WHERE ExtractValue(metadata, "//datafield[@tag=<< Field 
(XXX)>>]/subfield[@code=<<Subfield(Y)>>]")
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 
(XXX)>>]/subfield[@code=<<Subfield(Y)>>]")
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