[Koha] Reports by Indicator

[Charles Kelley]

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 at aol.com [h]
    cmkelleymls at gmail.com [p]

    linkedin.com/in/cmkelleymls <http://www.linkedin.com/in/cmkelleymls>
    Meeting Your Information Needs. Virtually.