SQL queries to retrieve barcode and call number for certain bib record values
- Article Type: General
- Product: Aleph
- Product Version: 18.01
Description:
Which ORACLE database do we use if we want to run SQL queries on the catalog?
For example we may want query the catalog like this:
1. select barcode, call number, title of serial records where ISSN is null (blank)
2. select barcode, call number, where MESH headings is not null
Resolution:
Values which are in the bib or HOL doc record only are not accessible via SQL since the variable fields in the z00 doc record are in blob (Binary Long OBject) format.
1. The barcode is in the ABC50 z30 record;
the call number is in the ABC50 z30 record (and in the ABC60 HOL record);
the title is in the ABC01 bib and z13 records; and
the serial-format is in the ABC01 bib and the ABC50 z30.
A variation on the SQL from KB 8192-939 can be used for this:
> s+ ABC50
SQL-ABC50> select Z13_TITLE, z30_call_no, Z30_BARCODE from ABC01.Z13, Z30, Z103 where Z13_REC_KEY = substr (Z103_REC_KEY_1,6,9) and substr (Z103_REC_KEY_1,1,5) = 'ABC01' and substr (Z103_REC_KEY,6,9) = substr (Z30_REC_KEY,1,9)
and z30_material = 'ISSUE' and z13_isbn_issn is null;
2. The 6xx fields are in the bib z00 only. It is possible to build z00r records, with each the field as a separate record and accessible by SQL.
To build the z00r, you would need to run the p_manage_07 GUI service on ABC01 specifying
Delete Short Records: No
Record Type: Update Z00R only
After doing that, you could run this SQL:
> s+ ABC50
SQL-ABC50> select z30_call_no, Z30_BARCODE from ABC01.Z00R, Z30, Z103 where Z00R_DOC_NUMBER = substr (Z103_REC_KEY_1,6,9) and substr (Z103_REC_KEY_1,1,5) = 'ABC01' and substr (Z103_REC_KEY,6,9) = substr (Z30_REC_KEY,1,9) and
Z00R_DOC_NUMBER in (select Z00R_DOC_NUMBER from ABC01.z00r where substr (Z00R_FIELD_CODE,1,2) = '65' and substr(Z00R_FIELD_CODE,5,1) = '2');
- Article last edited: 10/8/2013