yoonus para wrote:

Dear all,

How to create a SQL report using field 650 a with barcode and title,Please help me to create this report.

Thanks Yoonus

Did you find a solution? I thought it might be interesting, but I also can't figure it out. When I run SELECT * FROM marc_subfield_structure WHERE tagfield = 650 AND tagsubfield = 'a' it tells me the "kohafield" is bibliosubject.subject, yet it seems that I don't have a table called bibliosubject. Is that normal?

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="245"', biblioitems.marcxml)) + 19))), IF( LOCATE('<datafield tag="245"', biblioitems.marcxml) = 0 OR LOCATE('<subfield code="p">', 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="245"', biblioitems.marcxml)) + 19))),IF( LOCATE('<datafield tag="245"', biblioitems.marcxml) = 0 OR LOCATE('<subfield code="b">', 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="245"', biblioitems.marcxml)) + 19))), IF( LOCATE('<datafield tag="245"', biblioitems.marcxml) = 0 OR LOCATE('<subfield code="c">', 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="245"', biblioitems.marcxml)) + 19))) ) AS TitleSubtitle, IF( LOCATE('<datafield tag="650"', biblioitems.marcxml) = 0 OR LOCATE('<subfield code="a">', 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.