Limiting ITEM by item type
Submitted by ckeene on Tue, 2009-06-16 16:21.
Hi
I'm sure this is a very basic question, but can't work it out.
I have the following SQL, which will list each item in the ITEM table, (but only ifthe item has been loaned out at all).
(@transactions) = &sql($d,"
SELECT I.ITEM_ID, W.CONTROL_NUMBER, W.TITLE_DISPLAY, W.AUTHOR_DISPLAY, W.PUBR_DISPLAY, W.PUB_DATE
FROM WORKS W, ITEM I
WHERE W.WORK_ID = I.WORK_ID
AND I.ITEM_ID IN (SELECT ITEM_ID FROM LOAN)
");
All well and good.
But I want to limit it to just books.
What's the best way to do this, I know of some basic hacky ways (like trying to make sure thre control_number looks like an isbn) but would like to do this properly :)
Thanks for any help received!
Chris Keene
Limiting items by item type
You don't mention how you define an item as a book so I am going to assume it is an item type.
The item type is held in the ITEM.TYPE_ID attribute. This is a value rather than the code you see in Alto. To get the value you need to check the TYPE_STATUS table for the item type sub type which is 1
Run the following SQL to get the values for all item types and
select TYPE_STATUS,CODE,NAME from TYPE_STATUS where SUB_TYPE=1select the TYPE_STATUS attribute value for Books e.g. 2
In the SQL you supplied you would then do
(@transactions) = &sql($d,"
SELECT I.ITEM_ID, W.CONTROL_NUMBER, W.TITLE_DISPLAY, W.AUTHOR_DISPLAY, W.PUBR_DISPLAY, W.PUB_DATE
FROM WORKS W, ITEM I
WHERE W.WORK_ID = I.WORK_ID
AND I.TYPE_ID=2
AND I.ITEM_ID IN (SELECT ITEM_ID FROM LOAN)
");
If there is some other criteria for books then it could be more complicated.
On a wider note the SQL is probably not very efficient.
I would tend to do
select TYPE_STATUS,CODE,NAME from TYPE_STATUS where SUB_TYPE=1select the TYPE_STATUS attribute value for Books e.g. 2
In the SQL you supplied you would then do
(@transactions) = &sql($d,"
SELECT I.ITEM_ID, W.CONTROL_NUMBER, W.TITLE_DISPLAY, W.AUTHOR_DISPLAY, W.PUBR_DISPLAY, W.PUB_DATE
FROM WORKS W, ITEM I, LOAN L
WHERE W.WORK_ID = I.WORK_ID
AND I.TYPE_ID=2
AND I.ITEM_ID = L.ITEM_ID
");
It still may not be very quick although you don't mention performance so it might be a moot point. Depending on long it might be better to break things down within the Perl script rather than trying to do everything in a single query.
Thanks
Brian Crampton
Developer, Talis
Brian Thanks for this. It's
Brian
Thanks for this. It's actually related to a jisc project, following on from the JISC TILE project, and the work Dave Pattern did at Huddersfield in releasing circ usage data so help produce useful servers (such as recommender services). So this is a run-once script, speed not too important, but thanks none-the-less for the speed tip, always useful for future reference (I always presumed it was best to make the DB engine to do the heavy work rather than trying to do it in perl).
I don't manage our Talis LMS on a day to day basis, and have to confess I'm never quite sure what will be Library specific and what will be standard for all Talis customers.
In this case I presumed there was a standard field used by all which will hold something along these lines, either at the Work or Item level. (Of course, things like prism must use something to show the appropriate icon next to each item, presumably they use the MARC record, which isn't easy to integrate using SQL?)
For us, ITEM.TYPE_ID seems to be used for things like Long loans, short loans etc.
However, FORMAT_ID could be something to use, I'll look in to that (and talk to our cataloguer).
Thanks again
Chris Keene - University of Sussex.
Limiting ITEM by item type
Item type has a very specific meaning so that was what I was going with but it is reasonable straight forward to pick up on item format.
However the data is stored a little differently.
There is the ITEM.FORMAT_ID attribute which contains a code rather than than a numeric values as with item type.
The definition of these values are held in the ITEM_DESCRIPTION_TYPE table. This table holds different types of item description data: Format, Sequence, Size and Country. The type is defined by the attribute SUB_TYPE. Format is subtype 0, Sequence is subtype 1 etc.
So to see all item format codes you could run the following SQL
select * from ITEM_DESCRIPTION_TYPEwhere SUB_TYPE=0
However it is easier just to look in the Alto Configuration tool as the codes are displayed in Parameter Management | Names | Item | Formats. This is the same for the other item descriptors.
While some formats are shipped it is possible to modify and add these so it is difficult to say what value you require and in fact there may be a number of formats that form "Books"
The SQL would look something like the following given I am just guessing the format codes.
(@transactions) = &sql($d,"SELECT I.ITEM_ID, W.CONTROL_NUMBER, W.TITLE_DISPLAY, W.AUTHOR_DISPLAY, W.PUBR_DISPLAY, W.PUB_DATE
FROM WORKS W, ITEM I, LOAN L
WHERE W.WORK_ID = I.WORK_ID
AND I.FORAMT_ID in ("BK","HB")
AND I.ITEM_ID = L.ITEM_ID
");
In terms of performance it is usually worth avoiding subqueries unless the tables involved are not too big. Especially with the LOAN table you may find the SQL doing more work than you would expect.
Whether you use a single statement or PERL functionality depends on a range of issues. In the first instance go with the SQL and see how it runs but bear in mind that other approaches may be necessary.
Where you are going to have possible problems is where you are joining many tables together trying to achieve everything in one statement. In that case it may be easier to break down the selects on the database.
The other problem you may have is the space in tempdb for the SQL as you are looking at the whole LOAN table and so may fill up tempdb depending how Sybase is running the query, the size of the tables involved and what other SQL is running.
I am assuming you are testing the script and SQL on the MIS rather than the live server so you should have more control about what is being run but if you have Decisions people may have start running or have scheduled a job to run which may cause an unexpected impact.
Thanks
Brian Crampton
Developer, Talis
Hi SELECT I.ITEM_ID,
Hi
SELECT I.ITEM_ID, W.CONTROL_NUMBER, W.TITLE_DISPLAY, W.AUTHOR_DISPLAY, W.PUBR_DISPLAY, W.PUB_DATE
FROM WORKS W, ITEM I
WHERE W.WORK_ID = I.WORK_ID
AND I.FORMAT_ID in ('BK','HB','PB','PR')
AND I.ITEM_ID = L.ITEM_ID
AND I.ITEM_ID IN (SELECT ITEM_ID FROM LOAN)
Thanks, and again cheers for the background info, good practice with SQL and Sybase etc.
Looks good. Interestingly it didn't like adding the LOAN table in to the main SQL statement, ran out of memory (and took a while to do that), so have reverted back to doing a sub-select.
As an aside, noticed we have many with no format_id, might try and find out what type of things these are.
"select FORMAT_ID , COUNT(*)
from ITEM
GROUP BY FORMAT_ID"
FORMAT_ID
--------- -----------
NULL 1416
173049
AC 9741
[snip]
Chris Keene - University of Sussex.