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 sudrl...@vt.edu | 540.231.9669 <+15402319669> On Wed, May 27, 2020 at 2:33 PM Elaine Bradtke <e...@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 <e...@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.dru...@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 <e...@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@lists.katipo.co.nz > >> > Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha > >> > > > _______________________________________________ > > Koha mailing list http://koha-community.org > Koha@lists.katipo.co.nz > Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha > _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha