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 for bib 090 and 856; LDR bytes 07-08

    SQL for bib 090 and 856; LDR bytes 07-08

    1. Last updated
    2. Save as PDF
    3. Share
      1. Share
      2. Tweet
      3. Share
    No headers

     

    • Product: Aleph
    • Product Version: 20, 21, 22, 23
    • Relevant for Installation Type: Dedicated-Direct, Direct, Local, Total Care

     

    Description:
    I need to use SQL searches on bibliographic information to generate statistics. All bibliographic data seems to be in one very long field (Z00_DATA), and I am wondering how I can search among these bibliographic informations. Is there a more "friendly" file? :-)

    For example:

    These requests concern electronic resources, with no document records (meaning also no z30 records).

    1) Total of records with 090 $s not empty and with tag 856 $u empty.

    2) Total of records with 'BDD' in 090 $s and tag 856 $u empty.

    3) Among all records with tag 090 subfield s not empty, total of records for each 090 $ s values.

    How can I search in z00 file?

     

    Resolution:
    The z00r is the more "friendly" file. You would do: 

    > s+ abc01

    and then the following SQL:

    SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ count (*) from z00r 
    where Z00R_DOC_NUMBER in 
    (
    select /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ Z00R_DOC_NUMBER FROM abc01.Z00R WHERE SUBSTR(Z00R_FIELD_CODE,1,3) = '090'
    minus 
    SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ Z00R_DOC_NUMBER FROM abc01.Z00R WHERE SUBSTR(Z00R_FIELD_CODE,1,3) = '856'
    );


    SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ count (*) from z00r 
    where Z00R_DOC_NUMBER in 
    (
    select /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ Z00R_DOC_NUMBER FROM abc01.Z00R WHERE SUBSTR(Z00R_FIELD_CODE,1,3) = '090'
    and Z00R_TEXT like '%BDD%'
    minus 
    SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ Z00R_DOC_NUMBER FROM abc01.Z00R WHERE SUBSTR(Z00R_FIELD_CODE,1,3) = '856'
    );


    For #3, you would need to do the above for each different $$s, such as:

    SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ count (*) from z00r 
    where Z00R_DOC_NUMBER in 
    (
    select /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ Z00R_DOC_NUMBER FROM abc01.Z00R WHERE SUBSTR(Z00R_FIELD_CODE,1,3) = '090'
    and Z00R_TEXT like '%PEL%'
    minus 
    SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ Z00R_DOC_NUMBER FROM abc01.Z00R WHERE SUBSTR(Z00R_FIELD_CODE,1,3) = '856'
    );

     

    Additional Information
    [More from site:] Is it possible to mix information between abc01 z00r and abc50 z30 ? 
    For example : I want to count the number of documents which do not have the value 'Micro...' anywhere in the call number (z30_call_no not like 'Micro...'), and group it (total) for each bibliographic value contained in LDR 07-08 eg. am, as, etc. I cannot use Material type table (eg. LIVRE, ISSUE, CARTE, etc.) since these codes are not precise enough.

    I tried using Z13U to use bib. information with z30, but it did not work out. 

    Answer:

    I think the following will give you what you need....

    abc01@ALEPH20> select /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ substr (z00r_text,7,2), count(*) from z00r, z103, abc50.z30
    2 where z103_rec_key like 'ABC50%' and substr (z103_rec_key,6,9) = substr (z30_rec_key,1,9)
    3 and z30_call_no not like '%Micro%' and substr (z103_rec_key_1,6,9) = z00r_doc_number and z00r_field_code like 'LDR%'
    4 group by substr (z00r_text,7,2) order by substr (z00r_text,7,2) asc;

    **** Hit return to continue ****
    SUBSTR COUNT(*)
    ------ ----------
    aa 46
    ac 36
    ai 21
    am 1775292
    as 770249
    bm 3
    cb 1
    cc 64
    cm 37665
    cs 12
    dm 355
    em 893
    fm 4
    gm 54562
    im 2182
    is 1
    ja 2
    jc 6
    jm 37163
    js 3
    km 186
    mm 1656
    ms 822
    om 687
    rm 1416
    tm 16196

     

     


    • Article last edited: 12-Mar-2016
    View article in the Exlibris Knowledge Center
    1. Back to top
      • SQL FIRST / LAST functions
      • SQL for BIB Records Linked to Multiple HOLs or Multiple ADMs
    • Was this article helpful?

    Recommended articles

    1. Article type
      Topic
      Language
      English
      Product
      Aleph
    2. Tags
      This page has no tags.
    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