Revise Cataloging maintenance-related reports OC6 A - J

[gibsonjc]

Ted has revised some of the Cataloging Maintenance Priorities (OC6.A through OC6.J) to broaden their scope to include 10-digit OCLC numbers [edited to remove the two "new" reports OC6K and OC6L; moved to #147]

10 to revise:

1. Bib record 035 $a begins OCoLC but has no digits (OC6.A)
2. Bib record 035 $a begins OCoLC but has invalid characters (OC6.B)
3. Bib record 035 $a has malformed OCoLC prefix (OC6.C)
4. Bib record 035 $a begins OCoLCocm but has more than 8 digits (OC6.D)
5. Bib record 035 $a begins OCoLCocm but lacks leading zeroes (OC6.E)
6. Bib record 035 $a begins OCoLCocn but less than 9 digits (OC6.F)
7. Bib record 035 $a begins ocmocm (OC6.G)
8. Bib record 035 $a contains second OCLC number (OC6.H)
9. Bib record 035 $a lacks ocm or ocn prefix (OC6.I)
10. Bib record 035 $a lacks OCoLC prefix (OC6.J)

I've updated the shared Google spreadsheet list, uploaded his text file with the sql on files.carli.illinois.edu:/home/fellows/stat_sql, and will also attach it here for convenience. OC6_sql_revisions_2018_tcs.txt

[csaundrs]

I have entered all these reports into web-reports-devel. I tested them and they seem to run. The Google Spreadsheet has been updated so that each of these currently reflects a "READY FOR TEST" status.

[jenhm]

I'll do the QA on these updated queries.

[jenhm]

When I run the report and get no results (1-3 on DPU for example) I get this error "DataTables warning: table id=d-tables - Ajax error. For more information about this error, please see http://datatables.net/tn/7", when the report returns results the error doesn't happen.

I received this error on "Bib Record 035 $a begins OCoLCon but has less than 10 digits" on UIU as well, since there are no hits.

[jenhm]

4. Bib record 035 $a begins OCoLCocm but has more than 8 digits (OC6.D). The Results are coming back in the wrong columns, even though from what I can see in the back end the data is assigned to the right column. For example LENGTH(DISPLAY_HEADING) - 10 is assigned to the Number of Digits Column, but below you can clearly see it is displaying the BIB_INDEX.DISPLAY_HEADING:

BIB ID | Title Brief | Number of Digits | Display Heading | Normalized Heading | Suppress in OPAC | Create Date 732050 | Federal Income Tax: Objective Multiple Choice Exams and Analysis | (OCoLC)ocm174272361 | OCOLC 17427236 | N | 2/8/2011 10:55 | 9

[jenhm]

3. Bib Record 035 $a has malformed OcoLC prefix.

The Server SQL Ted provided is missing one of the criteria that the website version has: OCOLC (all capital letters). I think if that is added to the SQL it will be fine.

[jenhm]

5. Bib record 035 $a begins OCoLCocm but lacks leading zeroes (OC6.E). Bib record 035 $a begins OCoLCocn but less than 9 digits Bib record 035 $a contains second OCLC number

Results are coming back in the wrong columns as noted for the bib record 035 $a begins OCoLCocm but has more than 8 digits above.

I suspect this may be because of a change in the server sql that was introduced to compensate for the 10 digit OCLC numbers?

Example:
BIB ID | Title Brief | Number of Digits | Display Heading | Normalized Heading | Suppress in OPAC | Create Date 16160 | Journal of abnormal psychology. | (OCoLC)ocm1605720 | OCOLC 1605720 | N | 4/21/2002 0:00 | 7

Example from "second OCLC number" query:

BIB_ID	TITLE_BRIEF	second035norm	second035disp	NORMAL_HEADING	SUPPRESS_IN_OPAC	CREATE_DATE
24530	Contemporary Southeast Asia.	OCOLC 5526448	(OCoLC)5526448	(OCoLC)ocm05526448	OCOLC 05526448	N	4/21/2002 0:00

[jenhm]

9. Bib record 035 $a lacks ocm or ocn prefix (OC6.I)

ran on DPU: received this error: DataTables warning: table id=d-tables - Requested unknown parameter '3' for row 0, column 3. For more information about this error, please see http://datatables.net/tn/4

when I clicked OK retreived 73 results (same as through Access), but columns were again showing incorrect/no data:

BIB ID	Title Brief	Number of Digits	Display Heading	Normalized Heading	Suppress in OPAC	Create Date
1223740	OCOLC 1056721	(OCoLC)1056721

[jenhm]

10. Bib record 035 $a lacks OCoLC Prefix (OC6.J)

Ran on DPU: no results received

Ran on UIU: received this error: DataTables warning: table id=d-tables - Requested unknown parameter '3' for row 0, column 3. For more information about this error, please see http://datatables.net/tn/4 Clicked OK retreived 17 results (retrieved 16 through Access, so need to investigate further), but the columns are incorrect from webreports:

BIB ID	Title Brief	Display Heading	Normalized Heading	Suppress in OPAC	Create Date
5073210	OC0LC OCM54707743	(OC0LC)ocm54707743

[jenhm]

Summary of findings:

1. Bib record 035 $a begins OCoLC but has no digits (OC6.A) -OK except when no results returns AJAX error "DataTables warning: table id=d-tables - Ajax error. For more information about this error, please see http://datatables.net/tn/7",
2. Bib record 035 $a begins OCoLC but has invalid characters (OC6.B) OK except when no results returns AJAX error "DataTables warning: table id=d-tables - Ajax error. For more information about this error, please see http://datatables.net/tn/7",
3. Bib record 035 $a has malformed OCoLC prefix (OC6.C) - OCOLC not included in SQL. when no results returns AJAX error "DataTables warning: table id=d-tables - Ajax error. For more information about this error, please see http://datatables.net/tn/7",
4. Bib record 035 $a begins OCoLCocm but has more than 8 digits (OC6.D) - Results in wrong columns
5. Bib record 035 $a begins OCoLCocm but lacks leading zeroes (OC6.E) - results in wrong columns
6. Bib record 035 $a begins OCoLCocn but less than 9 digits (OC6.F) - results in wrong columns
7. Bib record 035 $a begins ocmocm (OC6.G) - no results found in any institution
8. Bib record 035 $a contains second OCLC number (OC6.H) - results in wrong columns
9. Bib record 035 $a lacks ocm or ocn prefix (OC6.I) - data table error; results in wrong columns
10. Bib record 035 $a lacks OCoLC prefix (OC6.J) - data table error; results in wrong columns.

[jenhm]

9. Bib record 035 $a lacks ocm or ocn prefix (OC6.I) -SQL needs revision.
10. Bib record 035 $a lacks OCoLC prefix (OC6.J) - data table error; results in wrong columns. - SQL needs revision.

Both of these seem to contain incomplete SQL.

[jenhm]

1-8 ok. AJAX error not occurring on PROD for other queries. Found issue #93 and think perhaps fix was not put on devel.

[jenhm]

1-2: OK for Prod 10/29 3: OK for Prod 11/2: added OCOLC to webreports as it was not in original shared in Jan, but was in version in Shared SQL.

[jenhm]

4-7: OK for Prod 11/2 re-checked.

[jenhm]

8: Ok for Prod 11/2

[gibsonjc]

1-8 moved to Prod today. 9 and 10 still pending

[jenhm]

Debbie and I updated 9 and 10. We changed the name on 9 since it referenced the oclc prefixes "ocm and ocn", so also now references "on".

Because we were having trouble with the new SQL on DEVEL we realized: 9.) the SQL was fine since it was simply looking for (OCoLC)0-9 (so already was including on numbers) so we edited the SQL back to the original SQL, updated the title, and descriptions. 10.) the SQL just needed the prefix variations for "on" added, we also adjusted the description.

[gibsonjc]

9 and 10 in Prod