• Article Type: General
• Product: Aleph
• Product Version: 20, 21, 22, 23

Description:
I've been asked to create a report that shows:
STA
Author
Title
Year (copyrightdate)
Call#
Last Loaned Date

I need to be able to filter it on any one of the fields, so a simple .txt file that I can bring into Excel or Access would work best. I cannot find a canned report to do this and my systems help cannot figure it out either. Can this report even be created using standard SQL and the Oracle databases? If so, how?

Resolution:
This SQL will do most of what you need:

select unique Z13_author, Z13_TITLE, z13_year, Z30_CALL_NO, z30_date_last_return from ABC01.Z13, Z30, Z103 where 'ABC01' || Z13_REC_KEY = Z103_REC_KEY_1 and substr (Z103_REC_KEY,1,14) = 'ABC50' || substr (Z30_REC_KEY,1,9);

The STA is a problem. The STA field is only in the bib z00 and the z00r. The z00 cannot be read by SQL and abc01 does not have the z00r (-- the abc01 tab100 has "CREATE-Z00R=N"). It may be that the Z30_item_process_status would give you some of the values you want? (If so, you could simply add it to the select statement shown above.)