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 bgkrie...@gmail.com On Wed, Jul 23, 2014 at 7:45 PM, Elaine Bradtke <e...@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@lists.katipo.co.nz > http://lists.katipo.co.nz/mailman/listinfo/koha > _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha