Skip to main content
ExLibris

Knowledge Assistant

BETA
 
  • Subscribe by RSS
  • Back
    Aleph

     

    Ex Libris Knowledge Center
    1. Search site
      Go back to previous article
      1. Sign in
        • Sign in
        • Forgot password
    1. Home
    2. Aleph
    3. Knowledge Articles
    4. SQL to connect course doc records with items?

    SQL to connect course doc records with items?

    1. Last updated
    2. Save as PDF
    3. Share
      1. Share
      2. Tweet
      3. Share
    1. Additional Information
    • Article Type: General
    • Product: Aleph
    • Product Version: 19.01

    Description:
    Is there any SQL that will connect course library doc records with the items that are linked to the courses? For version 19+?

    I had a query that used to do this in version 18, but it doesn't seem to work anymore, and I can't figure out quite why. It used Z103 to link to the ADM, then further limited items based on the reserves collection and item status.

    Resolution:
    You say "SQL that will connect course library doc records with the items that are linked to the courses".

    But, in fact, items are not linked to the courses directly. They are linked to doc records and doc records are linked to courses.

    The link from the doc record to the course is the xxx30 z11 CNO and CNO-S index entries.

    The link from the item to the doc record is via the z103 records:

    Case 1. The xxx50 item is linked to the xxx30 doc via an xxx01 record: there's an "SID L $$axxx01 ..." field in the xxx30 doc record.

    Case 2. The xxx50 item is linked directly to the xxx30: there's an "SID L $$axxx30 ..." field in the xxx30 doc record (linking it to the xxx30 "Connection record").


    Case 1:

    Reading doc : 000000206
    ...
    CNO L $$a216^^^^^^^^^^^^^^^^^0001
    SID L $$aUSM01-000029238-216


    util f/3/12:

    ENTER HOME LIBRARY : usm30
    ENTER DOC-NUMBER : 000000206
    USM50 000029238 ITM


    Reading usm50 doc : 000029238
    FMT L AD
    ...
    LKR L $$aADM$$lUSM01$$b000029238


    Case 2:

    Reading usm30 doc : 000000201
    ...
    PST L $$0Z30$$1000238856

    Reading doc : 000000202
    LDR L 00565nam--22001817a-4500
    CAT L $$aMASTER$$b99$$c20080409$$lUSM30$$h1129
    CNO L $$a212^^^^^^^^^^^^^^^^^0001
    SID L $$aUSM30-000000201-212


    util f/3/12:

    ENTER HOME LIBRARY : usm30
    ENTER DOC-NUMBER : 201
    USM50 000238856 ADM <------

    ENTER HOME LIBRARY : USM30
    ENTER DOC-NUMBER : 202
    USM50 000238856 ITM <------


    Reading usm50 doc : 000238856
    Load: /tmp/utf_files/exlibris/aleph/a19_4/usm50/tab/tab_expand
    FMT L AD
    ...
    LKR L $$aADM$$lUSM30$$b000000201

    Additional Information

    faq


    • Article last edited: 10/8/2013
    View article in the Exlibris Knowledge Center
    1. Back to top
      • SQL to change sublibrary in z305 records gets unique constraint error
      • SQL to find bib records without orders
    • Was this article helpful?

    Recommended articles

    1. Article type
      Topic
      Language
      English
      Product
      Aleph
    2. Tags
      1. 19.01
      2. contype:kba
      3. Prod:Aleph
      4. Type:General
    1. © Copyright 2025 Ex Libris Knowledge Center
    2. Powered by CXone Expert ®
    • Term of Use
    • Privacy Policy
    • Contact Us
    2025 Ex Libris. All rights reserved