[Koha] Report to find biblios without dates in 008 - needs work

[Bernardo Gonzalez Kriegel]

Hi Elaine,

First, the query must use HAVING instead of WHERE,
Date is not a column but an alias to some result.

Second, better to check that Date is not a number

SELECT biblionumber,
SUBSTR(ExtractValue(m.marcxml,'//controlfield[@tag="008"]'),8,4) as Date
FROM biblioitems m LEFT JOIN biblio b USING (biblionumber)
HAVING Date NOT REGEXP '[0-9]+'

Bernardo



-- 
Bernardo Gonzalez Kriegel
bgkriegel at gmail.com


On Wed, Jul 23, 2014 at 7:45 PM, Elaine Bradtke <eb at efdss.org> wrote:

> I'm halfway there:
>
> SELECT biblionumber,
> SUBSTR(ExtractValue(m.marcxml,'//controlfield[@tag="008"]'),8,4) as Date
> FROM biblioitems m
> LEFT JOIN biblio b USING (biblionumber)
> WHERE Date = '||||'
>
> Doesn't quite work.  If I omit the last line, it spits out a list of all
> our biblios, and the date.
> I'd like to limit the results to just the biblios with |||| in the first
> date section of the 008
> WHERE (Date = '||||') didn't work.
> NULL in place of '||||' doesn't work either.
> WHERE (ExtractValue(m.marcxml,'//controlfield[@tag="008"]'),8,4) = '||||'
> also didn't work.
>
>
> I've exhausted my cut and paste abilities. . . .
>
>
> --
> Elaine Bradtke (What I don't know about SQL would fill a few books)
> Data Wrangler
> VWML
> English Folk Dance and Song Society | http://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
> ---------------------------------------------------------------------------
> "Writing about music is like dancing about architecture"
> --Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha
>