[Koha] How to include 952$x in a report

[Chris Meech]

Hi Paul,
Perhaps this might give you what you want? (Using 999)

SELECT
ExtractValue(more_subfields_xml,
'/collection/record/datafield[\@tag=\"999\"]/subfield[\@code=\"x\"]'), items.barcode, items.biblionumber,items.barcode,items.dateaccessioned
FROM items
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)

ORDER BY items.biblionumber asc


Cheers
Chris Meech

-----Original Message-----
From: koha-bounces at lists.katipo.co.nz [mailto:koha-bounces at lists.katipo.co.nz] On Behalf Of Paul
Sent: Tuesday, 21 February 2012 11:47 a.m.
To: Chris Cormack; koha
Subject: Re: [Koha] How to include 952$x in a report

At 10:24 AM 2/21/2012 +1300, Chris Cormack wrote:
>On 21 February 2012 10:17, Paul <paul.a at aandc.org> wrote:
> > Help requested please from an expert in MySQL reports. Â The code below 
> works
> > perfectly, but I want to add 952$x (a text string for the condition of our
> > holdings as in "good/fair" or "fine tpb") to the first line.
> >
> > If I add items.more_subfields_xml (which includes 952$x) I get a 
> plethora of
> > formatted XML that is unsuitable for copying straight into a spreadsheet.
[snip first trial code]

>http://wiki.koha-community.org/wiki/SQL_Reports_Library#Query_MARC

Thanks Chris, I had tried something along those lines, and have now tried 
again. Started with deleting the:

     WHERE biblionumber=14),

per mysql manual "The statement selects all rows if there is no WHERE 
clause", but still get error "can't use undefined value as an ARRAY 
reference" at guided_reports.pl line 618. After multiple trial/error 
changes to punctuation, etc, here's what I have, still non-functional:

SELECT items.biblionumber,items.barcode,items.dateaccessioned
FROM items;

SELECT ExtractValue(marcxml,'//datafield[@tag="952"]/subfield[@code>="x"]') 
AS ITEM
FROM biblioitems

LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)

ORDER BY items.biblionumber asc

Removing the "SELECT ExtractValue ..." and preceding semi-colon works fine, 
I'm just totally flummoxed on getting the 952$x part to function.  Is 
another LEFT JOIN required?

Could you possibly suggest the correct syntax?  Many thanks.

Paul 

_______________________________________________
Koha mailing list  http://koha-community.org
Koha at lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha