Report "Bib records with 008 DATE1 may contain invalid "u" data" results do not match Access

[gibsonjc]

Number 4 from Issue #137 Local Catalog Maintenance > Bibliographic Records > Fixed Fields > Bib records with 008 DATE1 may contain invalid "u" data

For KNXdb on DEVEL: Web Reports returns 113 results Access returns 14 results

For BENdb on DEVEL: Web Reports returns 269 results Access returns 29 results

I can't quite put my finger on what is going wrong.

[dmcmpbll]

The only particular difference between the SQL in Access vs. Web Reports that I saw is in the WHERE line.

The Web Reports says: WHERE ((BIB_TEXT.PUBLISHER_DATE Is Not Null =2 parenthesis Access says: WHERE (((BIB_TEXT.PUBLISHER_DATE) Is Not Null =3 left parenthesis and a right parenthesis

I added in this set of parenthesis, but the results still differ (for ADL, Access returns 4; web reports returns 11).

Will keep looking.

[dmcmpbll]

Hm--- If I copy the SQL directly out of Web Reports and paste it into Access (without changing the % to * or the single quotes to double quotes), it runs and finds 11 results in ADL-devel (which matches what it finds in Web Reports).

The only edit I made was taking out the "$." before the table names.

The * in Access is supposed to be synonymous to the % in SQLPlusI had thought, meaning "zero or more characters".

[patrickzurek]

The * is analogous to %, you're right. I glanced at the sql, I'm assuming that query has a bunch of regexp meta symbols that need to be escaped but aren't. If I have time I'll take a longer look before the meeting.

This might help though: http://dbaontap.com/2015/01/10/pattern-matching-regular-expressions-part-1-meta-characters/

[tschwitz]

Here's the definition of the query from the Maintenance priorities page:

This query looks for bib records where the 260 $c is not null, does not contain “u” or “?” or “-“, and the fixed field DATE1 contains at least one “u”. Identifies bib records that could be edited to include more specific DATE1 data and for which 260$c contains more specific information. This query may retrieve some valid DATE1 data.

The ideal for the query is to return bib records that have a known, fixed date in the bib 260/264 $c, but a date value containing a "u" in the 008 Date 1. As noted in the definition, the query may retrieve some valid DATE1 data. Both sets of Debbie's results include two legitimate hits, and the rest are false drops.

The reality is that neither query is ideal. Both leave false drops, and we don't want to increase that. I think we need a better tool than the list of AND … NOT LIKE … wildcards to get what we really want.

[gibsonjc]

At our meeting on 1/8/2018, @dmcmpbll said she would take a first crack at trying to make this perform like the Access report since it is better.

[dmcmpbll]

@tschwitz and I talked about this query. @gibsonjc, will you update this issue to "inactive"; the shared sql for this report could use re-written, but re-writing this query is not a priority at this time. We can make this issue active again in the future should the original shared SQL be rewritten.