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

    FIND-C & CCL terms for new books don't match SQL

    • Article Type: General
    • Product: Aleph
    • Product Version: 20

    Problem Symptoms:
    SQL for recent items turns up more results than Web OPAC.

    **OPAC**

    The OPAC function is contained in /exlibris/aleph/u20_2/alephe/www_f_eng
    /head-1-nobor-abc01. The function is on line 71 and is as follows:
    &server_f?func=find-c&ccl_term=wnr+%3D+%28+newl+%29+and+wil%3D%28+201206*+or+201207*+or+201208*+or+201209*%29

    After translating the HTMLized characters, it looks like this:
    &server_f?func=find-c&ccl_term=wnr=(newl) and wil=(201206* or 201207* or 201208* or 201209*)

    **SQL**

    The SQL code on the database side to check the number of recent acquired items is:

    SQL> select z30_call_no, z30_description, z30_item_statistic,z30_inventory_number_date
    from z30
    where z30_inventory_number_date >= '20120601'

    Cause:
    There'd s discrepancy between the OPAC search terms and the SQL:

    &server_f?func=find-c&ccl_term=wnr=(newl) is looking for 946 fields with a value of "newl" while

    wil=(201206* or 201207* or 201208* or 201209*) is looking for item records with an z30_update_date of June-Sept. 2012

    The SQL is looking at the z30_inventory_number_date.

    Resolution:
    To make the OPAC search look like the SQL -- with the exception of the errors in the SQL noted below -- do the following:

    1. To limit results to sublibrary lpgen records, the "b lpgen" should be left as is.

    2. To use the inventory-date, change:

    N z30-update-date u zero X

    to:

    N z30-inventory-date u zero X

    3. Otherwise, change it to:

    N z30-open-date u zero X

    z30_update_date is definitely *not* the best choice: it changes with updates to the item.

    Additional Information

    sys01 tab00.eng has:

    H WNR W-022 00 11 Words NEWS or NEWL
    H WIL W-024 00 11 Words in LPTS Item

    sys01 tab11_word has:

    946## ... 03 WNR
    Z30-1 b lpgen bcmsopu 09 WIL

    The expand_doc_bib_z30 has:

    N z30-sub-library b blank N
    N z30-collection c space N
    N z30-material m blank X
    N z30-item-process-status s blank Y
    N z30-order-number o space X
    N z30-barcode p space X
    N z30-update-date u zero X

    Category: Web OPAC (500)

    Subject: Search (500)


    • Article last edited: 10/8/2013