Hello, Ms. McMillan! In our latest exchange, on 06 Jan. 2022 at 12:17 [JST], I received the following from you: We have this one that picks up incorrect indicators for authors and series
SELECT i.barcode AS 'Item barcode', ExtractValue(b.metadata, '//datafield[@tag="100"]/subfield[@code="9"]') AS '100$9', ExtractValue(b.metadata, '//datafield[@tag="100"]/subfield[@code="a"]') AS '100$a', ExtractValue(b.metadata, '//datafield[@tag="245"]/subfield[@code="a"]') AS '245$a', ExtractValue(b.metadata, '//datafield[@tag="440"]/subfield[@code="a"]') AS '440$a', ExtractValue(b.metadata, '//datafield[@tag="490"]/subfield[@code="a"]') AS '490$a', ExtractValue(b.metadata, '//datafield[@tag="490"]/subfield[@code="v"]') AS '490$v', ExtractValue(b.metadata, '//datafield[@tag="800"]/subfield[@code="9"]') AS '800$9', ExtractValue(b.metadata, '//datafield[@tag="800"]/subfield[@code="a"]') AS '800$a', ExtractValue(b.metadata, '//datafield[@tag="800"]/subfield[@code="t"]') AS '800$t', ExtractValue(b.metadata, '//datafield[@tag="800"]/subfield[@code="p"]') AS '800$p', ExtractValue(b.metadata, '//datafield[@tag="800"]/subfield[@code="v"]') AS '800$v', ExtractValue(b.metadata, '//datafield[@tag="830"]/subfield[@code="9"]') AS '830$9', ExtractValue(b.metadata, '//datafield[@tag="830"]/subfield[@code="a"]') AS '830$a', ExtractValue(b.metadata, '//datafield[@tag="830"]/subfield[@code="v"]') AS '830$v' FROM biblio_metadata b LEFT JOIN items i on b.biblionumber = i.biblionumber WHERE date(dateaccessioned) BETWEEN <<Added BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
And this one which picks up incorrect indicators or missing series in the 830$a field
SELECT biblio.biblionumber AS biblionumber, items.barcode AS barcode, ExtractValue(biblio_metadata.metadata, '//datafield[@tag="830"]/subfield[@code="a"]') AS heading FROM biblio_metadata JOIN biblio ON biblio.biblionumber=biblio_metadata.biblionumber JOIN items ON items.biblionumber=biblio.biblionumber WHERE length(ExtractValue(biblio_metadata.metadata, '//datafield[@tag="830"]/subfield[@code="a"]')) != 0 AND length(ExtractValue(biblio_metadata.metadata, '//datafield[@tag="830"]/subfield[@code="9"]')) = 0 ORDER BY items.barcode ASC
Ngā mihi
In response to my plea on 06 Jan. 2022 at 11:35 [JST], to wit:
I know how to compile a report when a subfield has particular value. Is there a way to compile a report when ind 1 or ind 2 has a particular value?
Here's a sample problem: finding subject headings whose ind 2 = "4".
The SQL SELECT statement might look something like
SELECT biblioitems.biblionumber AS 'Bib. Record No.', ExtractValue(metadata, '//datafield[@tag="245"]/subfield[@code="a"]') AS 'Main Title', ExtractValue(metadata,'//datafield[@tag="650"]/subfield[@ ind2 ="1"]') AS 'Topical Subject' FROM biblioitems JOIN biblio_metadata ON (biblioitems.biblionumber = biblio_metadata.biblionumber) WHERE ExtractValue(metadata, '//datafield[@tag="650"]/@ind2="2"') = "4"
I've looked in the Koha wiki, but I have not successfully found hints, instructions, sample reports, etc.
Can anyone suggest an alternative? Maybe MarcEdit will be easier than SQL.
Thanks! This gives me a start. -- よろしくお願いいたします。 /yorosiku onegai simasu/ = Please take care of the aforementioned. -- Charles. Charles Kelley, MLS PSC 704 Box 1029 APO AP 96338 Charles Kelley Tsukimino 1-Chome 5-2 Tsukimino Gaadenia #210 Yamato-shi, Kanagawa-ken 〒242-0002 JAPAN +1-301-741-7122 [US cell] +81-80-4356-2178 [JPN cell] mnogojazyk@aol.com [h] cmkelleymls@gmail.com [p] linkedin.com/in/cmkelleymls <http://www.linkedin.com/in/cmkelleymls> Meeting Your Information Needs. Virtually.