HUG Conference – Stirling 2000
My favourite SQL
University of Huddersfield
Hudds serials.qry This is Huddersfield’s variation on Jim Taylor’s query all locations in one sequence
Most of these are not very complicated – but a lot of them have things like report variables and derived fields.
Bstat List of borrowers at a particular BSTAT
Emailyes List of readers with Notice By Email set to Yes
Exborreq A list of expired borrowers who still have requests attached to their records
Finalist2 Expired borrowers listing the number of items they still have on loan.
ILL Items at ILL statuses – helps the ILL staff keep track of where they are.
Liverdrs Lists of readers who have borrowed items during a set time period to help with stats and SCONUL type returns.
Lmsubs Lost and lost-recall items with prices.
Lostmis2 Another listing of lost & missing books
Nobstats List all readers and sorts by Bstst so that empty bststat fields float to top – is there a better way?
Sconul1 Counts readers at Bstats
Slcall List of all lecturers with items in SLC (RBR) with page breaks between lecturers and courses.
Slclist Lists items in SLC (RBR) for a particular lecturer.
Slcodlst List of short loan items overdue. This is based on the reports which make up our email notices system.
Store3 List of items at a particular collection code.
Transit2 List of items in transit with a selection date so that you can look for items which have gone walkabout.
Finalyr1.rpt Produces printed notices to students who are about to finish their course at the University
Emainwrn.rpt The one which creates the email warning notices, and once the report has brought back data you need to click Tools/Macro on the Toolbar and then run the eholdnot macro to get the text file. Then use the basic program (which was in the handout I prepared for the HUG meeting in March) and blat.exe to send them.
SQL query files from Middlesex University
These files are all produced for running under ISQL.EXE, a program which requires a GO after every separate instruction. This will not be necessary under other methods of running the queries.
In our system these are run as follows:
isql -U{username} -P{password} -S{server} -i{filename as below} -o{OUTPUT file name}
856.SQL Produces a file with the contents of every 856 field with its bib number.
This could easily be adapted for any other field by changing 856 to the tag of the field you wish to printout.
856TITLE.SQL Produces a file with the title of every record which has an 856 field. This could easily be adapted to any other field by replacing 856 with the appropriate tag.
dewloc.sql This query produces a file of call number, bib# and title of all items in the Dewey range 34X. (i.e. 340 to 349.999)
Note that Dewey numbers are stored in the call number field in the item table without the decimal point but preceded by a figure 3. This actually indicates the number of digits before the decimal point.
So if you wish to change the query to the range 301.6 to 301.6999 you would enter '33016%' .
The % sign at the right of the searched string indicates right truncation. If you wanted to find anything with 492 anywhere in the string you could search for '%492%'
These are sorted (in the penultimate line) by call number.
locks.sql This sql file is run to find out if any process is locking the system.
loctitle.sql This produces a listing of titles with items at a particular location. To adapt to your needs replace QP with your location code.
The list is produced in alphabetical order because it takes the titles from a table which is ordered in that way: item_with_title
LOST.sql This query finds items lost before a certain date and lists the barcodes out by location
spanish.sql This query was produced in response to a request to produce a list of items in Spanish in one particular campus library. It checks the MARC 008 and 041 fields so is only as accurate as the data entry in those fields has been. These are coded data elements and are never seen by anyone other than the cataloguer so they may not always be very accurate.
SUBFIELD.SQL This prints out contents of specified subfields of a specified field.
745.SQL This query prints out the bib# and contents of every field where the tag is 745
245INDS.SQL This query selects every record which has a 245 field with second indicator 5 and prints out the bib number, indicators and text of the field. This is useful as the second indicator of MARC 245 field contains the number of non-filing characters at the start of the field (the title). So a title beginning with "A " should have 2 (a and the following space), "An " should have 3, "The " should have 4.
So what should have 5? Try this query on your own system and see what you retrieve! You can then present it to your Chief Cataloguer for comment (unless you can see justification in the results you get!).
subject.sql This query finds all bibliographic records with items attached at a particular campus (coded EN) which have subject fields. This was required for an exercise to ensure that all records had subject headings. The subject headings are stored in tags 600, 610, 611, 640, 650, 651, 660, 661 or 690
videos.sql This query was run to find items which are videos at a particular campus and assumes that videos will have the text vid in subfield $z of the 245 field.
A list of the records is produced with bib# and text of the 245 field.
MQLIST.SQL When we converted our data from our previous system, epixtech's predecessor Dynix provided a barcode for everything which did not have a barcode and the dummy barcodes began with mq. Later when we added serials we gave the serials records dummy barcodes as well. Recently we did a stockcheck and converted the status of all records to stchk. After the exercise anything with this code remaining unless it was a serial with a dummy barcode was 'suspect' so this list provides a list of these items.
This query lists everything with a barcode beginning with mq a collection code without a j (to exclude journals which all have collection codes containing j and item_status stchk. Epixtech would have written a similar query to identify the serials so that they could have their statuses put back to 'available' from stockcheck.
COUNTS There is no file for counts but they are very easy.
Sample provided in file on disk