[Koha] Report help needed

[Bernardo Gonzalez Kriegel]

Elaine,
you can't have DISTINCT heading and biblionumber at the same time.

A small modification will give you what you are looking

SELECT biblionumber, ExtractValue(marcxml,
'//datafield[@tag="650"]/subfield[@code="a"]') AS heading
 FROM biblioitems
 WHERE
  length(ExtractValue(marcxml,
'//datafield[@tag="650"]/subfield[@code="a"]')) != 0 AND
  length(ExtractValue(marcxml,
'//datafield[@tag="650"]/subfield[@code="9"]')) = 0
ORDER BY heading

Regards,
Bernardo

-- 
Bernardo Gonzalez Kriegel
bgkriegel at gmail.com


On Mon, Jun 17, 2013 at 7:56 PM, Elaine Bradtke <eb at efdss.org> wrote:

> I'm tracking down some unauthorized subject headings and have run into a
> problem with a term  that is far too common in our catalogue to be useful
> in any kind of search.
>
>  I'm trying to devise a report that will look for 650 fields with a
> subfield a, but without a subfield 9  -  But the second half of what I need
> - a report of not just the rogue term, but also the biblio number of the
> record that it is in. I have tried several things but cannot get it to
> work.
>
> The functional part of my report is here:
>
> SELECT DISTINCT heading FROM (
>  SELECT ExtractValue(marcxml,
> '//datafield[@tag="650"]/subfield[@code="a"]') AS heading
>  FROM biblioitems
>  WHERE
>   length(ExtractValue(marcxml,
> '//datafield[@tag="650"]/subfield[@code="a"]')) != 0 AND
>   length(ExtractValue(marcxml,
> '//datafield[@tag="650"]/subfield[@code="9"]')) = 0
> ) AS heads
> ORDER BY heading
>
>
> This gives a list of headings in 650 fields that are not linked to
> authorities
> Can anyone help me add in the query that will return the biblionumber too?
> Thanks in advance
> --
> 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
>