[Koha] Report help needed

[Heather Braum (NEKLS)]

Elaine, try the below. It looks like you were missing some commas -- that
was what was causing the syntax errors; you can then name the columns like
I did below or however you want using as ___ single-word (as 780t) or as
"two or more words" (as 780w field_).

Make sure every column of data in the select statement has a comma after
it, except for the last on before the from statement.

Hope that helps!  -- it ran on my system.

SELECT
biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype,

ExtractValue(biblioitems.marcxml,
'//datafield[@tag="780"]/subfield[@code="t"]') as 780t,
ExtractValue(biblioitems.marcxml,
'//datafield[@tag="780"]/subfield[@code="w"]') as "780w field",
ExtractValue(biblioitems.marcxml,
'//datafield[@tag="866"]/subfield[@code="a"]') as 866a,
ExtractValue(biblioitems.marcxml,
'//datafield[@tag="866"]/subfield[@code="z"]') as 866z
FROM biblioitems LEFT JOIN biblio USING (biblionumber)
WHERE biblioitems.itemtype='CR'

Heather Braum
NExpress Coordinator
Resource Sharing Librarian
Northeast Kansas Library System
hbraum at nekls.org

"The illiterate of the 21st century will not be those who cannot read
and write, but those who cannot learn, unlearn, and relearn." ~Alvin
Toffler, *Rethinking the Future*




On Mon, Nov 17, 2014 at 5:38 PM, Elaine Bradtke <eb at efdss.org> wrote:

> I have a request for a report that is beyond my paltry abilities and that
> of the report builder.
>
>
> I have this, and it works (thanks Liz!):
> SELECT
>
>  biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype
> FROM biblioitems LEFT JOIN biblio USING (biblionumber)   WHERE
> biblioitems.itemtype='CR'
>
> But I need to add in more columns that involve the MARC XML
>
> Specifically
> 780 t (previous title)
> 780 w (previous control no.)
> 866 a (holdings)
> 866 z (note)
> 867 e (source)
>
> When I try to add in:
>   Extract value ExtractValue(marcxml,
> '//datafield[@tag="780"]/subfield[@code="t"]')
> I manage to break the above query and it complains of syntax errors.
> Does it matter where this Extract phrase comes in?
> How do you handle multiple subfields (780 t and w for instance)
> For that matter, how do you handle multiple fields?  And how can I label
> the columns
>
> The following (and a few variations on the theme) did not work:
>
> SELECT
>
>  biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype
> ExtractValue(marcxml, '//datafield[@tag="780"]/subfield[@code="t"]')
> ExtractValue(marcxml, '//datafield[@tag="780"]/subfield[@code="w"]')
> ExtractValue(marcxml, '//datafield[@tag="866"]/subfield[@code="a"]')
> ExtractValue(marcxml, '//datafield[@tag="866"]/subfield[@code="z"]')
> FROM biblioitems LEFT JOIN biblio USING (biblionumber)   WHERE
> biblioitems.itemtype='CR'
>
>
> As always I am very, very grateful for whatever help you can give.
> Thanks
> --
> Elaine Bradtke
> Data Wrangler
> VWML
> English Folk Dance and Song Society | http://www.efdss.org
> Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
> Tel    +44 (0) 20 7485 2206 (This number is for the English Folk Dance and
> Song Society in London, England. If you wish to phone me personally, send
> an e-mail first. I work off site)
> --------------------------------------------------------------------------
> Registered Company No. 297142
> Charity Registered in England and Wales No. 305999
> ---------------------------------------------------------------------------
> "Writing about music is like dancing about architecture"
> --Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha
>