[Koha] Report help needed
Michael Sutherland
sudrland at vt.edu
Thu May 28 08:59:50 NZST 2020
Elaine,
Maybe this would be helpful for those codes -
https://www.loc.gov/marc/bibliographic/bdleader.html
And, to break it down for those that do not know and are interested, (at
the risk of condescending, which is not my intention, please, please, I
hope you do not take it that way)
in the expression - SUBSTRING(ExtractValue(metadata,'//leader'),7,1) AS
"Position06"
you are wanting to extract a piece (substring) from the entire leader
string - metadata,'//leader' - the 7 is the value that specifies the
initial position from which the characters can be extracted. The first
position of the expression starts with 1. But, in MARC the first position
is 0 (zero). The next value is a positive integer value that specifies the
ending limit and determines how many characters are going to be
extracted from the given expression in this case '1', which will give you
the single letter code in the leader 06, or the seventh position, however
you choose to look at it.
So, in collaboration with ByWater Solutions, I have co-written the
following report, which I use to report collection statistics by itemtype
to ACRL and ARL on our collection sans suppressed records from the OPAC. It
takes the combination of the leader 06 and 07 to determine itemtype and
count them.
SELECT
CASE SUBSTR(metadata,282,2)
WHEN ' m' THEN 'Book'
WHEN '2m' THEN 'Book'
WHEN 'am' THEN 'Book'
WHEN 'aa' THEN 'Book'
WHEN 'ac' THEN 'Book'
WHEN 'ad' THEN 'Book'
WHEN 'ai' THEN 'Book'
WHEN 'as' THEN 'Journal'
WHEN 'cc' THEN 'Music Score'
WHEN 'cm' THEN 'Music Score'
WHEN 'dm' THEN 'Music Score'
WHEN 'ei' THEN 'Map'
WHEN 'em' THEN 'Map'
WHEN 'es' THEN 'Map'
WHEN 'ga' THEN 'Video recording and motion pictures'
WHEN 'gs' THEN 'Video recording and motion pictures'
WHEN 'gc' THEN 'Video recording and motion pictures'
WHEN 'gi' THEN 'Video recording and motion pictures'
WHEN 'gm' THEN 'Video recording and motion pictures'
WHEN 'ic' THEN 'Non-music sound recording'
WHEN 'im' THEN 'Non-music sound recording'
WHEN 'is' THEN 'Non-music sound recording'
WHEN 'jm' THEN 'Music sound recording'
WHEN 'jc' THEN 'Music sound recording'
WHEN 'ji' THEN 'Music sound recording'
WHEN 'kc' THEN '2D image'
WHEN 'km' THEN '2D image'
WHEN 'ma' THEN 'Computer file'
WHEN 'mi' THEN 'Computer file'
WHEN 'mm' THEN 'Computer file'
WHEN 'ms' THEN 'Computer file'
WHEN 'oc' THEN 'Kit'
WHEN 'om' THEN 'Kit'
WHEN 'pc' THEN 'Mixed material'
WHEN 'pm' THEN 'Mixed material'
WHEN 'rc' THEN '3D Object'
WHEN 'rm' THEN '3D Object'
WHEN 'tc' THEN 'Manuscript'
WHEN 'tm' THEN 'Manuscript'
ELSE 'unknown' END AS Type,
COUNT(DISTINCT biblionumber) AS Titles,
COUNT(itemnumber) AS Volumes
FROM biblio_metadata
LEFT JOIN items USING (biblionumber)
WHERE
ExtractValue(metadata,'//datafield[@tag="942"]/subfield[@code="n"]')
!= 1
GROUP BY Type
WITH ROLLUP
which generates the following
TypeTitlesVolumes
2D image 65 92
3D Object 21 21
Book 1193934 1329489
Computer file 630 1555
Journal 38568 542424
Kit 41 66
Manuscript 5461 14120
Map 4754 5770
Mixed material 253 2517
Music Score 11948 15757
Music sound recording 9193 9766
Non-music sound recording 613 818
unknown 1018 214
Video recording and motion pictures 15246 17090
1281745 1939699
titles is the number of bib records and volumes are the number of items
attached to a bib record.
Best regards,
Michael
_________________________________________
*Michael J. Sutherland*
University Libraries
Virginia Tech
sudrland at vt.edu | 540.231.9669 <+15402319669>
On Wed, May 27, 2020 at 2:33 PM Elaine Bradtke <eb at efdss.org> wrote:
> Yes, I think I've got it now. Thanks for your help. The hard part was
> figuring out where to find the itemtype in the bib record.
> SELECT biblio.biblionumber,
> SUBSTRING(ExtractValue(metadata,'//leader'),7,1) AS "Position06"
> FROM biblio
> LEFT JOIN biblio_metadata USING (biblionumber)
> WHERE ExtractValue( metadata, '//datafield[@tag="942"]/subfield[@code="c"]'
> ) = <<Item Type|itemtypes>>
> Elaine Bradtke
> 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
>
>
> On Wed, May 27, 2020 at 10:50 AM Elaine Bradtke <eb at efdss.org> wrote:
>
> > It doesn't throw up error messages, but what I really need is a list of
> > biblio numbers, with the info in position 6 of the leader and the
> itemtype
> > from the bib record, but also choose a specific itemtype (not books).
> I'm
> > going to try to mash up what you did with another report and see what
> > happens.
> > Elaine Bradtke
> > 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
> >
> >
> > On Wed, May 27, 2020 at 1:51 AM Jonathan Druart <
> > jonathan.druart at bugs.koha-community.org> wrote:
> >
> >> Hi Elaine,
> >>
> >> The biblioitems.marcxml field has been moved to
> biblio_metadata.metadata.
> >>
> >> Does this query work as you want:
> >> SELECT CONCAT('<a
> >> href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=
> >> ',b.biblionumber,'\">',b.biblionumber,'</a>')
> >> AS biblionumber
> >> FROM biblio b
> >> LEFT JOIN biblioitems bi ON b.biblionumber= bi.biblionumber
> >> LEFT JOIN biblio_metadata bm ON b.biblionumber=bm.biblionumber
> >> WHERE bi.itemtype = 'BK' AND SUBSTR(bm.metadata,INSTR(bm.metadata,
> >> "<leader>")+8+6,1) = 'a';
> >>
> >> Regards,
> >> Jonathan
> >>
> >> Le mar. 26 mai 2020 à 20:35, Elaine Bradtke <eb at efdss.org> a écrit :
> >> >
> >> > What I'd like it to do - give a list of biblio numbers for records
> with
> >> a
> >> > particular itemtype, and the information found in the Leader *06 -
> Type
> >> of
> >> > record *field.
> >> > I found the following in the reports library as something that could
> be
> >> > modified to fit my needs. But it doesn't work I get the following
> >> > message: Unknown column 'marcxml' in 'field list'
> >> > Please check the log for further details.
> >> > Also, I don't understand how to indicate Leader position 06
> >> >
> >> > SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/
> >> detail.pl?biblionumber=
> >> > ',biblionumber,'\">',biblionumber,'</a>')
> >> >
> >> > AS biblionumber
> >> >
> >> > FROM biblioitems,
> >> >
> >> > (SELECT biblioitemnumber, SUBSTR(marcxml,INSTR(marcxml,
> >> "<leader>")+8+6,1)
> >> >
> >> > AS leader6 FROM biblioitems)
> >> >
> >> > AS leaders
> >> >
> >> > WHERE biblioitems.biblioitemnumber=leaders.biblioitemnumber AND
> >> > leaders.leader6 = 'a'
> >> >
> >> > AND itemtype = <<Item Type|itemtypes>>
> >> >
> >> > Any help would be appreciated
> >> >
> >> > stay safe,
> >> >
> >> > Elaine Bradtke
> >> > 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
> >> > _______________________________________________
> >> >
> >> > Koha mailing list http://koha-community.org
> >> > Koha at lists.katipo.co.nz
> >> > Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
> >>
> >
> _______________________________________________
>
> Koha mailing list http://koha-community.org
> Koha at lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
More information about the Koha
mailing list