IUG FORUM

Stay always connected!
  1. Nicole Allen
  2. Sierra/ Millennium/ Encore
  3. Monday, 24 September 2018
  4.  Subscribe via email
I am in the process of automating our circulation stats by call number and location. For some reason I am having some things not show up in my SQL query that are showing up in web management reports. I dumped everything with a checkout of yesterday to see what was happening. When I dumped the list it looks like my issue is with items that have a item_record_property.call_number (item call number) as blank. All other items automatically fill that field with the bib call no or display the actual item call no. The items that I'm having an issue with (and there are very few) have no item call no. in the record, but for some reason in SQL it is showing up as blank and not populating the field from the bib call no. Any idea what is going on? Here is a the query I'm using to dump the checkouts and compare the call no. fields:

I need to search on the item and bib call number to get accurate stats so ignoring the item record call no. isn't an option. Thanks!

select
itemcall.call_number,
call.index_entry,
item.record_num

From
sierra_view.bib_record as bib
Join sierra_view.circ_trans as circ on circ.bib_record_id = bib.record_id
Join sierra_view.phrase_entry as call on call.record_id = bib.record_id
Join sierra_view.item_view as item on item.id = circ.item_record_id
Left Join sierra_view.item_record_property as itemcall on itemcall.item_record_id = item.id

where circ.op_code = 'o' and
circ.transaction_gmt> (now() - interval '1 day') and call.varfield_type_code = 'c' and
circ.item_location_code like 'gm%' ;

nallen@greenwichlibrary.org
Comment
There are no comments made yet.
Accepted Answer Pending Moderation
0
Votes
Undo
I hadn't heard back and eventually escalated this, and they arranged for someone from the training team got back to me. The majority of our call numbers that don't have item specific call numbers DO have an entry in the item property call number field. It is less than 1% of our records that don't have this entry.

The person from the training team, who seemed quite knowledgeable about SQL and its structures, let me know that Innovative SHOULDN'T be storing anything in the item property call number field unless it had an item specific call number. That seemed odd to me. And while I would have felt fine manually having them correct the records for the 700 odd records that didn't have the call number, I really didn't want to remove the call number from the majority of our records, especially as I'm pretty sure I confirmed that for new records - the item property call number was getting filled in.

I just chalked this up to something that perhaps our instance of Sierra is doing differently as I've learned in my year in this job that no two instances of Sierra are the same. Ours was migrated from Millenium, not sure if that makes a difference. So I just decided to go with the workaround I have, and hope that I don't have any major data integrity issues. (In my workaround I adjusted my query slightly to get the call number from the bib varfield if it wasn't in item call property field)

But I do confess to being curious as to whether other libraries are seeing the data stored here - or if it is just me.
Below find the query and instructions that the trainer shared with me. As I said, the results contained the majority of my records, but I didn’t see any need to panic and call the Help Desk. But perhaps if a whole bunch of people report seeing the same as me, I could go back to him and let him know that things weren’t behaving as he reported for other libraries too, maybe someone else would look at it. Any other libraries want to run this and report back?


The following is a query that identifies item records that have data in the call_number column in the item_record_properties table (Item Call Number), with no corresponding data for the same item in the varfield_view table (V Item Call #). I added the varfield_view bib record call number (V Bib Call #) because if there is a value in item_record_properties then it is probably also in the bib record.

SELECT
id2reckey(irp.item_record_id) AS "Item Record",
irp.call_number AS "Item Call Number",
ic.field_content AS "V Item Call #",
id2reckey(bc.record_id) AS "Bib Record",
bc.field_content AS "V Bib Call #"
FROM
sierra_view.item_record_property irp
LEFT JOIN sierra_view.varfield_view ic ON irp.item_record_id = ic.record_id AND ic.varfield_type_code = 'c'
JOIN sierra_view.bib_record_item_record_link l ON irp.item_record_id = l.item_record_id
JOIN sierra_view.varfield_view bc ON l.bib_record_id = bc.record_id AND bc.varfield_type_code = 'c'
WHERE ic.record_id IS NULL AND irp.call_number IS NOT NULL AND irp.call_number = bc.field_content;

If this produces any results, they should be reported to the Help Desk for investigation and correction. A similar query could check if there is a call number entry in the varfield_view table, but no value in the call_number column in item_record_properties.



Julie Cole
Library Systems Administrator
604.323.5541 | jcole@langara.ca
Langara Library

jcole@langara.ca
Comment
  1. Julie Cole
  2. 1 year ago
  3. #1204
Sorry for not getting back to you sooner, IUG messages seem to go to my junk email folder. Thank you very much for taking the time to run the query.
If things worked the way my SQL contact THINKS they should, then it should not return any rows at all.
I have many rows in the Item Call Number field, and he suggests that they should not be in that field unless they are in the "V Item Call #" (The V column field represents records where there is a specific and different call number for the item record and not the standard one that is part of the bib.)

So far, while this is not statistically significant, all 3 people who have tried this are seeing similar results to me.

Julie.

jcole@langara.ca
Hi Julie,

I ran the query above, and it returned 1.5 million records, but nothing at all in the "V Item Call #" column. Is that what you were looking to verify?

Arianna


arschlegel@vassar.edu
There are no comments made yet.
  1. more than a month ago
  2. Sierra/ Millennium/ Encore
  3. # 1
Accepted Answer Pending Moderation
0
Votes
Undo
I have exactly the same thing going on. I have a ticket in with Innovative to fix this, since Sep 11. Haven’t heard back yet.

If the data you are expecting in the field itemcall.call_number should be the same as the call.index_entry field for those instances where it is blank, you could modify the query to be:
SELECT
CASE
WHEN itemcall.call_number is NULL
THEN call.index_entry
ELSE itemcall.callnumber
END AS ItemCallNumber,
call.index_entry,
item.record_num

FROM
sierra_view.bib_record as bib

jcole@langara.ca
Comment
Hi Julie,

Have you heard back? I submitted the issue in October and I haven't heard back. I've found that if I insert a item level call number into the records with this issue and then delete the item call number the record functions correctly. I am having the exact same issue with some barcodes as well. They don't show up in the SQL query in the circ_trans table even though the info is in the item record.

LMK and thanks!

nallen@greenwichlibrary.org
There are no comments made yet.
  1. more than a month ago
  2. Sierra/ Millennium/ Encore
  3. # 2
  • Page :
  • 1


There are no replies made for this post yet.
Be one of the first to reply to this post!