SQL
Submitted by Julie Hart on Wed, 2007-06-20 16:03.
Hi
I'm trying to count how many records in our database have a TAG_NO=022 by doing:
select count (*) form WORK_SUBFIELD
where TAG_NO=022
I get 1500 but I know there are more than this, any suggestions?
Thanks
SQL question
The SQL looks OK (ignoring the typo of from)
select count(*)from WORK_SUBFIELD
where TAG_NO=022
1500 is a nice round number. You don't have a the number of rows returned limited do you? This can be done by the rowcount command e.g.
set rowcount 100
go
This would only return 100 rows from an SQL statement. It can be turned off with:
set rowcount 0
go
Thanks
Brian Crampton
Developer, Talis
sql
Hi Brian
No I didn't have rowcount set. I have just tried the query again on the main server and got 1498
SQL question
That was a long shot but nothing is jumping out that is wrong.
Are you using isql and not some other tool?
Also, at the risk of displaying my lack of cataloguing knowledge, why do you think there should be more and how many were you expecting (roughly)?
Thanks
Brian Crampton
Developer, Talis
sql
Hi Brian
Yes I'm using isql. What I wanted to do is find journal records that don't have the 022 field. The only way I could think of to do this was to get a list of control numbers with an 022 field using isql. I then ran a BO report for a list of control numbers with a journals sequence then cross referenced the two lists in Access so that the list from BO didn't have any records in it from the list produced by isql (with me so far?). I expected therefore that what I would have left is a list of only those records that didn't have an 022 field but when I checked a few I found that they did. So why the sql query didn't find them left me baffled.
The query I used to get the list of control numbers was:
select ws.WORK_ID, w.WORK_ID, w.CONTROL_NUMBER
from WORK_SUBFIELD ws, WORKS w
where TAG_NO=022
and ws.WORK_ID=w.WORK_ID
It returned 1500 rows
SQL question
Just to confirm - you checked the output of isql statement for the control number and WORK_ID directly for those in question? Also how were you checking the suspect works?
Did you run the SQL with
and ws.CONTROL_NUMBER like "{number}"Also have you searched for the work without the join e.g.
select WORK_ID from WORK_SUBFIELDwhere TAG_NO=022 and WORK_ID=????
There could be an issue with the join because of a data condition or something. The best approach to diagnostic is to break down the query to make it a simple as possible first.
Thanks
Brian Crampton
Developer, Talis
sql
Thanks Brian
I'll do some more investigating.
Julie
sql
When I do select * from WORK_SUBFIELD where WORK_ID=7055 it doesn't list the 022 field so my original query was correct but when I look at the record on Alto it does have an 022 field. Curiouser and curiouser
SQL and MARC tags
Are you looking at a MARC21 record in Alto?
It's possible that a work has an 022 field visible on-line when there's no 022 in WORK_SUBFIELD. One of the things the conversion from TalisMARC does is to check whether the number in the 001 of the TalisMARC record is an ISBN or ISSN, and if so, it creates the relevant field in the MARC 21 record (020 for ISBN, 022 for ISSN).
In TalisMARC it was very common to have an ISBN or ISSN as the 001, in which case it wasn't repeated in another field. So there would be no 022 field in the TalisMARC record, but there would in the MARC 21 record."
Questions about MARC21 conversion can be raised on the MARC21 Forum.
Thanks
Brian Crampton
Developer, Talis
sql and MARC tags
Hi Brian
I was looking at a MARC21 record but I'm confused now. If I'm looking at a MARC21 record that has an 022 field then why didn't the SQL query find it? Sorry to be such a pain!
SQL and MARC records
I'm not the best person on the MARC21 stuff but here goes.
The MARC21 record is held in a new table structure and uses blobs (a database data type) that Alto uses to display the record. However the TalisMARC record is still held in the WORK_SUBFIELD table but there are different rules between the two types of record which requires a conversion process with whole sets of rules.
The on-line documentation and/or postings to the MARC21 Forum might clarify the exact relationship.
Thanks
Brian Crampton
Developer, Talis