COSUGI 2017: Walk like a SQLian

Got a session on SQL by Jeremy Newville.


SQL is what allows direct work with the Horizon database, useful both inside and outside of the client.

Inside the Client

Many mq view reports allow good search options, but can’t always get the views you want.

Example: want to see list of borrowers where barcodes does not begin with ‘2’

File -> Where Clause… shows which view mq view is tied to. That view does not have the barcode column. Can write SQL subquery to connect the two tables (borrower_barcode).

borrower# in (select borrower# from borrower_barcode where substring(bbarcode,1,1)!='2' and lost_date is null)

Bib Export

Example: e-resources bibs wanted (don’t have ERC), export to make separately searchable ‘target’ in Enterprise (as opposed to limited/faceted subset of the ILS target). Use Batch Bib Export to export certain records. Function expects selection of bib# only, so SELECT will be simple, and WHERE clause looking for text in tag 856u.

select distinct bib# from bib
where tag = '856'
and charindex(char(31)+'ut',text)>0
order by bib#

Can upload MARC Records to Enterprise using the Import process. Will need to resolve any display issues.

Records can then be set to staff only by updating bib_control.

-- copies bib# into temporary table
select distinct bc.bib# into
from bib_control bc, bib b
where b.bib# = bc.bib#
and bc.staff_only=0
and b.tag = '856'
and charindex(char(31)+'u',b.text)>0

-- updates the bib control table as needed
update bib_control
set staff_only=1
from bib_control,#bibs_to_update
where bib_control.bib# = #bibs_to_update.bib#

BlueCloud Analytics

Any reporting tool will simplify needed, but fenced into GUI model and confronts with challenges such as: inner joining vs. outer joining, applying MicroStrategy functions on attributes, separating data for side by side comparison within the same attribute (e.g. multiple tags). BCA does not query Horizon db, so need to learn BCA’s table structure and uses Cloudera’s Impala’s SQL dialect, especially specialized functions.

Example: Finding bad pubdates. Want report will bring up bib# where 008 differ from 260c, side-by-side display.

Can run simple report in GUI, then Tool -> Report Details Page which includes SQL query, so can see which tables and columns are involved, allowing you to build your final statement. Leave aliases and customer number, can ignore superfluous pieces. Import with Freeform SQL


That’s it. Hope everyone enjoyed the conference!
cat sleeping while holding a turtle

Author: Cynthia

Technologist, Librarian, Metadata and Technical Services expert, Educator, Mentor, Web Developer, UXer, Accessibility Advocate, Documentarian

Leave a Comment

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: