sql report for bibliographic records with more than one subject
Hi all! Im trying to make a report that will have in one column all subjects of the bibliographic record separated by a 'semicolon', so it can be saved as csv file. I'm trying the following: SELECT ExtractValue(marcxml,'//datafield[@tag="245"]/subfield[@code="a"]') AS TITLE, ExtractValue(marcxml,'//datafield[@tag="245"]/subfield[@code="b"]') AS TITLE2, ExtractValue(marcxml,'//datafield[@tag="246"]/subfield[@code="a"]') AS TITLE3,ExtractValue(marcxml,'//datafield[@tag="245"]/subfield[@code="c"]') AS AUTHOR, ExtractValue(marcxml,'//datafield[@tag="581"]/subfield[@code="a"]') AS Source, ExtractValue(marcxml,'//datafield[@tag="590"]/subfield[@code="a"]') AS Date_Approved, ExtractValue(marcxml,'//datafield[@tag="653"]/subfield[@code="a"]') AS Subjects FROM biblioitems LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) WHERE biblioitems.itemtype='LAWS' ORDER BY biblio.title asc However, with this query it puts in one column all subjects separated by space. I'm trying out group_concat with separator, but still to no avail. Anybody in the group who had the same query? Thanks in advance and cheers! -- View this message in context: http://koha.1045719.n5.nabble.com/sql-report-for-bibliographic-records-with-... Sent from the Koha-general mailing list archive at Nabble.com.
So I researched on this for the past few days, scouring the internet, only to find partly the answer in mysql-xml documentation (http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html), I came up with the following: SELECT ExtractValue(marcxml,'//datafield[@tag="245"]/subfield[@code="a"]') AS TITLE, ExtractValue(marcxml,'//datafield[@tag="245"]/subfield[@code="b"]') AS TITLE2, ExtractValue(marcxml,'//datafield[@tag="246"]/subfield[@code="a"]') AS TITLE3,ExtractValue(marcxml,'//datafield[@tag="245"]/subfield[@code="c"]') AS AUTHOR, ExtractValue(marcxml,'//datafield[@tag="653"]/subfield[@code="a"][1]') AS Subject_1, ExtractValue(marcxml,'//datafield[@tag="653"]/subfield[@code="a"][2]') AS Subject_2, ExtractValue(marcxml,'//datafield[@tag="653"]/subfield[@code="a"][3]') AS Subject_3, ExtractValue(marcxml,'//datafield[@tag="653"]/subfield[@code="a"][4]') AS Subject_4, ExtractValue(marcxml,'//datafield[@tag="653"]/subfield[@code="a"][5]') AS Subject_5 FROM biblioitems LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) WHERE biblioitems.itemtype='LAWS' ORDER BY biblio.title asc. So what this do is query field/tag 653a - uncontrolled vocab, adding [1] will bring the first 653a tag/subject. However, it did not answer my first query of putting in 1 column all the subjects separated by a dilemeter, like this: Railroad travel; Roads -- Surveying -- Finance. The query instead had column 1 Subject 1, column 2 Subject 2, up to Subject 5. Provided there are only up to five subjects to each bilbliorecord, and you want each subject in one column, this would suffice. So, I'll still try to find solution to my original query, I'm thinking of combination of stored procedure, substring_index (?) as what I have read so far. Or concatenate the extracted values, but it will make the query long. That's all for now. Cheers to all! -- View this message in context: http://koha.1045719.n5.nabble.com/sql-report-for-bibliographic-records-with-... Sent from the Koha-general mailing list archive at Nabble.com.
participants (1)
-
schnydszch