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

• 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