Zero results for SQL query involving z303 or z308 table
- Article Type: General
- Product: Aleph
- Product Version: 20
Problem Symptoms:
Site has bib library ABC01, ADM library DEF50, and usr_library ABC50. The following SQL receives zero hits when it should receive one hit:
select count(*)
FROM DEF50.z103, ABC01.z13, DEF50.z30, DEF50.z36, DEF50.z308, DEF50.z303
WHERE z36_rec_key = z30_rec_key and
(Z13.Z13_REC_KEY = Z103.Z103_LKR_DOC_NUMBER AND
SUBSTR(Z30.Z30_REC_KEY,1,9) = SUBSTR(Z103.Z103_REC_KEY,6,9)) and
(rtrim(z303_rec_key) = rtrim(z308.z308_id) and z308_rec_key like '03%') and
z36_status = 'L' and
rtrim(z36_id) = rtrim(z308_id) and
(z36_due_date >= '20080101' and z36_due_date <= '20140612') and
z36_rec_key = '000393974000010';
Cause:
The z303 and z308 (and z304 and z353) tables are in the $usr_library, *not* the ADM library. The SQL: "FROM DEF50.z103, ABC01.z13, DEF50.z30, DEF50.z36, DEF50.z308, DEF50.z303" is incorrect.
Resolution:
Correct SQL to: FROM DEF50.z103, ABC01.z13, DEF50.z30, DEF50.z36, ABC50.z308,ABCZ50.z303
Additional Information
Though there are z303 (Global patron), z304 (Patron address), z308 (Patron ID), and z353 (Patron index) tables in DEF50, there should *not* be.
The system looks for these tables in the $usr_library, which is ABC50. It never looks for them in the ADM library.
These def50 z303, z304, z308, and z353 tables should be dropped to avoid confusion.
Category: Circulation (500)
- Article last edited: 9/3/2014