Got a session on SQL by Jeremy Newville.
Introduction
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).
[code lite=”true”]
borrower# in (select borrower# from borrower_barcode where substring(bbarcode,1,1)!=’2′ and lost_date is null)
[/code]
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.
[code light=”true” language=”sql”]
select distinct bib# from bib
where tag = ‘856’
and charindex(char(31)+’uthttp://www.gutenberg.org’,text)>0
order by bib#
[/code]
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.
[code light=”true” language=”sql”]
— copies bib# into temporary table
select distinct bc.bib# into
#bibs_to_update
from bib_control bc, bib b
where b.bib# = bc.bib#
and bc.staff_only=0
and b.tag = ‘856’
and charindex(char(31)+’uhttp://ww.gutenberg.org’,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#
[/code]
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
End
That’s it. Hope everyone enjoyed the conference!
