[Koha] Report help needed

[Elaine Bradtke]

Forwarding this to the list.  Joel's suggestion fixed the problems.
Elaine
VWML <https://vwml.org>



---------- Forwarded message ---------
From: Coehoorn, Joel <jcoehoorn at york.edu>
Date: Tue, May 10, 2022 at 11:20 AM
Subject: Re: [Koha] Report help needed
To: Elaine Bradtke <eb at 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 at 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 at lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>