[Koha] Report help needed
Michael Sutherland
sudrland at vt.edu
Thu May 28 11:15:52 NZST 2020
Thank you. I'm unfamiliar with that particular bug and will check it out
for our records.
Best,
Michael
_________________________________________
*Michael J. Sutherland*
University Libraries
Virginia Tech
sudrland at vt.edu | 540.231.9669 <+15402319669>
On Wed, May 27, 2020 at 7:05 PM Elaine Bradtke <eb at efdss.org> wrote:
> Not condescending at all, the things I don't know about reports would fill
> a book.
> Interesting report you have there by the way.
> The purpose for mine is to double check the position 06 against the 942 $c
> because there is a bug 19419 that seems to cause trouble with the 008 if
> the Leader 06 is not set correctly for the format (still trying to verify
> that's what's going on). I wanted to find any records that didn't have the
> appropriate Leader 06 for a given format as specified in the 942.
> 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 2:00 PM Michael Sutherland <sudrland at vt.edu>
> wrote:
>
> > 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
> >>
> >
> _______________________________________________
>
> 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