Skip to content

Instantly share code, notes, and snippets.

@rayvoelker

rayvoelker/item_record_data_from_barcodes.sql

Created November 6, 2025 14:55
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save rayvoelker/f13aa52c16aaca52354f25a92bad0379 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save rayvoelker/f13aa52c16aaca52354f25a92bad0379 to your computer and use it in GitHub Desktop.
Download ZIP
Find Sierra item record details from a pasted set of item barcodes
Raw
item_record_data_from_barcodes.sql
with raw(txt) as (
values($$
A000073358188
A000999888777
1041606446014
$$)
),
pasted(barcode_raw) as (
select regexp_replace(s, '^\s+|\s+$', '', 'g') -- trim line-by-line
from regexp_split_to_table((SELECT txt FROM raw), E'\n') AS s
where s IS NOT NULL AND s <> '' -- skip blank lines
),
norm AS (
-- keep the original paste AND build the phrase_entry key
select
barcode_raw,
case
-- if user already pasted with a leading 'b', don't double it
when barcode_raw ~* '^\s*b' then lower(regexp_replace(barcode_raw, '\s+', '', 'g'))
else 'b' || lower(regexp_replace(barcode_raw, '\s+', '', 'g'))
end as pe_key
from pasted
)
select
n.barcode_raw as pasted_barcode,
-- n.pe_key as normalized_pe_key,
rm.record_num::TEXT as item_record_num,
i.item_status_code,
i.location_code,
i.icode2,
-- more item record fields can be added here
(
select v.field_content
from
sierra_view.volume_record_item_record_link vrirl
join sierra_view.varfield v ON v.record_id = vrirl.volume_record_id
where vrirl.item_record_id = i.record_id
limit 1
) as volume_record_stmnt
from
norm n
left outer join sierra_view.phrase_entry pe on (
pe.index_tag || pe.index_entry = n.pe_key -- uses your expression index
)
left outer join sierra_view.item_record i on i.record_id = pe.record_id
left outer join sierra_view.record_metadata as rm on rm.id = i.record_id
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment