[Koha] Koha 16.05 - SQL Report on Koha Control Numbers - Finding blank 001s

[Paul A]

On 2/8/2017 11:13 AM, Craig Butosi wrote:
> Hi all,
>
> Koha 16.05 o Ubuntu 14.04 LTS
> Package install
>
> Would a kind soul help me with a basic SQL report which compiles a list of all bibliographic records that have a blank 001, but which have a biblionumber? Columns selected for the report = biblionumber, title, author.
>
> I have tried a number of queries but can't seem to get the syntax correct.

We have one that just gives you "clickable links" to allow editing the 
biblio:

SELECT GROUP_CONCAT('<a target="_blank" href=\"/
cgi-bin/koha/cataloguing/addbiblio.pl?biblionumber=',biblionumber,'\">',biblionumber,'</a>' 
SEPARATOR ', ')
AS BibNums,
EXTRACTVALUE(marcxml,'//controlfield[@tag="001"]') AS Id FROM biblioitems
GROUP BY Id HAVING count(Id) = 0;

Best -- Paul