Transcript Using Universal Borrower (UB) Tables in Access Queries

What's Where: Using the
Voyager Class Diagrams and
Data Dictionary With Access
Queries
ELSUG
October 9, 2008
Cathy Salika
CARLI Consortium of Academic and Research Libraries in Illinois
1
Voyager is a relational database
A relational database is a bunch of tables.
A table looks like a spreadsheet.
2
Voyager has about 400 tables
Some are things you probably expect
PATRON, ITEM, CIRC_TRANSACTION
Some are less obvious
INV_LINE_ITEM_NOTES
Some are used to look up common codes
LOCATION, FINE_FEE_TYPE
3
The _ID Fields
Many tables have a sequentially assigned number
in a column with “_ID” in its name.
4
The _ID Fields
Sometimes the relationship is made directly.
5
The _ID Fields
Sometimes there’s an intermediary.
6
Starting to Write a Query
First you have to figure out
•what data you need
•what tables the data are in
•how to correctly relate those tables
This is easier if you know about Voyager
because you use it regularly.
7
Starting to Write a Query
A good query
•is built a few tables at a time
•is tested at each step of the way
•has no extraneous tables
8
Starting to Write a Query
There are two tools to show you what’s in the tables and
how the tables relate.
Voyager Class Diagrams
available on SupportWeb
Voyager Data Dictionary
in the Voyager Technical User’s Guide
But I recommend that you use the CARLI version of
these documents.
9
CARLI’s
Class
Diagrams
10
CARLI’s
Data
Dictionary
11
CARLI’s
Data
Dictionary
Lists the
columns in
alphabetical
order (except
that the _ID
fields come
first)
12
CARLI’s
Data
Dictionary
Serves as an
index to the
class diagrams
13
CARLI’s
Data
Dictionary
Tells you which
fields are in
UNICODE so
you can format
them properly
14
CARLI’s
Data
Dictionary
Includes
comments on
what’s in the
fields
15
CARLI’s
Data
Dictionary
Provides lots of
tips about
where to find
things.
16
CARLI’s
Data
Dictionary
Gives warnings
about tables to
avoid
17
CARLI’s
Data
Dictionary
Provides help
with tricky links
18
Where to Get Them
CARLI’s Data Dictionary and the additional Class
Diagrams
•Are in SupportWeb, in the KnowledgeBase.
Search for “CARLI” and you’ll find it.
•Will soon be in the EL Commons
•Are on these flash drives
19
Views
Both versions of the Data Dictionary include views.
Their names end with _VW
Inside Voyager, a view is actually a query, but you can use
them as if they were tables.
Some views are helpful, some are inefficient, some are
WRONG.
So let’s see which are which.
20
Good Views
Call Nos.
DEWEYCLASS_VW
LCCLASS_VW
NLMCLASS_VW
SUDOCCLASS_VW
UDCCLASS_VW
A call number is included in a view if the CALL_NO_TYPE
in MFHD_MASTER says it should be there.
21
Good Views
Fixed Field
Data for Bibs
MARCBOOK_VW
MARCCOMPUTER_VW
MARCMAP_VW
MARCMUSIC_VW
MARCSERIAL_VW
MARCVISUAL_VW
22
Inefficient
Views
CIRCCHARGES_VW
CIRCRENEW_VW
FUNDLEDGER_VW
ISSUES_VW
ITEM_VW
SERIALS_VW
Why I don’t like them
•Some use a dozen tables or more,
so queries that use them are slow.
•You have to know too much to
understand their quirks.
(CIRCCHARGES_VW drops
charge transactions if the item
has been deleted.)
•It’s too hard to make your statistics
consistent.
23
If you want to know what the views do
and you have access to SQLPLUS on your server
and you don’t mind reading a bit of SQL…
here’s how.
SQL>
SQL>
SQL>
SQL>
SQL>
set heading off
set long 4500
column view_name fold_a newl
column text newl newl newl newl
select view_name, text from all_views
where owner=‘XXXDB’ order by view_name;
24
Bad Views
AUTHBLOB_VW
BIBBLOB_VW
MFHDBLOB_VW
They don’t work correctly for long
records.
When to use them:
NEVER
25
And now, a commercial…
If you’re new to query writing in Voyager,
consider attending 2 other sessions:
“Scary Queries Laid to Rest” by Jean Vik at
8:30am
“Idiomatic Access Reports” at 9:30am
26
Questions?
Thank you!
27