Skip to main content
ExLibris
  • Subscribe by RSS
    • Back
    Aleph
    Ex Libris Knowledge Center

    OPAC includes issues not found in xxx50 z30 by SQL

    1. Last updated
    2. Save as PDF
    • Article Type: General
    • Product: Aleph
    • Product Version: 18.01

    Description:
    In Web OPAC, we have in find-e-code-include-abc01pub

    <option value=SCAN_CLD selected >Call Number </option>

    In ABC01/tab/

    tab00.eng says

    H CLD ACC 22 00 00 Mesh Call Num

    tab11_acc says

    LOC2# CLD

    In /abc01/tab/tab22

    ! 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
    !!!!!!!!!!-!-!!!!!-!-!!!!-!!!!!-!-!!!!-!!!!!-!-!!!!-!!!!!-!-!!!!-!!!!!-!-!!!!

    CALL-NO 1 LOC##

    So my test search is:

    SELECT substr(z30_call_no,1,30) AS CALL_NUMBER,
    TRIM(substr(Z13_TITLE,1,50)) AS TITLE,
    TRIM(substr(Z13_AUTHOR,1,50)) AS AUTHOR,
    Z13_YEAR AS YEAR,
    substr(z30_rec_key,10,5) AS CPY,
    Z13_IMPRINT AS IMPRINT
    FROM sth50.z30, sth50.z103, sth01.z13
    WHERE substr(z30_call_no,1,30) LIKE 'QW%'
    and substr(Z103_REC_KEY_1,1,5)='STH01'
    and substr(Z103_REC_KEY,6,9) = substr(Z30_REC_KEY,1,9)
    and Z13_REC_KEY = substr(Z103_REC_KEY_1,6,9) ORDER BY substr(z30_call_no,1,30);

    It finds :

    QW 4 M294 1974 Manual of clinical microbiology. 1974
    QW 4 M294 1980 Manual of clinical microbiology / editor-in-chi 1980
    QW 4 M294 1999 Manual of clinical microbiology / editor in chi 1999
    < six titles for QW 4 M294 >

    It does not find :

    QW 4 M294 2003 Manual of clinical microbiology 2003
    QW 4 M294 2007 Manual of clinical microbiology 2007

    How is it translating LOC## ?

    When I run this query, I only find SIX records:

    SELECT substr(z30_call_no,1,30) AS CALL_NUMBER FROM sth50.z30 WHERE substr(z30_call_no,1,30) LIKE 'QW 4 M294%';

    CALL_NUMBER
    --------------------------------------------------------------------------------
    QW 4 M294 1980
    QW 4 M294 1985
    QW 4 M294 1991
    QW 4 M294 1974
    QW 4 M294 1995
    QW 4 M294 1999

    So, when I only look at the z30 records, I still do not find records that the OPAC does.

    Resolution:
    The reason that the 2003 and the 2007 are not retrieved is different for each case.

    **2003**

    The "QW 4 M294 2003" is not retrieved because the z30_call_no includes a $$h:

    SQL> select substr(z30_call_no,1,30) AS CALL_NUMBER FROM sth50.z30 WHERE substr(z30_call_no,1,30) LIKE '%QW 4 M294%';

    CALL_NUMBER
    --------------------------------------------------------------------------------
    QW 4 M294 1980
    QW 4 M294 1985
    QW 4 M294 1991
    QW 4 M294 1974
    QW 4 M294 1995
    $$hQW 4 M294 2003
    $$hQW 4 M294 2003
    QW 4 M294 1999

    The form *with* the $$h is really the correct form: When the item is linked to an HOL record, the HOL 852 call number controls the z30_call_no -- including the $$h. SQL shows that *all* of these items are linked to HOL records:

    SQL> select z30_rec_key, z30_hol_doc_number_x FROM sth50.z30 WHERE substr(z30_call_no,1,30) LIKE '%QW 4 %M294%';

    Z30_REC_KEY Z30_HOL_D
    --------------- ---------
    000021364000010 000022855
    000040682000010 000042579
    000063143000010 000065797
    000049949000010 000052024
    000082401000010 000085791
    000094550000010 000098272
    000094550000020 000098272
    000091441000010 000094954

    and, therefore, should have the $$h. KB 8192-1703 describes a method for correcting these z30_call_no's. I suggest that you perform the steps described in this KB record.


    **2007**

    The "QW 4 M294 2007" is not retrieved because there is no z30 for this issue. There's a STH60 HOL record (000103519), but,

    as the following SQL shows there is no item for this HOL record:

    SQL> select z30_rec_key from z30 where z30_hol_doc_number_x = '000103519';

    no rows selected

    You should try to determine why no z30 was created.

    • Article last edited: 10/8/2013