Skip to content

Home

Jump to bottom
kylshiny edited this page Sep 18, 2024 · 142 revisions

This page contains SQL queries for Sierra and Postgres. The initial set of queries was converted from existing Millennium/Oracle queries that can be found at: Millennium SQL Queries (UNC Staff Wiki)

Table of Contents

general examples (snippets)

make record_num searchable in sierra

Append 'a'; prepend 'b', 'i', etc. 

 'b' || record_num || 'a'
 'i' || record_num || 'a'
 and so on

querying by a specific date 

 WHERE
   record_last_updated_gmt > TO_DATE('2015-08-05', 'YYYY-MM-DD')

Postgres date format codes: https://www.techonthenet.com/postgresql/functions/to_date.php

querying for a relative time/date (i.e. in the last hour, etc.)

This example will find records updated in the last 60 minutes: 

 WHERE
   record_last_updated_gmt > (localtimestamp - interval '60 minute')

This example will find records updated in the last 1.5 days (36 hours = 2160 minutes): 

 WHERE
   record_last_updated_gmt > (localtimestamp - interval '36 hour')

Postgres time functions and operators: https://www.postgresql.org/docs/9.1/static/functions-datetime.html

exclude locations from your query results

  • Add the following to the WHERE section of your query.
  • Used in a query where the set is already narrowed down to only bib records, adding the following would further narrow to exclude any bib with locations beginning with k, no, or w.
 and NOT EXISTS (SELECT * from sierra_view.bib_record_location ll
                 WHERE v.record_id = ll.bib_record_id
                    AND (ll.location_code like 'k%'
                         OR ll.location_code like 'no%'
                         OR ll.location_code like 'w%')
                 )

all locations for each bib (string aggregation)

  • Returns record number/key and list of comma separated locations for that bib – collapses multiple rows from the locations table into one row per record number
  • Will be most useful as a join expression, so you can output the locations for some set of bib records
 SELECT	bib_record_id,
        STRING_AGG(Trim(trailing FROM location_code), ', ' order by id) AS locations
 FROM	sierra_view.bib_record_location
 WHERE	location_code != 'multi'
 GROUP BY bib_record_id
  • Example of use within another query: This would return the record number (with 'a' appended), the bib locations, the marc_tag, indicators, and field value for all 020 fields in all bib records.
 select 'b' || b.record_num || 'a' AS bnum,
        bp.material_code,
        locs.locations,
        v.marc_ind1,
        v.marc_ind2,
        v.field_content
 from sierra_view.varfield v
      inner join sierra_view.bib_view b on b.id = v.record_id
      inner join (SELECT bib_record_id,
                      STRING_AGG(Trim(trailing FROM location_code), ', ' order by id) AS locations
               FROM   sierra_view.bib_record_location
               WHERE location_code != 'multi'
               GROUP  BY bib_record_id ) locs
             ON b.id = locs.bib_record_id
  • Can also be used in a select clause, e.g:
 select
   'b' || rm.record_num || 'a',
   v.field_content,
   (select STRING_AGG(brl.location_code, ',')
     from sierra_view.bib_record_location brl
     where brl.bib_record_id = rm.id
     and brl.location_code != 'multi') as bib_locs,
 from...

all locations for each item (string aggregation) 

 (select STRING_AGG(distinct i.location_code, ',')
   from sierra_view.bib_record_item_record_link bil
   inner join sierra_view.item_record i on i.id = bil.item_record_id
   where bil.bib_record_id = rm.id) as item_locs

return multiple columns for different var_fields

See this query below for an example of this

strip extraneous characters in the 020 (isbn, issn) field

This includes certain subfields and anything in parentheses 

distinct regexp_replace(vi.field_content, '^.*?\|a|\|q.*|\(.*', '', 'g') as ISBN

query by location and call no.

Update the location code as desired and the preferred call no. criteria. In this case the MDC and anything with DVD in its call no. 

select 'i'||i.record_num||'a',

FROM
     sierra_view.varfield v, sierra_view.item_view  i
    
 WHERE
     i.location_code ilike 'ul%'
     AND i.record_type_code = 'i' AND v.record_id = i.id
     AND (v.varfield_type_code = 'c' AND v.field_content ilike '%DVD%' )

specific queries

active items in the lsc

Finds all "active" UnL materials in the LSC. It filters items in HSL and the two private collections: MRC unprocessed and Archives special. It lso filters items that are in process, withdrawn or declared lost. 

SELECT
	i.barcode,
	i.agency_code_num,
	i.location_code,
	to_char(
		r.record_last_updated_gmt,
		'MM/DD/YYYY'
	) AS LastModded,
	i.itype_code_num,
i.item_status_code
FROM
	sierra_view.item_view i
INNER JOIN sierra_view.record_metadata r ON r."id" = i."id"
WHERE
	i.location_code LIKE 'tr%'
AND i.location_code NOT IN ('trlnc', 'trnhb', 'trz', 'trulv', 'trwas')
AND i.item_status_code NOT IN ('e', 'w', 'd', 'p')
ORDER BY
	i.location_code ASC

audit ebook 773s

Refer to recurring task instructions (STAFF WIKI) for how these are used

part 1 

 SELECT
  v.field_content,
  COUNT(v.record_id)     
 FROM
  sierra_view.varfield v
 INNER JOIN
  sierra_view.bib_record b
  ON v.record_id = b.record_id
  AND b.bcode3 NOT IN ('d', 'n', 'c')
 WHERE
  v.varfield_type_code = 'w'
  AND v.marc_tag = '773'
  AND v.field_content ~ '\(online collection\)|Undergraduate library Kindle ebook collection'
 GROUP BY v.field_content
 ORDER BY v.field_content ASC;

part 2 

 select 'b' || rm.record_num || 'a' AS bnum,
         brp.material_code,
         (SELECT STRING_AGG(Trim(trailing FROM brl.location_code), ', ' order by id)
            FROM sierra_view.bib_record_location brl
            WHERE brl.bib_record_id = b.id
              AND brl.location_code != 'multi'
         ) AS locations,
         v.marc_ind1,
         v.marc_ind2,
         v.field_content
 from sierra_view.varfield v
   inner join sierra_view.bib_record b ON b.id = v.record_id
      and b.bcode3 not in ('d', 'n', 'c')
   inner join sierra_view.record_metadata rm on rm.id = b.id
   inner join sierra_view.bib_record_property brp ON brp.bib_record_id = b.id
     and brp.material_code = 'z'
 where v.marc_tag = '773'
   and v.field_content not like '%(online collection)%'
   and v.field_content not like '|tOCLC WorldShare Collection Manager managed collection.%'
   and v.field_content !~ '\|[7db]|\|w\(|\|x\d';

bib 001s with alphabetic suffixes 

 select
    v.field_content, bp.material_code
 from
    sierra_view.varfield v, sierra_view.bib_record b,
    sierra_view.bib_record_property bp
 where
    v.marc_tag = '001'
    AND b.bcode3 not in ('n', 'c')
    AND v.field_content ~* '^(\|a)?[0-9]+[a-z]+$'
    AND v.field_content !~* '^(\|a)?[0-9]+(abcc|wcm|dukeup|eeubo|ebr|momw|scidir|spr|wol)$'
    AND bp.bib_record_id = v.record_id
    AND bp.bib_record_id = b.record_id;
  • ~* supplies the case-insensitive parameter

bib 001s with wrong field code type

Can be adapted to check other field types. 

SELECT
   'b'||v.record_num||'a'
FROM
   sierra_view.varfield_view v
WHERE
   v.record_type_code = 'b'
   AND v.varfield_type_code = 'y'
   AND v.marc_tag = '001'
ORDER BY
   v.record_num

bib 856 40s not on e-records 

 select 'b' || b.record_num || 'a' as bnum,
      bp.bib_level_code,
      bp.material_code,
      b.bcode3,
      locs.locations,
      l.record_type_code "ldr rec type",
      vform.p23 "008 23",
      vform.p29 "008 29",
      vt.content "GMD",
      vdesc.field_content as "300 description",
      vof.field_content as "other format note",
      v.marc_tag,
      v.marc_ind1,
      v.marc_ind2,
      substring(v.field_content, '\|u[^|]*') "URL",
      substring(v.field_content, '\|y[^|]*') "856y"
 from sierra_view.varfield v
      inner join sierra_view.bib_view b
           on v.record_id = b.id
           and v.marc_tag = '856'
           and v.marc_ind1 = '4'
           and v.marc_ind2 = '0'
       inner join (select bib_record_id, STRING_AGG(Trim(trailing FROM location_code), ', ' order by id) AS locations
       FROM   sierra_view.bib_record_location
               where location_code != 'multi'
                  GROUP  BY bib_record_id) locs
              ON b.id = locs.bib_record_id
      inner join sierra_view.bib_record_property bp
           on b.id = bp.bib_record_id
           and bp.material_code not in ('z', 'w', 's', 'm')
       left outer join sierra_view.leader_field l
           on l.record_id = b.id
       left outer join sierra_view.control_field vform
           on vform.record_id = b.id
           and vform.control_num = '8'
      left outer join sierra_view.subfield vt
           on v.record_id = vt.record_id
           and vt.marc_tag = '245'
           and tag = 'h'
      left outer join sierra_view.varfield vdesc
           on v.record_id = vdesc.record_id
           and vdesc.marc_tag = '300'
      left outer join sierra_view.varfield vof
           on v.record_id = vof.record_id
           and vof.marc_tag = '530'
 where NOT EXISTS (select * from sierra_view.bib_record_location bl
                 WHERE v.record_id = bl.bib_record_id
                 and (bl.location_code like 'e%')
     );

bib 856s with invalid subfield tags (indicating probable bad pipe characters in url) 

 SELECT 'b' || rm.record_num || 'a' AS bnum,
        v.field_content
 FROM   sierra_view.varfield v
 INNER JOIN sierra_view.bib_record br on br.id = v.record_id
 INNER JOIN sierra_view.record_metadata rm on rm.id = br.id
 WHERE  v.marc_tag = '856'
        AND v.field_content ~ '\|[^abcdfhijklmnopqrstuvwxyz2368]'

bib 856s with more than one |u 

 SELECT 'b' || rm.record_num || 'a' AS bnum,
        v.marc_ind1,
        v.marc_ind2,
        v.field_content
 FROM   sierra_view.varfield v
 INNER JOIN sierra_view.bib_record br on br.id = v.record_id
 INNER JOIN sierra_view.record_metadata rm on rm.id = br.id
 WHERE  v.marc_tag = '856'
        AND v.field_content ~ '\|u.*\|u'

variable fields in unsuppressed bibs using oclc-style dagger subfield delimiter instead of sierra pipe

(Or approximation of the above...) 

 SELECT
   'b' || rm.record_num || 'a' AS bnum,
   v.marc_tag,
   v.marc_ind1,
   v.marc_ind2,
   v.field_content
 FROM
   sierra_view.varfield v
 INNER JOIN sierra_view.bib_record b ON b. ID = v.record_id
 AND b.is_suppressed = 'f'
 INNER JOIN sierra_view.record_metadata rm ON rm. ID = v.record_id
 WHERE
   v.field_content ~ 'ǂ'

bibs unsuppressed and has cat date, with no unsuppressed items, holdings, or order records

This query has to exclude e-resource bibs, which by design have all their attached records suppressed. It does this by excluding records with any bib locations starting with e. It is Kristina's understanding that any noheb-type item/holdings/order location should scope within III to include a bib location starting with e. 

 SELECT 'b' || b.record_num || 'a' AS bib_rec_key,
        locs.locations,
       To_char(b.record_creation_date_gmt, 'YYYY-MM-DD') AS "created date",
       to_char(b.cataloging_date_gmt, 'YYYY-MM-DD') as "cat date",
       b.bcode3,
       bp.material_code,
      (select string_agg(v.field_content, ' ; ')
         from sierra_view.varfield v where v.record_id = b.id
         AND v.marc_tag = '001')as OCLCNo,
      (select string_agg(v1.field_content, ' ; ')
        from sierra_view.varfield v1 where v1.record_id = b.id
        AND v1.marc_tag = '856')as URL,
      (select string_agg(v2.field_content, ' ; ')
        from sierra_view.varfield v2 where v2.record_id = b.id
        AND v2.marc_tag = '919')as ReviewNotes
 FROM   sierra_view.bib_view b
        inner join (SELECT bib_record_id, STRING_AGG(Trim(trailing FROM location_code), ', ' order by id) AS locations
                    FROM   sierra_view.bib_record_location
                    WHERE location_code != 'multi'
                    GROUP  BY bib_record_id) locs
               ON b.id = locs.bib_record_id
        INNER JOIN sierra_view.bib_record_property bp on bp.bib_record_id = b.id
 WHERE  b.bcode3 NOT IN ( 'd', 'n', 'c' )
       AND b.cataloging_date_gmt IS NOT NULL
        AND NOT EXISTS (SELECT *
                        FROM sierra_view.bib_record_item_record_link lri
                        INNER JOIN sierra_view.item_record i
                        ON lri.item_record_id = i.id
                        WHERE b.id = lri.bib_record_id
                          AND i.icode2 <> 'n')
        AND NOT EXISTS (SELECT *
                        FROM sierra_view.bib_record_holding_record_link lrh
                        INNER JOIN sierra_view.holding_record h
                        ON lrh.holding_record_id = h.id
                        WHERE b.id = lrh.bib_record_id
                          AND h.is_suppressed <> 't')
        AND NOT EXISTS (SELECT *
                        FROM sierra_view.bib_record_order_record_link lro
                        INNER JOIN sierra_view.order_record o
                        ON lro.order_record_id = o.id
                        WHERE b.id = lro.bib_record_id
                          AND o.is_suppressed <> 't')
       AND NOT EXISTS (SELECT *
                       FROM   sierra_view.bib_record_location bl
                       WHERE  b.id = bl.bib_record_id
                              AND bl.location_code LIKE 'e%');

 ==items linked to more than one bib record, where icode2 is not set as linked or bound with==

This includes suppressed items (icode2 = n) that lack internal note about linked records (wanda) 

 select 'i' || rm.record_num || 'a' as item_rec_num
      , linkct.lbibs as linked_bib_count
      , (select string_agg('b' || rm2.record_num || 'a', ', ' ORDER BY rm2.record_num ASC)
           from sierra_view.bib_record_item_record_link bil2
           inner join sierra_view.record_metadata rm2 on rm2.id = bil2.bib_record_id
           where bil2.item_record_id = linkct.item_record_id
         ) as linked_to_bibs
      , i.icode2
      , i.item_status_code
      , i.location_code
      , min(v.field_content) as barcode
 from
       -- The set of items attached to more than one bib, with icode2 not l or b,
       -- with count of bibs to which item is attached (lbibs)
       (SELECT bil.item_record_id
             , count(*) as lbibs
        FROM sierra_view.item_record i
        INNER JOIN sierra_view.bib_record_item_record_link bil ON bil.item_record_id = i.id
        WHERE i.icode2 NOT IN ('b', 'l')
        GROUP BY bil.item_record_id
        HAVING count(*) > 1) linkct
        --
        inner join sierra_view.item_record i on i.id = linkct.item_record_id
        inner join sierra_view.record_metadata rm on rm.id = i.id
        left join sierra_view.varfield v on v.record_id = i.id
          and varfield_type_code = 'b'
        left join sierra_view.varfield vln on vln.record_id = i.id
          and vln.varfield_type_code = 'x'
          and vln.field_content like 'Item record linked to%'
        where i.icode2 != 'n'
              or (i.icode2 = 'n' and vln.field_content is null)
        group by item_rec_num, linkct.item_record_id, linkct.lbibs, i.icode2, i.item_status_code, i.location_code

bibs with more than one instance of a given field

Query now depends on whether field is a control field or variable field.

control field 

 SELECT
    'b' || b.record_num as bnum
 FROM
    sierra_view.control_field c, sierra_view.bib_view b
 WHERE
    c.control_num = '8'
    AND c.record_id = b.id
 GROUP BY
    bnum HAVING COUNT(*) > 1;

variable field 

 SELECT
    'b' || b.record_num as bnum
 FROM
    sierra_view.varfield v, sierra_view.bib_view b
 WHERE
    v.marc_tag = '856'
    AND v.record_id = b.id
 GROUP BY
    bnum HAVING COUNT(*) > 1;

items (non-e-format, based on location code) attached to e-bibs (based on material_code / mat_type) 

 select i.location_code, 'i' || i.record_num as inum, 'b' || b.record_num as bnum, br.material_code, i.itype_code_num as itemType, i.item_status_code as itemStatus
 from sierra_view.item_view i, sierra_view.bib_view b, sierra_view.bib_record_property br, sierra_view.bib_record_item_record_link L
 where br.material_code in ('s', 'w', 'z')
 and i.location_code not in ('dcpfi','dcya','dcyea','dcyef','dcyfa','eb','ebna','ebnb','ebz','ed','edas','er','erda','erdb','erra','errd','erri','erri@','errs','errw','estr','erz','hhya','hhyb','jjya','kwer','kwer2','nohe','noheb','uldah','wbcc','wbpab','wbpad','xcac','xcad','ydya')
 and L.item_record_id = i.id
 and L.bib_record_id = b.id
 and br.bib_record_id = b.id;

bibs where iii bib level (bcode1) != marc ldr/07 

 SELECT
   'b' || rm.record_num || 'a' AS bnum,
   br.bcode1,
   ldr.blvl
 FROM
   (
     SELECT
       bib_level_code AS blvl,
       record_id
     FROM
       sierra_view.leader_field
   )ldr
 INNER JOIN sierra_view.bib_record br ON ldr.record_id = br.record_id
 AND br.is_suppressed = 'f'
 INNER JOIN sierra_view.record_metadata rm ON ldr.record_id = rm. ID
 WHERE
   br.bcode1 != ldr.blvl

bibs where 008 lang = kor, but iii fixed field language != kor 

 SELECT
  'b' || bv.record_num || 'a' AS bnum
 FROM
  sierra_view.bib_view bv
 INNER JOIN sierra_view.control_field cf ON bv. ID = cf.record_id
 AND cf.control_num = '8'
 AND cf.p35 = 'k'
 AND cf.p36 = 'o'
 AND cf.p37 = 'r'
 WHERE
  bv.language_code != 'kor'

bibs where 008 lang != kor, but iii fixed field language = kor 

 SELECT
   'b' || bv.record_num || 'a' AS bnum
 FROM
   sierra_view.bib_view bv
 INNER JOIN sierra_view.control_field cf ON bv. ID = cf.record_id
 AND cf.control_num = '8'
 AND(
   cf.p35 != 'k'
   AND cf.p36 != 'o'
   AND cf.p37 != 'r'
 )
 WHERE
   bv.language_code = 'kor'

bibs with holdings record(s) for a location but no item record(s) for that location 

 (select
   'b' || b.record_num AS bnum
 from
   sierra_view.bib_record_holding_record_link Lr
   inner join sierra_view.holding_record_location h on
     Lr.holding_record_id = h.holding_record_id
    inner join sierra_view.bib_view b on
     Lr.bib_record_id = b.id
 where
   h.location_code like 'xc%')
 EXCEPT
 (select
   'b' || b.record_num AS bnum
 from
   sierra_view.bib_record_item_record_link Lrr
   inner join sierra_view.item_record i on
     Lrr.item_record_id = i.record_id
    inner join sierra_view.bib_view b on
     Lrr.bib_record_id = b.id
 where
     i.location_code like 'xc%')
 ;

bibs unsuppressed - count of 

 select count(b.id)
 from sierra_view.bib_view b
 where b.bcode3 not in ('d', 'n', 'c');
  • Using bib_record (and .record_id) rather than bib_view (and .id) reporting more accurate results due to presumed sync issues prior to Sierra migration completion

items in a location, returning input for endeca args_extract 

 SELECT DISTINCT 'b' || b.record_num as bnum,
                 to_char(b.cataloging_date_gmt, 'YYYYMMDD'),
                 br.material_code
 FROM
 sierra_view.item_view i, sierra_view.bib_view b, sierra_view.bib_record_property br, sierra_view.bib_record_item_record_link L
 WHERE
         i.location_code LIKE 'yb%'
         AND b.bcode3 NOT IN ('n', 'c', 'd')
         AND b.id = br.bib_record_id
         AND b.id = L.bib_record_id
         AND i.id = L.item_record_id

items last checked in within the last 18 hours, not in reserve locations

remove "not" to get only those in reserve locations 

 SELECT 'i' || i.record_num AS inum,
         i.item_status_code,
         TO_CHAR(i.last_checkin_gmt, 'MM/DD/YYYY HH24:MI')   AS chkin,
         date_part('day', (localtimestamp - i.last_checkin_gmt)) AS days_since_chkin,
         i.location_code,
         TO_CHAR(i.due_gmt, 'MM/DD/YYYY HH24:MI')    AS due,
         TO_CHAR(i.record_creation_date_gmt, 'MM/DD/YYYY')   AS created
 FROM    (sierra_view.item_view iv LEFT JOIN sierra_view.checkout c ON
         iv.id = c.item_record_id) i
 WHERE   i.last_checkin_gmt > (localtimestamp - interval '18 hour')
         AND i.icode2 != 'n'
         AND i.location_code !~* '^(aaba|bbb|ccb|ggb|kres|llb|mmb|nohv|rrb|ssb|ub|ulba|wcb|xcca|ydb).*$'
 ORDER BY i.last_checkin_gmt DESC
 ;

items recently checked out (last hour) 

 SELECT
     to_char(c.checkout_gmt, 'MM/DD/YYYY HH24:MI') as checkedout,
     'b' || v.record_num AS bnum,
     v.field_content
 from sierra_view.checkout c, sierra_view.varfield_view v, sierra_view.bib_record_item_record_link L
 where   c.checkout_gmt > (localtimestamp - interval '1 hour')
         AND c.item_record_id = L.item_record_id
         AND L.bib_record_id = v.record_id
         AND v.marc_tag = '245'
     order by c.checkout_gmt DESC;

items placed on hold in the last 24 hours 

 SELECT 'i' || i.record_num as inum, TO_CHAR(h.placed_gmt, 'YYYY/MM/DD HH24:MI:SS'), 'b' || b.record_num as bib, v.field_content as barcode, i.location_code
         from sierra_view.hold h
   INNER JOIN sierra_view.bib_record_item_record_link l ON h.record_id = l.item_record_id
   INNER JOIN sierra_view.bib_view b ON b.id = l.bib_record_id
   INNER JOIN sierra_view.varfield v ON v.record_id = h.record_id
     AND v.varfield_type_code = 'b'
   INNER JOIN sierra_view.item_view i ON h.record_id = i.id
         where h.placed_gmt >= (localtimestamp - interval '24 hour')
         order by h.placed_gmt;

items that have never circulated

This first query is the equivalent of what we were using with Millennium. It yields unsuppressed items that have never been checked in (have null last_checkin_gmt fields). So, it includes unsuppressed items that are currently checked out, so long as they have never been checked in: 

 select 'i' || record_num || 'a' as inum
      , i.item_status_code
      , i.location_code
      , to_char(rm.creation_date_gmt,'MM/DD/YYYY') as created
 from sierra_view.item_record i
 inner join sierra_view.record_metadata rm on rm.id = i."id"
 where i.last_checkin_gmt is NULL
   and i.icode2 != 'n'
 order by i.location_code

This second query gets unsuppressed items that have never been checked out. It yields a subset of the first query's results; it excludes unsuppressed items that are currently checked out but have never been checked in. The "i.last_checkout_gmt is NULL" statement can be replaced with "i.last_checkin_gmt is NULL" without affecting the results: 

 select 'i' || record_num || 'a' as inum
      , i.item_status_code
      , i.location_code
      , to_char(rm.creation_date_gmt,'MM/DD/YYYY') as created
 from sierra_view.item_record i
 inner join sierra_view.record_metadata rm on rm.id = i."id"
 where i.last_checkout_gmt is NULL
   and i.icode2 != 'n'
   and not exists (select * from sierra_view.checkout chk where chk.item_record_id = i.id)
 order by i.location_code

items with more than one instance of a given field

This example finds records with more than one barcode field: 

 SELECT
     'i' || i.record_num || 'a' AS inum
 FROM
     sierra_view.varfield v, sierra_view.item_view  i
 WHERE
     v.varfield_type_code = 'b'
     AND i.record_type_code = 'i'
     AND v.record_id = i.id
 GROUP BY
     i.record_num HAVING COUNT(*) > 1;

items with more than one instance of a given field, listing the values of that field 

 select 'i' || vf.record_num || 'a' AS "item_num",
     vf.varfield_type_code AS "field type",
     vf.occ_num AS "field_order",
     vf.field_content AS "call_number_field_value"
 FROM sierra_view.varfield_view vf
 INNER JOIN (select vs.record_id
 from sierra_view.varfield_view vs
 where vs.record_type_code = 'i'
 and vs.varfield_type_code = 'c'
 group by vs.record_id
 having count(vs.record_id) > 1) vi
 ON vf.record_id = vi.record_id
 WHERE vf.varfield_type_code = 'c'
 order by vf.record_id ASC;

items for the main ul collection and reserves 

/*This pulls items for the main Robert House UL collection and UL Reserves.
It does NOT include the MRC or the MRC's TRLN holdings (TRULS and TRULV)
It filters out some, but not all statuses. Tweaking can be done as needed.
*/

SELECT
   'i'||i.record_num || 'a' AS RecordNumber,
   ip.barcode AS Barcode,
   i.agency_code_num AS ItemAgency,
   i.location_code AS ItemLocation,
   i.itype_code_num AS ItemType,
   i.item_status_code AS ItemStatus,
(
        SELECT STRING_AGG(v.field_content, ' ; ')
        FROM sierra_view.varfield v
        WHERE v.record_id = i.id
        AND v.varfield_type_code = 'x'
     ) AS XNotes
FROM
  sierra_view.item_view i
INNER JOIN sierra_view.record_metadata r ON r."id" = i."id"
INNER JOIN sierra_view.item_record_property ip on ip.item_record_id = i.id
WHERE
   i.location_code LIKE 'u%'
   AND i.location_code NOT LIKE '%z'
   AND i.location_code NOT LIKE 'ul%'
   AND i.item_status_code NOT IN ('b','e', 'w', 'd', 'p')
ORDER BY
   i.location_code ASC

count occurrence of marc tags across the catalog 

 select
     v.marc_tag, count(v.marc_tag)
 from
     sierra_view.varfield v, sierra_view.bib_record b
 where
     b.bcode3 not in ('d', 'n', 'c')
     AND b.record_id = v.record_id
 group by v.marc_tag;

list bnums containing 856 |u that is longer than 300 characters, ordered by |u length 

 SELECT
   'b' || rm.record_num || 'a' AS bnum,
   CHAR_LENGTH(sf. CONTENT)AS URLlength,
   sf. CONTENT AS URL
 FROM
   sierra_view.subfield sf
 INNER JOIN sierra_view.bib_record br ON sf.record_id = br. ID
 INNER JOIN sierra_view.record_metadata rm ON rm. ID = br. ID
 WHERE
   sf.marc_tag = '856'
 AND sf.tag = 'u'
 AND CHAR_LENGTH(sf. CONTENT)> 300
 ORDER BY
   CHAR_LENGTH(sf. CONTENT)DESC;

bib 020s from records where any 020 contains more than one |a

Returns ALL 020s from any record with a repeated 020|a 

 SELECT 'b' || relbibs.record_num || 'a' AS bnum,
        allbibs.varfield_type_code,
        allbibs.marc_tag,
        allbibs.marc_ind1,
        allbibs.marc_ind2,
        allbibs.field_content
 FROM
   (SELECT v.record_id,
          v.varfield_type_code,
          v.marc_tag,
          v.marc_ind1,
          v.marc_ind2,
          v.field_content
   FROM sierra_view.varfield v, sierra_view.bib_record b
   WHERE v.record_id = b.record_id
         AND marc_tag = '020') allbibs
 INNER JOIN
     (SELECT vv.record_id, bb.record_num
     FROM sierra_view.varfield vv, sierra_view.bib_view bb
     WHERE   marc_tag = '020'
             AND vv.record_id = bb.id
             AND field_content ~ '\|a.*\|a') relbibs
 ON allbibs.record_id = relbibs.record_id
 ORDER by bnum ASC;

(selected) bib marc fields where count of subfield a does not equal 1 

 select 'b' || rm.record_num || 'a' as bnum
      , b.bcode3
      , case when v.field_content !~ '\|a' then 'none' else 'multi' end as sf_count
      , ldr.bib_level_code as blvl
      , (SELECT STRING_AGG(Trim(trailing FROM brl.location_code), ', ' order by id)
         FROM   sierra_view.bib_record_location brl
         WHERE brl.location_code != 'multi' and brl.bib_record_id = b.id
         GROUP BY bib_record_id ) as locs
      , marc_tag
      , v.field_content
      , rm.creation_date_gmt
 from sierra_view.bib_record b 
 inner join sierra_view.varfield v on v.record_id = b."id"
   and v.marc_tag in ('100', '110', '700', '710', '600', '610')
 inner join sierra_view.record_metadata rm on rm.id = b.id
 inner join sierra_view.leader_field ldr on ldr.record_id = b.id
 where (v.field_content !~ '\|a' or v.field_content ~ '\|a.*\|a')
 order by creation_date_gmt

bib variable fields that are too long (primarily used to identify long notes fields that need to be split up for endeca ingest)

based on char length 

 select
 'b' || b.record_num as bnum, octet_length(v.field_content) as octet_length, length(v.field_content) as char_length, *
 from
     sierra_view.varfield v, sierra_view.bib_view as b
 WHERE 
     length(v.field_content) >= 2985
     AND b.id = v.record_id;

based on octet length 

 select
 'b' || b.record_num as bnum, octet_length(v.field_content) as octet_length, length(v.field_content) as char_length, *
 from
     sierra_view.varfield v, sierra_view.bib_view as b
 WHERE 
     octet_length(v.field_content) >= 3950
     AND b.id = v.record_id;
  • measuring the octet length directly cuts the number of results down to ~680 from ~2400, and should still identify fields with a length of >4k bytes. Perhaps a useful alternative, perhaps not.

items with barcodes that do not match known valid barcode patterns 

 select 'i' || i.record_num AS inum,
        i.location_code,
        i.item_status_code,
        r.record_last_updated_gmt,
        vc.field_content AS "callnumber",
        vv.field_content AS "volume",
        v.field_content AS "barcode"
 from sierra_view.varfield v
 left join sierra_view.item_view i
 on v.record_id = i.id 
 left outer join sierra_view.varfield vc on v.record_id = vc.record_id and vc.varfield_type_code = 'c'
 left outer join sierra_view.varfield vv on v.record_id = vv.record_id and vv.varfield_type_code = 'v'
 left join sierra_view.record_metadata r on i.id = r.id
 where v.varfield_type_code = 'b'
 and NOT i.item_status_code = 'w'
 and NOT v.field_content ~ '^0\d{10}$' /* AAL Mill normal 11-char */
 and NOT v.field_content ~ '^1\d{10}$' /* AAL Mill on-the-fly 11-char */
 and NOT v.field_content ~ '^H[ABX][A-Z]{2}-\d{4}-\d{5}$' /* AAL DRA autogenerated placeholder */
 and NOT v.field_content ~ '^L[AB][A-Z]{2}-\d{4}-\d{5}$' /* AAL DRA loaned */
 and NOT v.field_content ~ '^L\d{8}[A-Z]$' /* LAW Mill 10-char with ending letter */
 and NOT v.field_content ~ '^H\d{8}[A-Z]$' /* HSL Mill 10-char with ending letter */
 and NOT v.field_content ~ '^CPC\d{8}$' /* Carolina Pop Ctr Mill*/
 and NOT v.field_content ~ '^PL\d{8}$' /* Park Library Mill*/
 and NOT v.field_content ~ '^9\d{10}$' /* Hwy Saftey Mill*/
 and NOT v.field_content ~ '^\d{8}$' /* ESM PRR serials barcode (bnum without the b on the front) */
 and NOT v.field_content ~ '^HAYA-\d{4}-\d{6}$' /* Gov doc serial items never inventoried/barcoded by staff */
 /* The following are NOT in Wanda's list of valid patterns, but there are a lot of them */
 and NOT v.field_content ~ '^L\d{9}$' /* LAW Mill 10-char, no ending letter: L003987970 */
 and NOT v.field_content ~ '^L\d{8}(-|[%.$+`])$' /* LAW Mill 10-char, ending punctuation: 
   L00399687% L00399456- L00399457. L00399459$ L00399479+ */
 and NOT v.field_content ~ '^H\d{8}(-|[%.$+`])$' /* HSL Mill 10-char, ending punctuation: 
   H00098769$ H00467879+ H00158797. H00129996- */
 and NOT v.field_content ~ '^L\d{8}$' /* LAW Mill 9-char, no ending letter: L00399485 */
 and NOT v.field_content ~ '^H\d{8}$' /* HSL Mill 9-char, no ending letter: H00099758 */
 and NOT v.field_content ~ '^H\d{9}$' /* HSL Mill 10-char, no ending letter: H001130005 */
 and NOT v.field_content ~ '^[01]\d{10}0+$' /* AAL Mill 11-char followed by addtl 0s: 000030389101 */
 and NOT v.field_content ~ '^[01]\d{11}$' /* AAL Mill 12-char: 000455125050 */
 and NOT v.field_content ~ '^[0]\d{9}$' /* AAL Mill normal 10-char: 0000337460 */
 and NOT v.field_content ~ '^[0]\d{8}$' /* AAL Mill normal 9-char: 000362186 */
 and NOT v.field_content ~ '^[01]\d{10}(-|[%.$+`])$' /* AAL Mill 11-char followed by punctuation: 
   00046484641- 00038189888` */
 ;

bib 590s (all) from only records where a 590 has a repeated $a and 001 beginning with sse

Use as pattern for any time you need to get all occurrences of a field from only those records where at least one instance of that field meets criteria 

 SELECT 'b' || b.record_num || 'a' as bnum,
         v.field_content as note
 FROM
     sierra_view.varfield v
 INNER JOIN sierra_view.bib_view b
     ON b.id = v.record_id
 WHERE v.marc_tag = '590'
     AND b.bcode3 NOT IN ('d', 'n', 'c')
     AND EXISTS (select * from sierra_view.varfield vo
                 where v.record_id = vo.record_id
                 and vo.marc_tag = '001'
                 and vo.field_content like 'sse%')
     AND EXISTS (select * from sierra_view.varfield vd
                 where v.record_id = vd.record_id
                 and vd.marc_tag = '590'
                 and vd.field_content ~ '\|a.*\|a')
                 ;

bib 500s and 588s for ebooks without law, hsl, or wilson locations, and not from marcive or serialssolutions, which have 'title from' in the field 

 select 'b' || b.record_num || 'a' AS bnum,
        vc.field_content as coll,
        locs.locations,
        v.marc_tag,
        v.marc_ind1,
        v.marc_ind2,
        v.field_content
 from sierra_view.varfield v
 inner join sierra_view.bib_view b on v.record_id = b.id
 inner join sierra_view.bib_record_property bp on v.record_id = bp.bib_record_id and bp.material_code = 'z'
         inner join (select bib_record_id, STRING_AGG(Trim(trailing FROM location_code), ', ' order by id) AS locations
         FROM   sierra_view.bib_record_location
                 where location_code != 'multi'
                    GROUP  BY bib_record_id) locs
                ON b.id = locs.bib_record_id
 left outer join sierra_view.varfield vg on v.record_id = vg.record_id and vg.marc_tag = '040'
 left outer join sierra_view.varfield vc on v.record_id = vc.record_id and vc.marc_tag = '773' and vc.field_content like '%online collection%'
 where
   v.marc_tag in ('500', '588')
   and v.field_content like '%title from%'
   and vg.field_content not like '%WaSeSS%'
   and vg.field_content not like '%GPO%'
   and b.cataloging_date_gmt is not null
   and NOT EXISTS (select * from sierra_view.bib_record_location ll
                   WHERE v.record_id = ll.bib_record_id
                   and (ll.location_code like 'k%'
            or ll.location_code like 'no%'
            or ll.location_code like 'w%')
       )
   ;

bib 008s - list of unique language codes used in catalog 

 select
  DISTINCT(concat(c.p35, c.p36, c.p37)) as lang_code
 from
  sierra_view.control_field c, sierra_view.bib_record b
 where c.control_num = '8'
     AND b.record_id = c.record_id
 ;
  • Using concat(n1, n2, n3) rather than n1 || n2 || n3 preserves spaces (i.e. does not collapse "n e" into "ne")

summary of local subject & genre headings used in unsuppressed mrc bib records 

 SELECT
   tmp.marc_tag,
   tmp.heading,
   COUNT(*)
 FROM
   (
     SELECT
       v.marc_tag,
       btrim(
         REPLACE(
           REPLACE(
             v.field_content,
             '|2local',
             ''
           ),
           '|a',
           ''
         ),
         ' '
       )AS heading
     FROM
       sierra_view.varfield v
     INNER JOIN sierra_view.bib_record_location loc ON loc.bib_record_id = v.record_id
     AND loc.location_code = 'ul'
     INNER JOIN sierra_view.bib_record br ON br. ID = v.record_id
     AND br.is_suppressed = 'f'
     WHERE
       v.marc_tag IN('655', '690')
     AND v.field_content LIKE '%|2local%'
   )tmp
 GROUP BY
   1,
   2
 ORDER BY
   COUNT(*)DESC

local 690 fields in records without mrc location

Used to determine whether such 690s tend to be genre or subject headings. 

 SELECT
   v.field_content,
   COUNT(v.field_content)
 FROM
   sierra_view.varfield v
 INNER JOIN sierra_view.bib_record br ON br. ID = v.record_id
 AND br.is_suppressed = 'f'
 INNER JOIN sierra_view.record_metadata rm ON rm. ID = v.record_id
 WHERE
   v.marc_tag = '690'
 AND v.field_content LIKE '%|2local%'
 AND NOT EXISTS(
   SELECT
     1
   FROM
     sierra_view.bib_record_location bl
   WHERE
     bl.bib_record_id = v.record_id
   AND bl.location_code IN ('ul', 'ulz')
 )
 GROUP BY
   v.field_content
 ORDER BY
   COUNT(v.field_content)DESC

get endeca bnum input for extract for list of bibs 

 select 'b' || record_num AS bnum,
        to_char(cataloging_date_gmt, 'YYYYMMDD'),
        bcode2
 from sierra_view.bib_view
 where record_num in ('4895022', '5191508', '6227008');

bnums and 001s for selected collections 

 select 'b' || rm.record_num || 'a' as bnum, v001.field_content
 from sierra_view.varfield v
 inner join sierra_view.record_metadata rm on rm.id = v.record_id
 inner join sierra_view.varfield v001 on v001.record_id = rm.id and v001.marc_tag = '001'
 where v.marc_tag = '773'
   and v.field_content like '%|tEbscohost ebooks (online collection). Via NCLive%'

get all isbns from selected collections (e.g. for jstor dda) 

 SELECT 'b' || rm.record_num || 'a' as bnum, v.field_content as collection,
        vi.field_content as isbn, vt.field_content as title
 FROM sierra_view.varfield v
 INNER JOIN sierra_view.varfield vi on vi.record_id = v.record_id and vi.marc_tag = '020'
 LEFT OUTER JOIN sierra_view.varfield vt on vt.record_id = v.record_id and vt.marc_tag = '245'
 INNER JOIN sierra_view.bib_record b on v.record_id = b.id
 INNER JOIN sierra_view.record_metadata rm on rm.id = b.id
 WHERE v.marc_tag = '773'
      and (v.field_content like '%|tEbscohost ebooks (online collection). HSL Via YBP%'
     or v.field_content like '%|tEbrary perpetual access (online collection). UNL title-by-title%'
     or v.field_content like '%|tEbrary perpetual access (online collection). UNL via YBP%')

get bibs with cat date in the future

Adjust date as needed: 

 SELECT 'b' || b.record_num || 'a' as bib_rec_key,
        locs.locations,
        to_char(b.cataloging_date_gmt, 'YYYY-MM-DD') as "cat_date",
        to_char(b.record_creation_date_gmt, 'YYYY-MM-DD') as "created", 
        to_char(r.record_last_updated_gmt, 'YYYY-MM-DD') as "updated"		
 FROM sierra_view.bib_view b
        inner join (select bib_record_id, STRING_AGG(Trim(trailing FROM location_code), ', ' order by id) AS locations
                FROM   sierra_view.bib_record_location
                where location_code != 'multi'
                   GROUP  BY bib_record_id) locs
               ON b.id = locs.bib_record_id,
       sierra_view.record_metadata r
 WHERE b.cataloging_date_gmt > '2017-01-26'::date
       and r.id = b.id

get Elvl code for E-Resources within a particular collection

In this example it is the wcm collection. Update the collection name as needed. You can also restrict by bcode3. Uncomment the bcode3 condition and update as needed. 

select DISTINCT 'b' || rm.record_num || 'a' AS bnum,
        b.bcode3,
        ldr.encoding_level_code as elvl,
        v.field_content
 from sierra_view.varfield v
   inner join sierra_view.bib_record b ON b.id = v.record_id
   inner join sierra_view.record_metadata rm on rm.id = b.id
   inner join sierra_view.bib_record_property brp ON brp.bib_record_id = b.id
   inner join sierra_view.leader_field ldr on ldr.record_id = b.id
 where 
    v.marc_tag = '773'and v.field_content like'|tOCLC WorldShare Collection Manager managed collection.%'
    and brp.material_code = 'z'
    --and b.bcode3 in ('x','d','n','c','@','t')

holdings record 866 fields (iii field tag = h) where the holdings record has more than 2 such 866 fields 

 SELECT
   *
 FROM
   sierra_view.varfield_view v
 INNER JOIN(
   SELECT
     vv.record_num
   FROM
     sierra_view.varfield_view vv
   WHERE
     vv.record_type_code = 'c'
   AND vv.varfield_type_code = 'h'
   AND vv.marc_tag = '866'
   GROUP BY
     vv.record_num
   HAVING
     COUNT(*)> 2
 )vvv ON vvv.record_num = v.record_num
 WHERE
   v.record_type_code = 'c'
 AND v.varfield_type_code = 'h'
 AND v.marc_tag = '866'
 ORDER BY
   v.record_num,
   v.occ_num ASC;

holdings record numbers where holdings record is attached to a serial bib, is unsuppressed, contains more than one (h)863 field, and contains (h)866 field 

 SELECT
   'c' || hr.record_num
 FROM
   sierra_view.holding_view hr
 INNER JOIN sierra_view.bib_record_holding_record_link bhl ON hr. ID = bhl.holding_record_id
 INNER JOIN sierra_view.leader_field ldr ON bhl.bib_record_id = ldr.record_id
 AND ldr.bib_level_code = 's'
 INNER JOIN sierra_view.varfield vf ON hr. ID = vf.record_id
 AND vf.varfield_type_code = 'h'
 AND vf.marc_tag = '863'
 AND NOT EXISTS(
   SELECT
     *
   FROM
     sierra_view.varfield v2
   WHERE
     v2.record_id = vf.record_id
   AND v2.varfield_type_code = 'h'
   AND v2.marc_tag = '866'
 )
 WHERE
   hr.is_suppressed = 'f'
 INTERSECT
   SELECT
     'c' || vv.record_num
   FROM
     sierra_view.varfield_view vv
   WHERE
     vv.record_type_code = 'c'
   AND vv.varfield_type_code = 'h'
   AND vv.marc_tag = '863'
   GROUP BY
     vv.record_num
   HAVING
     COUNT(*)> 1

unsuppressed monograph bibs with >2 unsuppressed holdings attached, where bib locations include k*, no*, or mm*, with count of unsuppressed holdings 

 SELECT
   'b' || rm.record_num || 'a' AS bnum,
   aggct.hct
 FROM
   (
     SELECT
       ubh.bib_record_id,
       COUNT(ubh.bib_record_id)AS hct
     FROM
       (
         SELECT DISTINCT
           bh.bib_record_id,
           bh.holding_record_id
         FROM
           sierra_view.bib_record_holding_record_link bh
         INNER JOIN sierra_view.holding_record hr ON hr. ID = bh.holding_record_id
         AND hr.is_suppressed = 'f'
         INNER JOIN sierra_view.bib_record br ON br. ID = bh.bib_record_id
         AND br.is_suppressed = 'f'
         AND br.bcode1 = 'm'
       )ubh
     GROUP BY
       ubh.bib_record_id
     HAVING
       COUNT(*)> 2
   )aggct
 INNER JOIN sierra_view.record_metadata rm ON rm. ID = aggct.bib_record_id
 WHERE
   EXISTS(
     SELECT
       *
     FROM
       sierra_view.bib_record_location ll
     WHERE
       aggct.bib_record_id = ll.bib_record_id
     AND(
       ll.location_code LIKE 'mm%'
       OR ll.location_code LIKE 'no%'
       OR ll.location_code LIKE 'mm%'
     )
   )

mono bibs, unsuppressed holdings with no unsuppressed items

Unsuppressed monograph bibs, with unsuppressed holdings for a location, which don't have any unsuppressed items for that location. Some locations are non-problematic and excluded. Display problems in search.lib.unc.edu 

 SELECT DISTINCT
   'b' || rmb.record_num || 'a' as bnum,
   'c' || rmc.record_num || 'a' as cnum,
   --bh.bib_record_id,
   --bh.holding_record_id,
   hL.location_code as problem_location,
   'http://search.lib.unc.edu/search?R=UNCb' || rmb.record_num as url
   --holddisp.location_code as holdings_display
 FROM
   sierra_view.bib_record_holding_record_link bh
 INNER JOIN sierra_view.holding_record hr ON hr. ID = bh.holding_record_id
   AND hr.is_suppressed = 'f'
 INNER JOIN sierra_view.holding_record_location hL ON hL.holding_record_id = bh.holding_record_id
   AND hL.location_code not like 'n%'
   AND hL.location_code not like 'k%'
   AND hL.location_code not like '%z'
 INNER JOIN sierra_view.bib_record br ON br. ID = bh.bib_record_id
   AND br.is_suppressed = 'f'
   AND br.bcode1 = 'm'
 INNER JOIN sierra_view.record_metadata rmb ON rmb.id = bh.bib_record_id
 INNER JOIN sierra_view.record_metadata rmc ON rmc.id = bh.holding_record_id
 INNER JOIN ( select ibh.bib_record_id, ihL.location_code
             from
               sierra_view.bib_record_holding_record_link ibh
             INNER JOIN sierra_view.holding_record ihr ON ihr. ID = ibh.holding_record_id
               AND ihr.is_suppressed = 'f'
             INNER JOIN sierra_view.holding_record_location ihL ON ihL.holding_record_id = ibh.holding_record_id
             WHERE
                ihL.location_code like 'mm%'
             or ihL.location_code like 'n%'
             or ihL.location_code like 'k%' ) holddisp on holddisp.bib_record_id = bh.bib_record_id
 WHERE NOT EXISTS(
       SELECT *
       FROM sierra_view.holding_record_item_record_link hi --hi!
       INNER JOIN sierra_view.item_record ir on ir.id = hi.item_record_id
         AND ir.is_suppressed = 'f'
       WHERE hr.id = hi.holding_record_id
       )
 ORDER BY hL.location_code ASC

bibs where marc field (e.g. 773) has incorrect field group tag (e.g. 'w') 

 select * from sierra_view.varfield_view
 where marc_tag = '773'
 and record_type_code = 'b'
 and varfield_type_code != 'w'

marc fields containing potential html tags 

 SELECT 'b' || rm.record_num || 'a' as bnum, v001.field_content as _001, v.marc_tag, v.field_content
 FROM sierra_view.varfield v
 INNER JOIN sierra_view.bib_record b on b.record_id = v.record_id
   AND b.bcode3 not in ('d', 'c')
 INNER JOIN sierra_view.record_metadata rm on rm.id = b.id
 INNER JOIN sierra_view.varfield v001 on v001.record_id = v.record_id
   AND v001.marc_tag = '001'
   AND v001.field_content not ilike 'ss%'
 WHERE
   v.field_content ~ '<[/a-zA-Z]'

marc fields containing potential html entities 

 SELECT 'b' || rm.record_num || 'a' as bnum, v001.field_content as _001, b.bcode3,
   v.marc_tag, v.field_content
 FROM sierra_view.varfield v
 INNER JOIN sierra_view.bib_record b on b.record_id = v.record_id
   AND b.bcode3 not in ('d', 'n', 'c')
 INNER JOIN sierra_view.record_metadata rm on rm.id = b.id
 INNER JOIN sierra_view.varfield v001 on v001.record_id = v.record_id
   AND v001.marc_tag = '001'
   AND v001.field_content not ilike 'ss%'
 WHERE
   v.field_content ~ '&#?[a-z0-9]{2,25};'

grouped bib creation date for recs w/ specific 773

normalize the 773 and prepend the index_tag ('t') 

 SELECT DATE_TRUNC('day', record_creation_date_gmt), count(*)
 FROM sierra_view.phrase_entry phe
 INNER JOIN sierra_view.bib_view b ON b.id = phe.record_id
 WHERE phe.index_tag || phe.index_entry = 'toecd ilibrary online collection'
 GROUP BY DATE_TRUNC('day', record_creation_date_gmt)
 ORDER BY DATE_TRUNC('day', record_creation_date_gmt)

suppressed e-bibs (by location) with under authority control 915 

 select distinct 'b' || rm.record_num || 'a'
 from sierra_view.bib_record b
 inner join sierra_view.record_metadata rm on rm.id = b.id
 inner join sierra_view.bib_record_location bl on bl.bib_record_id = b."id"
   and (bl.location_code = 'eb' or bl.location_code = 'er')
 inner join sierra_view.varfield v on v.record_id = b."id"
   and v.marc_tag = '915' and v.field_content like '%Under Authority Control%'
 where b.bcode3 = 'n'

find collections missing 506 fields

Returns: 

 - collection title from 773 field
 - count of all bib record with that 773 title
 - count of all bib records with that 773 title AND a UNC 506 field
 - difference between collection count and count of records with 506

 SELECT
   fullct.colltitle as collectionTitle,
   fullct.fct as recsInCollection,
   authct.act as recsWith506,
   (fullct.fct - authct.act) as difference
 FROM
   (
     SELECT
       tmp1.colltitle AS colltitle,
       COUNT(*)AS fct
     FROM
       (
         SELECT
           v1.field_content AS colltitle
         FROM
           sierra_view.varfield v1
         WHERE
           v1.marc_tag = '773'
         AND v1.field_content ~ '\(online collection\)|Undergraduate library Kindle ebook collection'
       )tmp1
     GROUP BY
       1
     ORDER BY
       tmp1.colltitle ASC
   )fullct
 left OUTER JOIN
   (
     SELECT
       tmp2.colltitle AS colltitle,
       COUNT(*)AS act
     FROM
       (
         SELECT
           v1.field_content AS colltitle
         FROM
           sierra_view.varfield v1
         WHERE
           v1.marc_tag = '773'
         AND v1.field_content ~ '\(online collection\)|Undergraduate library Kindle ebook collection'
         AND EXISTS(
           SELECT
             *
           FROM
             sierra_view.varfield v2
           WHERE
             v2.record_id = v1.record_id
           AND v2.marc_tag = '506'
           AND v2.field_content ~ '\|aAccess limited to UNC Chapel Hill-authenticated users\.'
         )
       )tmp2
     GROUP BY
       1
   ) authct ON authct.colltitle = fullct.colltitle
 ORDER BY difference DESC

diff two create lists / review files

Supply list numbers in first CTE. 

 with lists as (select 222 as list1   --enter list numbers here
                     , 242 as list2   --enter list numbers here
      ),
      diff as (
        select bs.record_metadata_id
            , case when bs2.id is null then bs.bool_info_id::varchar
                   else 'both'
              end as appears
            , case when bs2.id is null then bsi.name
                   else 'both'
              end as name
        from sierra_view.bool_set bs
        cross join lists
        left join sierra_view.bool_set bs2 on bs2.record_metadata_id = bs.record_metadata_id
          and bs2.bool_info_id = lists.list2
        inner join sierra_view.bool_info bsi on bsi.id = lists.list1
        where bs.bool_info_id = lists.list1
        -- 
        UNION
        --
        select bs2.record_metadata_id
             , bs2.bool_info_id::varchar as appears
             , bsi.name
        from sierra_view.bool_set bs2
        cross join lists
        inner join sierra_view.bool_info bsi on bsi.id = lists.list2
        where bs2.bool_info_id = lists.list2
          and not exists (
            select *
            from sierra_view.bool_set
            where record_metadata_id = bs2.record_metadata_id
              and bool_info_id = lists.list1
          )
      ) --end diff
 select rm.record_type_code || rm.record_num || 'a' as rnum, diff.appears, diff.name
 from diff
 inner join sierra_view.record_metadata rm on rm.id = diff.record_metadata_id
 order by diff.appears

erm resource records w/ license info

This creates a list of resources and their license(s) if any. A separate record/row exists for each resource/license pair. When a resource or license record has multiple instances of the same kind of variable field, the STRING_AGG function is combining the values of each instance into a single result, with the values being separated by: "; " (semi-colon space). So a resource record with three separate subject fields in Sierra might have a "subjects" field in these results that looks like "Biology; Chemistry; Ecology". Resources with no license records attached can be excluded by changing one of the left joins to an inner join. 

 SELECT 'e' || rm.record_num || 'a' AS resource_num,
       (SELECT STRING_AGG(v.field_content, '; ' order by occ_num)
          FROM sierra_view.varfield v
          WHERE v.record_id = r.id
          AND v.varfield_type_code = 't'
       ) AS title,
       (SELECT STRING_AGG(v.field_content, '; ' order by occ_num)
          FROM sierra_view.varfield v
          WHERE v.record_id = r.id
          AND v.varfield_type_code = 'd'
       ) AS subject,
       (SELECT STRING_AGG(v.field_content, '; ' order by occ_num)
          FROM sierra_view.varfield v
          WHERE v.record_id = r.id
          AND v.varfield_type_code = 'y'
       ) AS link,
       (SELECT STRING_AGG(v.field_content, '; ' order by occ_num)
          FROM sierra_view.varfield v
          WHERE v.record_id = r.id
          AND v.varfield_type_code = 'e'
       ) AS description,
        'l' || rml.record_num || 'a' AS license_num,
       (SELECT STRING_AGG(v.field_content, '; ' order by occ_num)
          FROM sierra_view.varfield v
          WHERE v.record_id = rll.license_record_id
          AND v.varfield_type_code = 'u'
       ) AS authorized_users,
       (SELECT STRING_AGG(v.field_content, '; ' order by occ_num)
          FROM sierra_view.varfield v
          WHERE v.record_id = rll.license_record_id
          AND v.varfield_type_code = 't'
       ) AS terms_of_use,
       (SELECT STRING_AGG(v.field_content, '; ' order by occ_num)
          FROM sierra_view.varfield v
          WHERE v.record_id = rll.license_record_id
          AND v.varfield_type_code = 'v'
       ) AS terms_notes
 FROM sierra_view.resource_record r
 INNER JOIN sierra_view.record_metadata rm ON rm.id = r.id
 LEFT JOIN sierra_view.resource_record_license_record_link rll ON rll.resource_record_id = rm.id
 LEFT JOIN sierra_view.record_metadata rml ON rml.id = rll.license_record_id
 ORDER BY rm.record_num

Open Orders. (Orders with status 'o' or 'q')

Courtesy of Nicole Allen and Jeremy Goldstein on the Sierra Discord Server 

SELECT
id2reckey(o.record_id)||'a' as OrderNum,
b.best_title AS title,
o.order_date_gmt::DATE AS order_date,
o.order_status_code AS status,
o.vendor_record_code AS vendor,
cmf.copies,
STRING_AGG (DISTINCT f.fund_code,', ') AS fund,
COALESCE(vv.field_content) as published,
COALESCE(string_agg(vz.field_content,',')) AS staffnote

FROM
sierra_view.order_record o 
JOIN sierra_view.bib_record_order_record_link l ON o.id = l.order_record_id
JOIN sierra_view.bib_record_property b ON l.bib_record_id = b.bib_record_id
JOIN sierra_view.order_record_cmf cmf ON o.id = cmf.order_record_id 
JOIN sierra_view.accounting_unit a ON o.accounting_unit_code_num = a.code_num
JOIN sierra_view.fund_master fm ON fm.code_num = cmf.fund_code::INT AND fm.accounting_unit_id = a.id
JOIN sierra_view.fund f ON fm.code = f.fund_code AND f.fund_type = 'fbal'
JOIN sierra_view.fund_property fp ON fm.id = fp.fund_master_id AND fp.fund_type_id = '1'
left JOIN sierra_view.varfield vv ON vv.record_id = o.record_id AND vv.varfield_type_code = 'n' 
left JOIN sierra_view.varfield vz ON vz.record_id = o.record_id AND vz.varfield_type_code = 'z' 

WHERE 
order_status_code IN ('o','q')

GROUP BY 
o.record_id,
b.best_title,
o.order_date_gmt::DATE,
o.order_status_code,
o.vendor_record_code,
cmf.copies,
COALESCE(vv.field_content)

ORDER BY 8 ;

holdings/item location mismatches, problem ac-az_call_no/location records

Three queries collected here for now. It's not clear if these need to be scoped differently or what fields need to be output.

holdings/item location mismatches 

 select distinct 'c' || rm.record_num || 'a' as cnum, hl.location_code,-- i.location_code,
  (select string_agg(distinct subi.location_code, '; ')
           from sierra_view.holding_record_item_record_link sublink
           inner join sierra_view.item_record subi on subi.id = sublink.item_record_id and subi.is_suppressed = 'f'
  					where  sublink.holding_record_id = h.id
  ) as attached_item_locs,
  (select   case when count(*) > 10 then 'more than 10'
           else string_agg(distinct 'i' || rmi.record_num || 'a', '; ')
            end
           from sierra_view.holding_record_item_record_link sublink
           inner join sierra_view.item_record unmatchedi on unmatchedi.id = sublink.item_record_id and unmatchedi.is_suppressed = 'f' and unmatchedi.location_code != hl.location_code
  					inner join sierra_view.record_metadata rmi on rmi.id = unmatchedi.id
 					where  sublink.holding_record_id = h.id
  ) as attached_nonmatching_inums,
  'b' || rmb.record_num || 'a' as bnum, b.bcode1 as blvl
  from sierra_view.holding_record h
  inner join sierra_view.holding_record_location hl on hl.holding_record_id = h.id
  inner join sierra_view.holding_record_item_record_link hil on hil.holding_record_id = h.id
  inner join sierra_view.record_metadata rm on rm.id = h.id
  inner join sierra_view.item_record i on i.id = hil.item_record_id and i.is_suppressed = 'f' and i.location_code != hl.location_code
  inner join sierra_view.bib_record_holding_record_link bhl on bhl.holding_record_id = h.id
 inner join sierra_view.bib_record b on b.id = bhl.bib_record_id and b.bcode3 not in ('d', 'n', 'c')
  inner join sierra_view.record_metadata rmb on rmb.id = bhl.bib_record_id
 where h.is_suppressed = 'f'
    and (b.bcode1 in ('b', 'i', 's') or hl.location_code ~ '^(m|n)' or i.location_code ~ '^(m|n)')
 order by hl.location_code, attached_item_locs, cnum

davis ac-az holdings records bad location 

 select 'i' || rm.record_num || 'a' as inum, 'b' || rmb.record_num || 'a' as bnum, b.bcode1, b.bcode3, i.location_code, v.field_content as item_callno, regexp_replace(v.field_content, '\|.', ' ', 'g') as cleaner_callno
 from sierra_view.item_record i
 inner join sierra_view.varfield v on v.record_id = i.id and v.varfield_type_code = 'c'
 inner join sierra_view.subfield sf on sf.varfield_id = v.id and sf.tag ='a' and sf.content ~* '^A'
 inner join sierra_view.record_metadata rm on rm.id = i.id
 inner join sierra_view.bib_record_item_record_link bil on bil.item_record_id = i.id
 inner join sierra_view.bib_record b on b.id = bil.bib_record_id
 inner join sierra_view.record_metadata rmb on rmb.id = b.id
 where i.location_code in ('ddca', 'ddcb', 'ddcc', 'ddda', 'dhca')
 order by v.field_content ASC

davis ac-az item records bad location 

 select 'i' || rm.record_num || 'a' as inum, 'b' || rmb.record_num || 'a' as bnum, b.bcode1, b.bcode3, i.location_code, v.field_content as item_callno, regexp_replace(v.field_content, '\|.', ' ', 'g') as cleaner_callno
 from sierra_view.item_record i
 inner join sierra_view.varfield v on v.record_id = i.id and v.varfield_type_code = 'c'
 inner join sierra_view.subfield sf on sf.varfield_id = v.id and sf.tag ='a' and sf.content ~* '^A'
 inner join sierra_view.record_metadata rm on rm.id = i.id
 inner join sierra_view.bib_record_item_record_link bil on bil.item_record_id = i.id
 inner join sierra_view.bib_record b on b.id = bil.bib_record_id
 inner join sierra_view.record_metadata rmb on rmb.id = b.id
 where i.location_code in ('ddca', 'ddcb', 'ddcc', 'ddda', 'dhca')
 order by v.field_content ASC

ethiopic script outside of 880 (records break endeca forge) 

 select 'b' || rm.record_num || 'a', *
 from sierra_view.varfield v
 inner join sierra_view.record_metadata rm on rm.id = v.record_id and rm.record_type_code = 'b'
 where v.marc_tag != '880'
   and v.field_content ~ '[\u1200-\u137F]'

identify lsc holds and the lsc_holds.pl run that reported them 

select
  case
  when h2.run = 'late'
    then to_char(placed_gmt, 'YYYYMMDD')
  when to_char(placed_gmt, 'dy') = 'sat'
    then to_char(placed_gmt + INTERVAL '2 days', 'YYYYMMDD')
  when to_char(placed_gmt, 'dy') = 'sun'
    then to_char(placed_gmt + INTERVAL '1 days', 'YYYYMMDD')
  when placed_gmt::time < make_time(6, 30,0)
    then to_char(placed_gmt, 'YYYYMMDD')
  else to_char(placed_gmt + INTERVAL '1 days', 'YYYYMMDD')
  end as run_date
, h2.run
--, to_char(placed_gmt, 'dy') as day_placed
, i.barcode as item_barcode
, p.barcode as patron_barcode
, concat_ws (' ', pnf.last_name, pnf.first_name, pnf.middle_name, pnf.suffix) as patron
, h.placed_gmt
, brp.best_title as title
, brp.best_author as author
--, *
from sierra_view.hold h
inner join ( select id,
  case
  when to_char(placed_gmt, 'dy') in ('sat', 'sun')
    then 'early'
  when placed_gmt::time >= make_time(6, 30,0) and placed_gmt::time < make_time(12, 0,0)
    then 'late'
  else 'early'
  end as run
from sierra_view.hold ) h2 on h2.id = h.id
inner join sierra_view.item_view i on i.id = h.record_id
inner join sierra_view.patron_view p on p.id = h.patron_record_id
inner join sierra_view.patron_record_fullname pnf on pnf.patron_record_id = p.id
inner join sierra_view.bib_record_item_record_link bil on bil.item_record_id = i.id
inner join sierra_view.bib_record b on b.id = bil.bib_record_id
inner join sierra_view.bib_record_property brp on brp.bib_record_id = b.id
and i.location_code in ('trln', 'troup')
--where to_char(placed_gmt, 'dy') = 'friday'
order by h.placed_gmt

annual giftcat report for fiscal services

This pulls data that Fiscal Services needs to report to the State each year. To use this code, the USER MUST UPDATE THE DATES TO REFLECT THE APPROPRIATE TIME PERIOD. Those reporting periods may change year to year. Then within that time frame the query looks for items with "Giftcat" in the Item Notes (x) or Item Gift (w) fields. It skips various locations: HSL, Law, the Wilson Specials (cuz they submit their own report) and the non-UnL affiliates like Highway Safety, Park Library of Journalism, NC Botanical Gardens... and it skips items that are backlogged or in process. It sorts on GiftNotes, then Location 

SELECT
   iv.barcode,
   'i'||iv.record_num||'a' AS ItemNumber,
   to_char(
	iv.record_creation_date_gmt,
	'MM/DD/YYYY'
   ) AS CreatedDate,
   iv.agency_code_num,
   iv.location_code,
   iv.item_status_code,
   iv.itype_code_num AS ItemType,
   (	
        SELECT STRING_AGG(v.field_content, ' ; ')
        FROM sierra_view.varfield v
        WHERE v.record_id = iv.id
        AND v.varfield_type_code = 'j'
     ) AS StatsNotes,
   (
        SELECT STRING_AGG(v.field_content, ' ; ')
        FROM sierra_view.varfield v
        WHERE v.record_id = iv.id
        AND v.varfield_type_code = 'w'
     ) AS GiftNotes

FROM
  sierra_view.item_view iv
  INNER JOIN sierra_view.varfield v on v.record_id = iv.id
 WHERE
   (v.varfield_type_code = 'j' AND v.field_content ILIKE '%Cataloged FY 2020-21%')
	AND EXISTS
		(Select * from sierra_view.varfield vo 
                 where v.record_id = vo.record_id
		AND vo.varfield_type_code='w' and vo.field_content ILIKE '%Gift%')
 		AND iv.location_code NOT IN ('k%','y%','hh%','wa%','wb%','wc%','xb%','xc%','xd%')
		AND iv.item_status_code NOT IN ('e','w','d','p','b')
ORDER BY
	GiftNotes, iv.location_code ASC

Cat Stats annual pull for analytics and boundwiths

This generates a row for each bib that an item is attached to. You must update the Cat Stats year note for each fiscal year. 

SELECT
   'i' || i.record_num ||'a' AS iNum, 
   i.location_code AS iLoc,
   i.icode2 as LinkedOrNot, 
   i.itype_code_num AS Itype,
   i.item_status_code,
   (
      SELECT STRING_AGG(v.field_content, ' ; ')
      FROM sierra_view.varfield v
      WHERE v.record_id = i.id
      AND v.varfield_type_code = 'x'
    ) AS XNotes,
   (  
      SELECT STRING_AGG(v.field_content, ' ; ')
      FROM sierra_view.varfield v
      WHERE v.record_id = i.id
      AND v.varfield_type_code = 'j'
    ) AS StatsNotes,
  'b' || b.record_num || 'a' AS bNum,
   to_char(b.cataloging_date_gmt, 'YYYY-MM-DD') as "cat_date",
   (
     SELECT STRING_AGG(Trim(trailing FROM brl.location_code), ', ')
     FROM sierra_view.bib_record_item_record_link br
		INNER JOIN sierra_view.bib_record_location brl on brl.bib_record_id =  br.bib_record_id
		WHERE br.item_record_id=i.id
		AND brl.location_code != 'multi'
   ) AS BibLoc
   
FROM sierra_view.item_view i
INNER JOIN sierra_view.varfield v on v.record_id = i.id
inner join sierra_view.bib_record_item_record_link bil on bil.item_record_id = i.id
inner join sierra_view.bib_view b on b.id = bil.bib_record_id
WHERE
     (varfield_type_code='j' and v.field_content ILIKE '%Cataloged FY 2024-25%')
     AND i.item_status_code NOT IN ('b','p') -- leave in withdrawn found, they will be taken out in the withdrawn reprot
     AND i.location_code NOT LIKE 'dqda' --weeds out global browse
     AND i.location_code NOT LIKE 'yh%' --weeds out latin american film library
     AND i.location_code NOT LIKE 'err%' --weeds out electronic that will be pulled under eresources
     AND i.icode2 in ('b','l') -- restricts to the boundwith analytics only
    
ORDER BY b.record_num

Bib records where Sierra converted Cyrillic letter to '{' in the 880 fields 

Select 'b' || rm.record_num || 'a' as bnum,
 marc_tag,
 v.field_content,
 to_char(rm.creation_date_gmt, 'YYYY-MM-DD') As "Bib Created Date",
 to_char(b.cataloging_date_gmt, 'YYYY-MM-DD') AS "Bib Cat Date",
 to_char(rm.record_last_updated_gmt, 'YYYY-MM-DD') As "Last Update"
FROM sierra_view.bib_record b 
 inner join sierra_view.varfield v on v.record_id = b."id"
 inner join sierra_view.record_metadata rm on rm.id = b.id
WHERE v.marc_tag in ('880')
  AND b.language_code IN ('rus','ukr')
  AND v.field_content ~ '\{'
  AND v.field_content !~'\}'

unsuppressed bib records with only 1 unsuppressed item, where that item is in a specified location 

 SELECT
   'b' || rm.record_num || 'a' AS bnum,
   aggct.ict
 FROM
   (
     SELECT
       ubi.bib_record_id,
       COUNT(ubi.bib_record_id)AS ict
     FROM
       (
         SELECT DISTINCT
           bi.bib_record_id,
           bi.item_record_id
         FROM
           sierra_view.bib_record_item_record_link bi
         INNER JOIN sierra_view.item_record ir ON ir. ID = bi.item_record_id
         AND ir.is_suppressed = 'f'
         AND ir.item_status_code = '-'
         AND ir.location_code like 'dn%'
         INNER JOIN sierra_view.bib_record br ON br. ID = bi.bib_record_id
         AND br.is_suppressed = 'f'
       )ubi
     GROUP BY
       ubi.bib_record_id
     HAVING
       COUNT(*) = 1
   )aggct
 INNER JOIN sierra_view.record_metadata rm ON rm. ID = aggct.bib_record_id
 WHERE
   NOT EXISTS(
     SELECT
       *
     FROM
       sierra_view.bib_record_item_record_link bi2
     INNER JOIN sierra_view.item_record i ON bi2.item_record_id = i.id
       AND i.location_code !~ '^dn'
     WHERE
       aggct.bib_record_id = bi2.bib_record_id
   )

NCC inventory

Pulls information from active NCC items (i.e. the script eliminates Withdrawn, Lost and In Process items). Provides assorted info on the Items and the Title associated with it per the request of Eileen Dewitya.

For post processing: for unique items, run dedupe on the item number (as it can get multiple entries due to linked records). Also, you can translate item type codes into human labels with a vlookup. Current item code/label table is online at https://afton.lib.unc.edu:4444/itypes/ 

SELECT
    iv.barcode,
    'i'||iv.record_num||'a' AS ItemNumber,
    iv.location_code, 
    to_char(
		iv.record_creation_date_gmt,
		'MM/YYYY'
	) AS ItemCreatedDate,
    iv.icode2,
    iv.item_status_code,
    iv.itype_code_num AS ItemType,
    (
        SELECT STRING_AGG(v.field_content, ' ; ')
        FROM sierra_view.varfield v
        WHERE v.record_id = iv.id
        AND v.varfield_type_code = 'c'
     ) AS CallNum,
     iv.copy_num,
     (
        SELECT STRING_AGG(v.field_content, ' ; ')
        FROM sierra_view.varfield v
        WHERE v.record_id = iv.id
        AND v.varfield_type_code = 'v'
     ) AS VolInfo,
    (
        SELECT STRING_AGG(v.field_content, ' ; ')
        FROM sierra_view.varfield v
        WHERE v.record_id = iv.id
        AND v.varfield_type_code = 'x'
     ) AS XNotes,
    (
        SELECT STRING_AGG(v.field_content, ' ; ')
        FROM sierra_view.varfield v
        WHERE v.record_id = iv.id
        AND v.varfield_type_code = 'w'
     ) AS GiftNotes,

    (
        SELECT STRING_AGG(v.field_content, ' ; ')
        FROM sierra_view.varfield v
        WHERE v.record_id = iv.id
        AND v.varfield_type_code = 'j'
     ) AS StatNotes,

    rp.best_title

 FROM
  sierra_view.item_view iv
  INNER JOIN sierra_view.bib_record_item_record_link br on br.item_record_id = iv.id
  INNER JOIN sierra_view.bib_record_property rp on rp.bib_record_id = br.bib_record_id
  WHERE (iv.location_code ILIKE 'wb%')	AND iv.item_status_code NOT IN ('w', 'd', 'p')

ORDER BY
   ItemNumber ASC

identify possible nuisance urls 

/*Pull bib records with possible 856 nuisance links. Run quarterly. Just update the dates in the script with each run.
You may also have to update the parameters as well. These are based on the Deletes documented in the wiki at
https://internal.lib.unc.edu/wikis/staff/index.php/F.3.1_Extended_guidelines_and_examples_for_856_indicators
*/

 SELECT 'b' || rm.record_num || 'a' AS bnum
      
 FROM   sierra_view.varfield v

 INNER JOIN sierra_view.bib_record br on br.id = v.record_id
 INNER JOIN sierra_view.record_metadata rm on rm.id = br.id

 WHERE  
			br.cataloging_date_gmt BETWEEN to_date('07/01/2017', 'MM/DD/YYYY') AND to_date('03/27/2018', 'MM/DD/YYYY')
			AND v.marc_tag = '856'
			AND	v.field_content NOT ILIKE '%|xchk%' --this removes URL's that Connie has vetted
      AND (v.field_content ILIKE ANY('{%buchcover%,%contributor biographical%,%cover image%,%klappentext%,%preliminary pages%,%publisher description%,%publisher information%,%publisher home page%,%reviews of resource%,%sample text%,%ausfuhrliche beschreibung%,%credits from Internet Movie database%,%notice et cote du catalogue%}')
      OR (v.field_content ILIKE '%more info%' AND v.field_content NOT ILIKE '%savine%' AND v.field_content NOT ILIKE '%russia beyond russia%'))

Bad GPO Permanent URLs

The GPO has restructured a number of their PURLs. However, they have not necessarily been updated in OCLC. This check is run monthly to identify any out of date PURLs that may have come in with retrocon/new cataloging. 

SELECT 'b' || rm.record_num || 'a' AS bnum,
	br.bcode3,
	v.marc_tag,
        v.marc_ind1,
        v.marc_ind2,
        v.field_content
 FROM   sierra_view.varfield v
 INNER JOIN sierra_view.bib_record br on br.id = v.record_id
 INNER JOIN sierra_view.record_metadata rm on rm.id = br.id
 WHERE  
	(v.marc_tag = '856' AND v.field_content ilike '%purl\.access\.gpo%')
	OR 
	(v.marc_tag = '856' AND v.field_content ilike '%permanent\.access\.gpo\.gov%')
        OR
        (v.marc_tag = '856' AND v.field_content ilike '%frwebgate\.access\.gpo\.gov%')

Catstats data based on an item J note

Designed to pull new cataloging based on the J stats note. Eliminates any items which have the status of b (backlogged) w(withdrawn) d (declared lost) and p in process. If an item is linked to more than one bib, it returns multiple entries for that item. This is deduped in later processing. NOTE: EACH YEAR THE CAT STAT NOTE NEEDS TO BE UPDATED IN THE QUERY 

  SELECT
  'i' || i.record_num ||'a' AS iNum, 
   i.location_code AS iLoc,
   i.itype_code_num AS Itype,
   i.item_status_code,
   (
      SELECT STRING_AGG(v.field_content, ' ; ')
      FROM sierra_view.varfield v
      WHERE v.record_id = i.id
      AND v.varfield_type_code = 'x'
    ) AS XNotes,
   (  
      SELECT STRING_AGG(v.field_content, ' ; ')
      FROM sierra_view.varfield v
      WHERE v.record_id = i.id
      AND v.varfield_type_code = 'j'
    ) AS StatsNotes,
  'b' || b.record_num || 'a' AS bNum,
   to_char(b.cataloging_date_gmt, 'YYYY-MM-DD') as "cat_date",
   (
    SELECT STRING_AGG(Trim(trailing FROM brl.location_code), ', ')
    FROM sierra_view.bib_record_item_record_link br
    INNER JOIN sierra_view.bib_record_location brl on brl.bib_record_id =  br.bib_record_id
    WHERE br.item_record_id=i.id
    AND brl.location_code != 'multi'
   ) AS BibLoc
   
FROM sierra_view.item_view i
INNER JOIN sierra_view.varfield v on v.record_id = i.id
inner join sierra_view.bib_record_item_record_link bil on bil.item_record_id = i.id
inner join sierra_view.bib_view b on b.id = bil.bib_record_id
WHERE
		(varfield_type_code='j' and v.field_content LIKE '%Cataloged FY 2020-21%')
		 AND i.item_status_code NOT IN ('b','w', 'd', 'p')

Cataloged and available East Asian titles

This verifies a cat date and that the title is not suppressed. Search pulls by language and place of publication. 

 SELECT distinct
	'b' || b.record_num ||'a' AS bNum,
   brp.material_code, 
   b. country_code as Ctry,
   b.language_code as Lang,
	to_char(b.cataloging_date_gmt, 'YYYY-MM-DD'),
   b.title as Title 
	
FROM sierra_view.item_view i
INNER JOIN sierra_view.varfield v on v.record_id = i.id
inner join sierra_view.bib_record_item_record_link bil on bil.item_record_id = i.id
inner join sierra_view.bib_view b on b.id = bil.bib_record_id
inner join sierra_view.bib_record_property brp ON brp.bib_record_id = b.id

WHERE
b.cataloging_date_gmt is not null 
AND ((b.language_code IN ('chi', 'kor', 'jap') OR 
(b.country_code IN ('cc', 'hk', 'ja', 'ko')))) -- can add in more languages as desired
AND b.bcode3 !~'n' --to weed out surpressed titles

ORDER by b.country_code, b.language_code, b.title ASC

Middle Eastern Area Studies stats for MEAS reporting

To find the materials with physical formats. (Each year must update the J stats note to reflect the current FY.) 

 SELECT
	'i' || i.record_num ||'a' AS iNum, 
   b. country_code as Ctry,
   b.language_code as Lang,
   i.itype_code_num AS Itype,
    (
      SELECT STRING_AGG(v.field_content, ' ; ')
      FROM sierra_view.varfield v
      WHERE v.record_id = i.id
      AND v.varfield_type_code = 'w'
    ) AS GiftNotes,
 (
      SELECT STRING_AGG(v.field_content, ' ; ')
      FROM sierra_view.varfield v
      WHERE v.record_id = i.id
      AND v.varfield_type_code = 'j'
    ) AS StatNotes,
  
  TO_CHAR(i.record_creation_date_gmt, 'YYYY')As AddedDate,
  b.title as Title 
	
   
FROM sierra_view.item_view i
INNER JOIN sierra_view.varfield v on v.record_id = i.id
inner join sierra_view.bib_record_item_record_link bil on bil.item_record_id = i.id
inner join sierra_view.bib_view b on b.id = bil.bib_record_id
WHERE
		b.cataloging_date_gmt is not null 
		AND (varfield_type_code='j' and v.field_content LIKE '%Cataloged FY 2020-21%')
		AND ((b.language_code IN ('ara', 'arm', 'ber', 'heb','kur','per', 'tur') OR 
(b.country_code IN ('aa','ae', 'ai', 'ba', 'cq', 'ft', 'gz', 'iq', 'ir', 'is', 'jo', 'ku', 'le', 'ly', 'mk', 'mr', 'mu', 'qa', 'sj', 'so', 'su', 'sy', 'ti', 'ts', 'tu', 'ua', 'wj', 'ye'))))


ORDER by b.country_code, b.language_code, i.itype_code_num, b.title ASC

to find the e-resources. (Each year must update the Cat Date to reflect the current FY.) 

select 
'i' || i.record_num ||'a' AS iNum, 
'b'||b.record_num || 'a' as Bnum,
b.country_code,
b.language_code, 
i.itype_code_num AS Itype,
    (
      SELECT STRING_AGG(v.field_content, ' ; ')
      FROM sierra_view.varfield v
      WHERE v.record_id = i.id
      AND v.varfield_type_code = 'w'
    ) AS GiftNotes,
to_char(b.cataloging_date_gmt, 'YYYY') As CatDate,
brp.material_code,
brp.best_title

FROM sierra_view.item_view i
INNER JOIN sierra_view.varfield v on v.record_id = i.id
inner join sierra_view.bib_record_item_record_link bil on bil.item_record_id = i.id
inner join sierra_view.bib_view b on b.id = bil.bib_record_id
inner join sierra_view.bib_record_property brp ON brp.bib_record_id = b.id
join sierra_view.bib_view bv on bv.id = b.id

where
b.cataloging_date_gmt between '2020-07-01'::date AND '2021-06-30'::date
AND brp.material_code not in ('-','a','b','c','d','e','f','o','p','t')
AND 
(b.language_code in ('ara', 'arm', 'ber', 'heb','kur','per', 'tur')
OR b.country_code in('ae', 'ai', 'ba', 'cq', 'ft', 'gz', 'iq', 'ir', 'is', 'jo', 'ku', 'le', 'ly', 'mk', 'mr', 'mu', 'qa', 'sj', 'so', 'su', 'sy', 'ti', 'ts', 'tu', 'ua', 'wj', 'ye'))

order by b.country_code, b.language_code, i.itype_code_num, b.title ASC

Graphic novels (coded 6) in Sierra

User can turn on|off a language code if they wish 

SELECT
  'b' || bv.record_num || 'a' AS bnum,
   bv.language_code,
   brl.location_code,
	 brc.call_number_prefix AS CallNoBase,
   brp.best_title,
	 brp.best_author,
	 brp.publish_year
 FROM
  sierra_view.bib_view bv
 INNER JOIN sierra_view.control_field cf ON bv. ID = cf.record_id
 INNER JOIN sierra_view.bib_record_property brp on brp.bib_record_id = bv.id
 INNER JOIN sierra_view.bib_record_call_number_prefix brc on brc.bib_record_id = bv.id
 INNER JOIN sierra_view. bib_record_location brl on brl.bib_record_id = bv.id
 
 WHERE
  --bv.language_code != 'eng'
  bv.cataloging_date_gmt is not null
  AND bv.bcode3 !='n'
  AND cf.control_num = '8'
  AND (cf.p25= '6' OR cf.p23='6' OR cf.p24='6')

Check for UnL v HSL dupes

Using phe-index_tag = "o" checks against ocn, "i" against isxns 

with id_dupe as (select phe.index_tag||phe.index_entry as identifier_entry, count(*) as rec_count
from sierra_view.phrase_entry phe
where phe.index_tag = 'o' -- phe.index_tag = 'i' for isxns
group by 1
order by 2 DESC),

hsl_dupes as (select 'b'||rm.record_num||'a' as hsl_bnum, phe.index_entry as identifier, id_dupe.identifier_entry, id_dupe.rec_count, phe.record_id as record_id
from id_dupe
inner join sierra_view.phrase_entry phe on phe.index_tag||phe.index_entry = id_dupe.identifier_entry
inner join sierra_view.record_metadata rm on rm.id = phe.record_id
inner join sierra_view.bib_record b on b.id = rm.id and b.is_suppressed = 'f'
inner join sierra_view.bib_record_location brl on brl.bib_record_id = rm.id and brl.location_code ~ '^n'
where id_dupe.rec_count > 1)

select * from hsl_dupes
where exists (
  select * from sierra_view.phrase_entry phe
  inner join sierra_view.bib_record b on b.id = phe.record_id and b.is_suppressed = 'f'
  where phe.index_tag||phe.index_entry = hsl_dupes.identifier_entry and phe.record_id != hsl_dupes.record_id)

Find GPO tmp records 

select rm.record_type_code||rm.record_num||'a', phe.index_entry, * from sierra_view.phrase_entry phe
inner join sierra_view.record_metadata rm on rm.id = phe.record_id
inner join sierra_view.bib_record b on b.id = rm.id and b.cataloging_date_gmt is null
where phe.index_tag||phe.index_entry ~ '^otmp'
and exists (
	select * from sierra_view.phrase_entry phesub
	inner join sierra_view.bib_record_location brl on brl.bib_record_id = phesub.record_id and brl.location_code ~ '^dc'
  inner join sierra_view.bib_record bsub on bsub.id = phesub.record_id and bsub.cataloging_date_gmt is not null
  inner join sierra_view.varfield v on v.record_id = bsub.id and v.marc_tag = '001' and v.field_content ~ '^[0-9]'
	where phesub.record_id != phe.record_id and phesub.index_tag||phesub.index_entry = 'o'||phe.index_entry
)

identify boundwith and analytics and count the bibs they are attached to 

SELECT
   distinct('i' || i.record_num ||'a') AS iNum, 
   i.location_code AS iLoc,
   i.itype_code_num AS Itype,
   i.icode2,
   i.item_status_code,
   (
    SELECT 
        count(distinct (b.record_num))
        FROM sierra_view.bib_record_item_record_link br
        INNER JOIN sierra_view.bib_view b on b.id = br.bib_record_id
        WHERE br.item_record_id = i.id
    ) AS num
FROM sierra_view.item_view i
INNER JOIN sierra_view.varfield v on v.record_id = i.id
inner join sierra_view.bib_record_item_record_link bil on bil.item_record_id = i.id
inner join sierra_view.bib_view b on b.id = bil.bib_record_id
WHERE
     b.cataloging_date_gmt is not null
     AND b.bcode3 NOT IN ('n','d','x')	 
     AND i.item_status_code NOT IN ('b','w', 'd', 'p')
     AND i.icode2 in ('b','l') 
order by i.location_code

Use the 944 field to count how often bibs in a particular collection have been sent to the Marcive vendor

This example looks at springer records. Update the 944 value as needed for various collections. 

Select 
    count(distinct(v915.field_content)),
    'b'||rm.record_num ||'a' as bnum
 from sierra_view.varfield v
 inner join sierra_view.record_metadata rm on rm.id = v.record_id
 inner join sierra_view.varfield v915 on v915.record_id = rm.id and v915.marc_tag = '915' and v915.field_content like '%MARCIVE%'
 where v.marc_tag = '944'
   and (v.field_content ilike '%sprold%' OR v.field_content ilike '%spnew%' or v.field_content ilike '%springer%')
group by rm.record_num;

queries to summarize, count, etc.

most common field content

This query shows all unique field content values for a given MARC field, with a count of how many records each value appears in.

It is limited to unsuppressed bib records. 

 SELECT
   v.field_content,
   COUNT(v. ID)AS ct
 FROM
   sierra_view.varfield v
 INNER JOIN sierra_view.bib_record b ON b. ID = v.record_id
 AND b.is_suppressed = 'f'
 INNER JOIN sierra_view.record_metadata rm ON rm. ID = v.record_id
 WHERE
   v.marc_tag = '538'
 GROUP BY
   v.field_content
 ORDER BY
   ct DESC
Clone this wiki locally