Re: [Koha] SQL report 650 a

[nikunj1]

We are using following sql report, change your barcode range and try

SELECT items.barcode, biblio.author, CONCAT(biblio.title, ", ",IF(
LOCATE('<datafield tag="245"', biblioitems.marcxml) = 0 OR LOCATE('<subfield
code="n">', biblioitems.marcxml,
LOCATE('<datafield tag="245"', biblioitems.marcxml)) = 0 OR
LOCATE('<subfield code="n">', biblioitems.marcxml,
LOCATE('<datafield tag="245"', biblioitems.marcxml)) >
LOCATE('</datafield>', biblioitems.marcxml, LOCATE('<datafield tag="245"',
biblioitems.marcxml)), '',
SUBSTRING( biblioitems.marcxml,
LOCATE('<subfield code="n">', biblioitems.marcxml, LOCATE('<datafield
tag="245"', biblioitems.marcxml)) + 19,
LOCATE('</subfield>', biblioitems.marcxml, LOCATE('<subfield code="n">',
biblioitems.marcxml,
LOCATE('<datafield tag="245"', biblioitems.marcxml)) + 19)
-(LOCATE('<subfield code="n">', biblioitems.marcxml,
LOCATE('<datafield tag=&quot;245&quot;', biblioitems.marcxml)) + 19))),
IF( LOCATE('&lt;datafield tag=&quot;245&quot;', biblioitems.marcxml) = 0 OR
LOCATE('&lt;subfield code=&quot;p&quot;>', biblioitems.marcxml,
LOCATE('<datafield tag="245"', biblioitems.marcxml)) = 0 OR
LOCATE('<subfield code="p">', biblioitems.marcxml,
LOCATE('<datafield tag="245"', biblioitems.marcxml)) >
LOCATE('</datafield>', biblioitems.marcxml, LOCATE('<datafield tag="245"',
biblioitems.marcxml)), '',
SUBSTRING( biblioitems.marcxml,
LOCATE('<subfield code="p">', biblioitems.marcxml, LOCATE('<datafield
tag="245"', biblioitems.marcxml)) + 19,
LOCATE('</subfield>', biblioitems.marcxml, LOCATE('<subfield code="p">',
biblioitems.marcxml,
LOCATE('<datafield tag="245"', biblioitems.marcxml)) + 19)
-(LOCATE('<subfield code="p">', biblioitems.marcxml,
LOCATE('<datafield tag=&quot;245&quot;', biblioitems.marcxml)) + 19))),IF(
LOCATE('&lt;datafield tag=&quot;245&quot;', biblioitems.marcxml) = 0 OR
LOCATE('&lt;subfield code=&quot;b&quot;>', biblioitems.marcxml,
LOCATE('<datafield tag="245"', biblioitems.marcxml)) = 0 OR
LOCATE('<subfield code="b">', biblioitems.marcxml,
LOCATE('<datafield tag="245"', biblioitems.marcxml)) >
LOCATE('</datafield>', biblioitems.marcxml, LOCATE('<datafield tag="245"',
biblioitems.marcxml)), '',
SUBSTRING( biblioitems.marcxml,
LOCATE('<subfield code="b">', biblioitems.marcxml, LOCATE('<datafield
tag="245"', biblioitems.marcxml)) + 19,
LOCATE('</subfield>', biblioitems.marcxml, LOCATE('<subfield code="b">',
biblioitems.marcxml,
LOCATE('<datafield tag="245"', biblioitems.marcxml)) + 19)
-(LOCATE('<subfield code="b">', biblioitems.marcxml,
LOCATE('<datafield tag=&quot;245&quot;', biblioitems.marcxml)) + 19))), IF(
LOCATE('&lt;datafield tag=&quot;245&quot;', biblioitems.marcxml) = 0 OR
LOCATE('&lt;subfield code=&quot;c&quot;>', biblioitems.marcxml,
LOCATE('<datafield tag="245"', biblioitems.marcxml)) = 0 OR
LOCATE('<subfield code="c">', biblioitems.marcxml,
LOCATE('<datafield tag="245"', biblioitems.marcxml)) >
LOCATE('</datafield>', biblioitems.marcxml, LOCATE('<datafield tag="245"',
biblioitems.marcxml)), '',
SUBSTRING( biblioitems.marcxml,
LOCATE('<subfield code="c">', biblioitems.marcxml, LOCATE('<datafield
tag="245"', biblioitems.marcxml)) + 19,
LOCATE('</subfield>', biblioitems.marcxml, LOCATE('<subfield code="c">',
biblioitems.marcxml,
LOCATE('<datafield tag="245"', biblioitems.marcxml)) + 19)
-(LOCATE('<subfield code="c">', biblioitems.marcxml,
LOCATE('<datafield tag=&quot;245&quot;', biblioitems.marcxml)) + 19))) )
AS TitleSubtitle,
IF( LOCATE('&lt;datafield tag=&quot;650&quot;', biblioitems.marcxml) = 0 OR
LOCATE('&lt;subfield code=&quot;a&quot;>', biblioitems.marcxml,
LOCATE('<datafield tag="650"', biblioitems.marcxml)) = 0 OR
LOCATE('<subfield code="a">', biblioitems.marcxml,
LOCATE('<datafield tag="650"', biblioitems.marcxml)) >
LOCATE('</datafield>', biblioitems.marcxml, LOCATE('<datafield tag="650"',
biblioitems.marcxml)), '',
SUBSTRING( biblioitems.marcxml,
LOCATE('<subfield code="a">', biblioitems.marcxml, LOCATE('<datafield
tag="650"', biblioitems.marcxml)) + 19,
LOCATE('</subfield>', biblioitems.marcxml, LOCATE('<subfield code="a">',
biblioitems.marcxml,
LOCATE('<datafield tag="650"', biblioitems.marcxml)) + 19)
-(LOCATE('<subfield code="a">', biblioitems.marcxml,
LOCATE('<datafield tag="650"', biblioitems.marcxml)) + 19))) AS Subject,
CONCAT(biblioitems.place,"  ", biblioitems.publishercode,"  ",
biblio.copyrightdate) AS publisher,  items.itemcallnumber,
biblioitems.cn_suffix,items.itype,items.homebranch
FROM items LEFT JOIN biblioitems on
(items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on
(biblioitems.biblionumber=biblio.biblionumber) WHERE items.barcode  BETWEEN
'[Barcode]' AND '[barcpde]'
ORDER BY items.barcode ASC

Thanks & Regards
Nikunj Tyagi, DPL



--
View this message in context: 
http://koha.1045719.n5.nabble.com/SQL-report-650-a-tp5782646p5782749.html
Sent from the Koha-general mailing list archive at Nabble.com.
_______________________________________________
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha