Report help needed
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)
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@gmail.com On Mon, Jun 17, 2013 at 7:56 PM, Elaine Bradtke <eb@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@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
Thanks Bernardo, you have come to my rescue again! Someday I will take the time to actually learn how to do this properly. It worked perfectly. Elaine On Tue, Jun 18, 2013 at 1:17 AM, Bernardo Gonzalez Kriegel < bgkriegel@gmail.com> wrote:
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@gmail.com
On Mon, Jun 17, 2013 at 7:56 PM, Elaine Bradtke <eb@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@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
-- 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)
participants (2)
-
Bernardo Gonzalez Kriegel -
Elaine Bradtke