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)
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)+'uthttp://www.gutenberg.org',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 #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#
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