Query Examples

SELECT 
  reckey2id('b2354225') as record_metadata_id

record_metadata_id      
--------------
420909149233
(1 row)				


					
SELECT 
  id2reckey(420909149233) as record_num

record_num
----------
b2354225
(1 row)


										
SELECT 
  id2reckey(bib_record_id) as bib, 
  id2reckey(item_record_id) as item 
FROM 
  sierra_view.bib_record_item_record_link
					  
   bib    |   item
----------+---------- 
 b1123651 | i1262563
(1 row)
					
					
SELECT 
  reckey2id(record_type_code||record_num) as moby_record_metadata_id 
FROM 
  sierra_view.varfield_view 
WHERE 
    marc_tag = '245' 
  AND  
    field_content LIKE '|aMoby Dick%' 

moby_record_metadata_id    
--------------
420907842255
(1 row)

Join to sierra_view.varfield to see the varfield contents.

--
-- joining to the varfield view
--
SELECT
  rm.record_num,
  v.varfield_type_code,
  v.field_content
FROM sierra_view.record_metadata rm
  JOIN sierra_view.varfield v ON v.record_id = rm.id
WHERE
  rm.record_type_code = 'b'
  AND v.varfield_type_code = 'a'
LIMIT 100

Join to sierra_view.subfield to see the subfield contents.

SELECT
  rm.record_num,
  s.field_type_code,
  s.marc_tag, s.content
FROM sierra_view.record_metadata rm
  JOIN sierra_view.subfield s ON s.record_id = rm.id
WHERE rm.record_type_code = 'b'
  AND s.field_type_code = 'a'
LIMIT 100

This example illustrates how to select varfields, how to parse them with regular expressions, and how to select specific items based on the barcode field exposed in the item view.

--
-- notes
--  (a) joins to varfield are 'outer' joins; in case the varfield is not
--      in the record, you still get the record
--  (b) subfields can be parsed from varfield.field_content with regular
--      expressions as done below, or one could join to the 'subfield'
--      view instead
--
SELECT  
-- bib fields
  b.record_num             AS "bib_record",
  b.is_on_course_reserve,  bc.marc_tag    AS "bib_record_varfield_c_marc_tag",
  bc.field_content         AS "bib_record_call_number",
  -- item fields
  i.record_num             AS "item_record",
  i.item_status_code,  i.location_code    AS "item_location_code",
  i.barcode                AS "item_record_barcode",
  ic.marc_tag              AS "item_record_varfield_c_marc_tag",
  ic.field_content         AS "item_record_call_number",
  TRIM(REGEXP_REPLACE(ic.field_content,'\|.',' ','g'))
    AS "item_record_call_number_subfield_tags_removed",
  iv.marc_tag              AS "item_record_varfield_v_marc_tag",
  iv.field_content         AS "item_record_volume",
  i.last_checkin_gmt,
  --checkout fields
  c.due_gmt
FROM  sierra_view.item_view i
    JOIN sierra_view.bib_record_item_record_link birl
      ON i.id = birl.item_record_id
    JOIN sierra_view.bib_view b
      ON b.id = birl.bib_record_id
    JOIN sierra_view.checkout c
      ON c.item_record_id = i.id
    -- Join for item record varfield type code 'c'
    LEFT OUTER JOIN sierra_view.varfield ic
      ON i.id = ic.record_id AND ic.varfield_type_code = 'c'
    -- Join for item record varfield type code 'v'
    LEFT OUTER JOIN sierra_view.varfield iv
      ON i.id = iv.record_id AND iv.varfield_type_code = 'v'
    -- Join for bib record varfield type code  'c'
    LEFT OUTER JOIN sierra_view.varfield bc
      ON b.id = bc.record_id AND bc.varfield_type_code = 'c'
WHERE   b.record_num = '1013595';

SELECT
  record_type_code,
  varfield_type_code as "field_group_tag",
  display_order,
  name
FROM sierra_view.phrase_type
  JOIN sierra_view.phrase_type_name ON phrase_type_name.phrase_type_id = phrase_type.id
ORDER by 1, 2
=> ...
"b";"m"; 7;"NON-LC CALL NOS"
"b";"o";11;"OCLC NO"
"b";"s";10;"JOURNAL TITLE"
"b";"t"; 9;"TITLE"
"b";"x";16;"INNOVACQ#"
"c";"l";32;"OLD ORDER #"
"i";"b";21;"BARCODE"
"i";"c";18;"CALL NO"
...

By joining to the phrase_entry view using this information, you can efficiently select records matching the indexed values.

Selecting bibs by OCLC number at a site that has indexed the bib OCLC number stored in an 'o'-tagged varfield:

SELECT
  record_type_code,
  record_num,
  title,
  cataloging_date_gmt
FROM sierra_view.bib_view b
  JOIN sierra_view.phrase_entry p ON p.record_id = b.id
WHERE
  p.varfield_type_code = 'o'
  and p.occurrence = 0
  and index_entry in ('10727045','10727067','10727062','10727085')
ORDER BY record_type_code, record_num

=>
"b";1000999;"A computer and communications network performance analysis primer";"1985-10-30 00:00:00-06"
"b";1001000;"Interpreting mathematical economics and econometrics";             "1985-07-06 00:00:00-05"
"b";1001001;"North Africa : regional tensions and strategic concerns";          "1985-04-24 00:00:00-06"
"b";1001002;"Augustus Baldwin Longstreet";                                      "1984-10-12 00:00:00-05"

The following shows an example of selecting fields from a new adjusted view.

SELECT 
    c.barcode as "item_barcode", 
    c.price 
FROM sierra_view.item_view_adjcur  c 

The following examples shows how to apply the adj_moneyfmt() function on a column in an existing view.

SELECT 
    c.barcode as "item_barcode", 
    public.adj_moneyfmt(c.price) as "price" 
FROM  
    sierra_view.item_view c  

The following example shows how to use the adj_moneyfmt() function on an existing view and how to properly use a JOIN clause (that is, how to apply the function after joining another view) to optimize performance.

SELECT 
    c.barcode as "item_barcode",
    c.record_num as "item_num", 
    public.adj_moneyfmt(c.price) as "price" 
FROM sierra_view.checkout k 
JOIN sierra_view.item_view c ON k.item_record_id=c.id 
WHERE k.due_gmt between '2018-09-01 00:00:00' and '2019-09- 28'   

To access bib record numbers, their material type codes, and material type descriptions:

SELECT
  b.record_num      AS "bib_record_number",
  brp.material_code AS "material_type_code",
  m.name            AS "material_type_description"
FROM sierra_view.bib_view b
  JOIN sierra_view.bib_record_property brp    ON brp.bib_record_id = b.id
  JOIN sierra_view.material_property_myuser m ON brp.material_code = m.code
LIMIT 100

Similarly for bib_level:

SELECT
  b.record_num       AS "bib_record_number",
  brp.bib_level_code AS "bib_level_code",
  bl.name            AS "bib_level_description"
FROM sierra_view.bib_view b
  JOIN sierra_view.bib_record_property brp      ON brp.bib_record_id = b.id
  JOIN sierra_view.bib_level_property_myuser bl ON brp.bib_level_code = bl.code
LIMIT 100

Join to the result of a SELECT statement.

SELECT
 patron.record_num,
 name.first_name,
 name.last_name,
 patron.pcode4,
 pcode4property.name
FROM sierra_view.patron_view AS patron
JOIN sierra_view.patron_record_fullname AS name     
  ON patron.id = name.patron_record_id
JOIN (
 SELECT propertyname.name, property.code::integer
  FROM sierra_view.user_defined_property property
JOIN sierra_view.user_defined_category propertycategory     
 ON property.user_defined_category_id= propertycategory.id
JOIN sierra_view.user_defined_property_name AS propertyname 
 ON property.id = propertyname.user_defined_property_id
WHERE propertycategory.code = 'pcode4')
AS pcode4property ON patron.pcode4 = pcode4property.code

SELECT 
   rc   .code              AS  permission_category, 
   r    .code              AS  permissions,
   u    .name              AS  user_name,
   u    .full_name         AS  user_full_name,
   u_loc.location_code     AS  user_location
FROM  
           sierra_view.iii_user           u 
LEFT JOIN  sierra_view.iii_user_location  u_loc  ON  u_loc.iii_user_id = u.id
JOIN       sierra_view.iii_user_iii_role  ur     ON  ur.iii_user_id    = u.id 
JOIN       sierra_view.iii_role           r      ON  r.id              = ur.iii_role_id 
JOIN       sierra_view.iii_role_category  rc     ON  rc.id             = r.iii_role_category_id   
 -- WHERE  u.name = 'iiitest'
ORDER  BY  u. name

SELECT
					f.acct_unit,
					f.fund_code,
					f.name AS fund_name,
					f.appropriation,
					f.expenditure,
					f.encumbrance,
					f.appropriation - f.expenditure - f.encumbrance AS "free_balance",
					f.appropriation - f.expenditure AS "cash_balance"
					FROM sierra_view.fund_myuser f
					WHERE
					f.fund_type = 'fbal'
					ORDER BY
				1, 2

SELECT
					at.voucher_num,
					'o' || o.record_num            AS "order_record",
					f.name                         AS "fund_name",
					ie.subfund_code,
					f.external_fund_code_num       AS "external_fund",
					i.paid_date_gmt::date,
					ie.invoice_date::date,
					i.invoice_number_text          AS "invoice_number",
					at.amt::numeric(30,2)          AS "amount",
					ie.tax_amt::numeric(30,2)      AS "tax",
					ie.use_tax_amt::numeric(30,2)  AS "use_tax",
					ie.ship_amt::numeric(30,2)     AS "shipping",
					ie.discount_amt::numeric(30,2) AS "discount",
					at.note,
					v.code                         AS "vendor_code",
					o.blanket_purchase_order_num   AS "blanket_po"
					FROM   sierra_view.fund_myuser f
					JOIN sierra_view.accounting_transaction at                     
					ON at.fund_master_id             = f.fund_master_id
					JOIN sierra_view.accounting_transaction_invoice_expenditure ie 
					ON ie.accounting_transaction_id  = at.id
					JOIN sierra_view.invoice_view i                                
					ON ie.invoice_record_metadata_id = i.id
					JOIN sierra_view.vendor_view v                                 
					ON ie.vendor_record_metadata_id  = v.id
					JOIN sierra_view.order_view o                                  
					ON ie.order_record_metadata_id   = o.id
					WHERE
					fund_type = 'fbal'
					ORDER BY
					voucher_num,
				order_record

SELECT
					b.record_num               AS "bib_record",
					b.record_creation_date_gmt AS "bib_created_date",
					i.record_num               AS "item_record",
					i.record_creation_date_gmt AS "item_created_date"
					FROM
					sierra_view.bib_view b
					JOIN sierra_view.bib_record_item_record_link l ON l.bib_record_id =  b.id
					JOIN sierra_view.item_view i                   ON l.item_record_id = i.id
					WHERE
					date_part('month', b.record_creation_date_gmt) = 8     -- month of August
					AND date_part('month', i.record_creation_date_gmt) = 12    -- month of December
				ORDER BY 1

EXPLAIN ANALYZE SELECT
					b.record_num                AS "bib_record",
					date(b.cataloging_date_gmt) AS "bib_cataloged_dt",
					b.title                     AS "bib_title",
					a.item_count                AS "item_count"
					FROM
					(SELECT
					l.bib_record_id AS "bib_record_id",
					count(l.id) AS "item_count"
					FROM
					sierra_view.bib_record_item_record_link l
					GROUP BY l.bib_record_id
					HAVING count(l.id) > 100) AS a
				JOIN sierra_view.bib_view b ON a.bib_record_id = b.id

SELECT
					id2reckey(l.item_record_id)
					FROM sierra_view.bib_record_item_record_link l
					GROUP BY 1
				HAVING COUNT(*) > 1;

SELECT
					p.record_num          AS "patron_record",
					max(n.last_name)      AS "last_name",
					max(n.first_name)     AS "first_name",
					p.expiration_date_gmt AS "expiration_date",
					count(c.id)           AS "checkout_count"
					FROM sierra_view.patron_view p
					JOIN sierra_view.patron_record_fullname n ON n.patron_record_id = p.id
					JOIN sierra_view.checkout c               ON c.patron_record_id = p.id
					WHERE 
					p.expiration_date_gmt < current_date
					GROUP BY 1,4
				ORDER BY 5 desc

SELECT
					'p' || p.record_num                    AS "patron_record",
					'b' || b.record_num                    AS "bib_record",
					b.title                                AS "bib_title",
					current_date - h.placed_gmt::date      AS "days_since_hold_placed",
					date_trunc('days',h.placed_gmt)::date  AS "placed_date",
					h.delay_days,
					date_trunc('days',h.expires_gmt)::date AS "expires_date",
					h.is_ill,
					h.is_ir,
					CASE WHEN h.is_ir
					THEN pickup_location_code
					ELSE ir_pickup_location_code
					END                                    AS "pickup_location_code"
					FROM
					sierra_view.bib_view b
					JOIN sierra_view.hold h        ON h.record_id        = b.id
					JOIN sierra_view.patron_view p ON h.patron_record_id = p.id
					WHERE is_frozen = true
					AND (current_date - h.placed_gmt::date) >= 365
				ORDER BY 4 desc

--
					-- list patrons with more than 20 holds
					--
					SELECT  'p' || p.record_num AS "patron_record",
					count(h.id)                 AS "number_of_holds"
					FROM sierra_view.patron_view p
					JOIN sierra_view.hold h ON h.patron_record_id = p.id
					GROUP by 1
					HAVING count(h.id) > 20
				ORDER by 2 desc

SELECT
					b.record_num                AS "bib_record",
					date(b.cataloging_date_gmt) AS "bib_cataloged_dt",
					b.title                     AS "bib_title",
					count(h.id)                 AS "hold_count"
					FROM  sierra_view.bib_view b
					JOIN sierra_view.hold h ON h.record_id   = b.id
					GROUP BY 1,2,3
					HAVING count(h.id) > 1
				ORDER BY 4 desc