SQL retrieves all items for an ADM record, rather than just a specific item
- Article Type: General
- Product: Aleph
- Product Version: 20
Problem Symptoms:
An SQL query which should retrieve just a specific item or items for a particular ADM record retrieves all items for the ADM record. See example in Additional Information.
Cause:
The item is being JOIN'ed based on just the first 9 digits (the ADM#) of the z30_rec_key:
JOIN HPL50.z30 ON substr(z31_key,1,9) = substr(z30_rec_key,1,9)
This means that all items for this ADM record will be included.
Resolution:
Include the entire 15-digit item key in the JOIN:
JOIN HPL50.z30 ON substr(z31_key,1,15) = substr(z30_rec_key,1,15)
Additional Information
Example:
SELECT "Patron Status",
"Patron Barcode",
"Name",
"Title",
"Barcode",
( D - C ) / 100 "Amount Owed"
FROM (
SELECT ( SELECT Z305_BOR_STATUS
FROM HPL50.Z305
WHERE Z305_REC_KEY = Z303_REC_KEY || 'HPL '
) "Patron Status",
( SELECT substr( Z308_REC_KEY, 3 )
FROM SYS00.Z308
WHERE Z308_ID = Z303_REC_KEY
AND substr( Z308_REC_KEY, 1, 2 ) = '01'
AND Z308_STATUS = 'AC'
) "Patron Barcode",
Z303_NAME "Name",
z13_title "Title",
z30_barcode "Barcode",
nvl( sum( decode( Z31_CREDIT_DEBIT, 'C', Z31_SUM ) ), 0 ) "C",
nvl( sum( decode( Z31_CREDIT_DEBIT, 'D', Z31_SUM ) ), 0 ) "D"
FROM HPL50.Z31
INNER JOIN SYS00.Z303 ON substr( Z31_REC_KEY, 1, 12 ) = Z303_REC_KEY
JOIN HPL50.z30 ON substr(z31_key,1,15) = substr(z30_rec_key,1,15)
JOIN SYS01.Z13 ON substr(z31_key,1,9) = Z13_REC_KEY
WHERE Z31_STATUS = 'O'
AND Z31_SUB_LIBRARY LIKE 'HPL '
and z303_name like 'ECHEVERRIA, ELIZ%'
GROUP BY Z303_REC_KEY,
Z303_NAME_KEY,
Z303_NAME,
Z31_KEY,
z13_title,
z30_barcode
ORDER BY 1, Z303_NAME_KEY, 5
)
WHERE ( D - C ) > 00000;
Category: Circulation (500)
- Article last edited: 12/11/2014