[Koha] Report help needed

[Elaine Bradtke]

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
>>
>