[Koha-bugs] [Bug 19495] New: Automatic report conversion needs to do global replace on 'biblioitems' and 'marcxml'

[bugzilla-daemon at bugs.koha-community.org]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19495

            Bug ID: 19495
           Summary: Automatic report conversion needs to do global replace
                    on 'biblioitems' and 'marcxml'
 Change sponsored?: ---
           Product: Koha
           Version: 17.05
          Hardware: All
                OS: All
            Status: NEW
          Severity: enhancement
          Priority: P5 - low
         Component: Reports
          Assignee: koha-bugs at lists.koha-community.org
          Reporter: barton at bywatersolutions.com
        QA Contact: testopia at bugs.koha-community.org

Bug 17898 provides a way of converting reports that use biblioitems.marcxml so
that they will use biblio_metadata.metadata instead.

This only works with reports that do not refer to other columns in the
biblioitems table. This is a known limitation. It means that we should be able
to do a substitution of every occurrence of biblioitems with biblio_metadata,
and every occurrence of marcxml with metadata.

Unfortunately, we're not doing a global replace, we're only replacing the first
occurrence. Here's the original:

SELECT
    biblio.title,
    biblio.biblionumber,
    A.barcode,ExtractValue(biblioitems.marcxml,'//controlfield[@tag="001"]') AS
"field_001",
    A.itemcallnumber,
    A.itype
FROM
    items A
    JOIN biblio on (A.biblionumber = biblio.biblionumber)
    JOIN biblioitems on (A.biblionumber = biblioitems.biblionumber)
WHERE
    withdrawn = 1 AND A.biblionumber NOT in (
        SELECT
            biblionumber
        FROM
            items B
        WHERE
            B.itemnumber != A.itemnumber
    )

And here's the converted:

SELECT
    biblio.title,
    biblio.biblionumber,
   
A.barcode,ExtractValue(biblio_metadata.metadata,'//controlfield[@tag="001"]')
AS "field_001",
    A.itemcallnumber,
    A.itype 
FROM
    items A
    JOIN biblio on (A.biblionumber = biblio.biblionumber)
    JOIN biblioitems on (A.biblionumber = biblioitems.biblionumber) 
WHERE
    withdrawn = 1 AND A.biblionumber NOT in (
        SELECT
            biblionumber 
        FROM
            items B 
        WHERE
            B.itemnumber != A.itemnumber
    )

Note that there are three occurrences of 'biblioitems' in the first query, but
only the first has been substituted in the second.

-- 
You are receiving this mail because:
You are the assignee for the bug.
You are watching all bug changes.