• 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