Forwarding this to the list. Joel's suggestion fixed the problems. Elaine VWML <https://vwml.org>
---------- Forwarded message --------- From: Coehoorn, Joel <[email protected]> Date: Tue, May 10, 2022 at 11:20 AM Subject: Re: [Koha] Report help needed To: Elaine Bradtke <[email protected]> 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 <[email protected]> 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 > [email protected] > Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha > _______________________________________________ Koha mailing list http://koha-community.org [email protected] Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
