Forwarding this to the list. Joel's suggestion fixed the problems. Elaine VWML <https://vwml.org> ---------- Forwarded message --------- From: Coehoorn, Joel <jcoehoorn@york.edu> Date: Tue, May 10, 2022 at 11:20 AM Subject: Re: [Koha] Report help needed To: Elaine Bradtke <eb@efdss.org> It could just be my email client, but I see line endings in the wrong place. It works on my system (in that at least I see data) if I don't change any code other than fixing formatting and line endings: SELECT authid, datecreated, authtypecode, concat(ExtractValue(`marcxml`,'//datafield[@tag="100"]/*'), -- PERSO_NAME ExtractValue(`marcxml`,'//datafield[@tag="110"]/*'), -- CORPO_NAME ExtractValue(`marcxml`,'//datafield[@tag="111"]/*'), -- MEETI_NAME ExtractValue(`marcxml`,'//datafield[@tag="130"]/*'), -- UNIF_TITLE ExtractValue(`marcxml`,'//datafield[@tag="148"]/*'), -- CHRON_TERM ExtractValue(`marcxml`,'//datafield[@tag="150"]/*'), -- TOPIC_TERM ExtractValue(`marcxml`,'//datafield[@tag="151"]/*'), -- GEOGR_NAME ExtractValue(`marcxml`,'//datafield[@tag="155"]/*') -- GENRE/FORM ) main_heading FROM `auth_header` WHERE datecreated BETWEEN <<(from yyyy-mm-dd)>> AND <<(to yyyy-mm-dd)>> ORDER BY datecreated, authtypecode, main_heading Note that MySql is picky about double-dash (--) comments, and requires whitespace after the comment characters. Joel Coehoorn Director of Information Technology York College of Nebraska On Tue, May 10, 2022 at 12:42 PM Elaine Bradtke <eb@efdss.org> wrote:
I imported the following report from Mana, but it doesn't work as is. I tried changing `marcxml` to `metadata` but it still didn't work. The error message (You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1) isn't specific enough for my limited abilities. I'd be grateful for your help.
Here's the original: SELECT authid, datecreated, authtypecode, concat(ExtractValue(`marcxml`,'//datafield[@tag="100"]/*'), -- PERSO_NAMEExtractValue(`marcxml`,'//datafield[@tag="110"]/*'), -- CORPO_NAMEExtractValue(`marcxml`,'//datafield[@tag="111"]/*'), -- MEETI_NAMEExtractValue(`marcxml`,'//datafield[@tag="130"]/*'), -- UNIF_TITLEExtractValue(`marcxml`,'//datafield[@tag="148"]/*'), -- CHRON_TERMExtractValue(`marcxml`,'//datafield[@tag="150"]/*'), -- TOPIC_TERMExtractValue(`marcxml`,'//datafield[@tag="151"]/*'), -- GEOGR_NAMEExtractValue(`marcxml`,'//datafield[@tag="155"]/*') -- GENRE/FORM) main_headingFROM `auth_header`WHERE datecreated BETWEEN <<(from yyyy-mm-dd)>> AND <<(to yyyy-mm-dd)>>ORDER BY datecreated, authtypecode, main_heading
Elaine Bradtke VWML <https://www.vwml.org> English Folk Dance and Song Society <https://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