Useful Code snippets

Code examples useful for query construction

Aggregate multi fields

string_agg(distinct(bib_record_location.location_code), ',')        AS "location code"

Author in firstname, lastname order

REPLACE(SPLIT_PART(SPLIT_PART(b.best_author,' (',1),', ',2),'.','')||' '||SPLIT_PART(b.best_author,', ',1)

Calculate check digit

SELECT
md.record_type_code||md.record_num||
COALESCE(
    CAST(
        NULLIF(
        (
            ( md.record_num % 10 ) * 2 +
            ( md.record_num / 10 % 10 ) * 3 +
            ( md.record_num / 100 % 10 ) * 4 +
            ( md.record_num / 1000 % 10 ) * 5 +
            ( md.record_num / 10000 % 10 ) * 6 +
            ( md.record_num / 100000 % 10 ) * 7 +
            ( md.record_num / 1000000 ) * 8
         ) % 11,
         10
         )
  AS CHAR(1)
  ),
  'x'
 ) AS "Bib Number"

Call number range table

 CASE
 WHEN varfield.field_content ~ '\|a0[0-9][0-9]' THEN '000'
 WHEN varfield.field_content ~ '\|a1[0-9][0-9]' THEN '100'
 WHEN varfield.field_content ~ '\|a2[0-9][0-9]' THEN '200'
 WHEN varfield.field_content ~ '\|a3[0-9][0-9]' THEN '300'
 WHEN varfield.field_content ~ '\|a4[0-9][0-9]' THEN '400'
 WHEN varfield.field_content ~ '\|a5[0-9][0-9]' THEN '500'
 WHEN varfield.field_content ~ '\|a6[0-9][0-9]' THEN '600'
 WHEN varfield.field_content ~ '\|a7[0-9][0-9]' THEN '700'
 WHEN varfield.field_content ~ '\|a8[0-9][0-9]' THEN '800'
 WHEN varfield.field_content ~ '\|a9[0-9][0-9]' THEN '900'
 ELSE 'unknown'
 END AS "Call#_Range",

Concatenate multiple fields

patron_record_fullname.first_name || ' ' || patron_record_fullname.middle_name || ' ' || patron_record_fullname.last_name AS Name

Convert id to record number

id2reckey(patron_view.id)||'a' AS pnumber

Convert integer to numeric for accounting arithmetic

round(cast (fund.appropriation-fund.expenditure as numeric (12,2))/100, 2) AS "CASH BALANCE"

Convert Mattype Code to Label

CASE
WHEN brp.material_code = '2' THEN 'Large Print'
WHEN brp.material_code = '3' THEN 'Periodical'
WHEN brp.material_code = '4' THEN 'Spoken CD'
WHEN brp.material_code = '5' THEN 'DVD'
WHEN brp.material_code = '6' THEN 'Film/Strip'
WHEN brp.material_code = '7' THEN 'Music Cassette'
WHEN brp.material_code = '8' THEN 'LP'
WHEN brp.material_code = '9' THEN 'Juv Book + CD'
WHEN brp.material_code = 'a' THEN 'Book'
WHEN brp.material_code = 'b' THEN 'Archival Material'
WHEN brp.material_code = 'c' THEN 'Music Score'
WHEN brp.material_code = 'e' THEN 'Map'
WHEN brp.material_code = 'g' THEN 'VHS'
WHEN brp.material_code = 'h' THEN 'Downloadable eBook'
WHEN brp.material_code = 'i' THEN 'Spoken Cassette'
WHEN brp.material_code = 'j' THEN 'Music CD'
WHEN brp.material_code = 'k' THEN '2D Visual Material'
WHEN brp.material_code = 'l' THEN 'Downloadable Video'
WHEN brp.material_code = 'm' THEN 'Software'
WHEN brp.material_code = 'n' THEN 'Console Game'
WHEN brp.material_code = 'o' THEN 'Kit'
WHEN brp.material_code = 'p' THEN 'Mixed Material'
WHEN brp.material_code = 'q' THEN 'Equipment'
WHEN brp.material_code = 'r' THEN '3D Object'
WHEN brp.material_code = 's' THEN 'Downloadable Audiobook'
WHEN brp.material_code = 't' THEN 'Manuscript'
WHEN brp.material_code = 'u' THEN 'Blu-ray'
WHEN brp.material_code = 'v' THEN 'eReader/Tablet'
WHEN brp.material_code = 'w' THEN 'Downloadable Music'
WHEN brp.material_code = 'x' THEN 'Playaway Video'
WHEN brp.material_code = 'y' THEN 'Online'
WHEN brp.material_code = 'z' THEN 'Playaway Audio'
ELSE 'unexpected code '||brp.material_code
END     AS "MatType"

Converting timestamps

date(order_date_gmt)
date_part('year', order_date_gmt)

Divide by Zero error fix

(COUNT(i.id)/(NULLIF(SUM(i.checkout_total),0)))

Encore URL

'http://find.minlib.net/iii/encore/record/C__Rb'||bv.record_num   AS "URL"

Filter calculations

AVG(i.price) FILTER(WHERE i.price>'0')

Join attached record types together

FROM
    sierra_view.bib_view                              AS bv
JOIN
    sierra_view.bib_record_item_record_link           AS bilink
    ON
    bv.id = bilink.bib_record_id
    JOIN
    sierra_view.item_view                             AS iv
    ON
    bilink.item_record_id = iv.id 

Location codes belong to a location

location_code LIKE 'nee%'

MD5 Hash Patron IDs

md5(CAST(c.patron_record_id AS varchar))

Mode

MODE() WITHIN GROUP (order by i.price)

Pull out Juv/YA Age Level from Location code

CASE
WHEN SUBSTRING(item_view.location_code,4,1)='j' THEN 'Juv'
WHEN SUBSTRING(item_view.location_code,4,1)='y' THEN 'YA'
Else 'Adult'
END AS "Age level"

Relative date limit

WHERE
item_view.record_creation_date_gmt < (localtimestamp - interval '60 days')

Relative total

round(cast(count (*) as numeric (12,2)) / ((select cast(count (*)as numeric (12,2)) from sierra_view.item_view where location_code LIKE 'nee%')), 6) as relative_item_total

Reverse to extract last characters from a string

REVERSE(SUBSTRING(REVERSE(v.field_content) FOR 5))

Review file query

FROM
sierra_view.bib_view as b 
JOIN sierra_view.bool_set as sb ON b.id = sb.record_metadata_id AND sb.bool_info_id = '[review file #]'

split_part to parse strings

SPLIT_PART(v.field_content, ' ', 9)
-- Takes 9th segment of field_content delimited by a space

Temp Table to compare two result sets

DROP TABLE IF EXISTS mvhdholds;
CREATE TEMP TABLE mvhdholds AS
SELECT *
FROM Sierra_view.item_view;

Turnover rate

round(cast(SUM(item_view.year_to_date_checkout_total) as numeric (12,2))/cast(count (bib_record_item_record_link.id) as numeric (12,2)), 2) as "turnover"

Unavailable items

HAVING
  SUM(
    CASE
      WHEN s2_i.item_status_code = '$' THEN 0 -- Lost and Paid
      WHEN s2_i.item_status_code = 'd' THEN 0 -- Damaged
      WHEN s2_i.item_status_code = 'e' THEN 0 -- e-resource
      WHEN s2_i.item_status_code = 'm' THEN 0 -- Missing
      WHEN s2_i.item_status_code = 'n' THEN 0 -- Billed
      WHEN s2_i.item_status_code = 'o' THEN 0 -- Library Use Only
      WHEN s2_i.item_status_code = 'w' THEN 0 -- Withdrawn
      WHEN s2_i.item_status_code = 'z' THEN 0 -- Claims Returned
      ELSE 1
    END
  ) = 0 -- The number of available items is 0

Variable fields

FROM sierra_view.item_view as item
LEFT OUTER JOIN sierra_view.varfield AS call_num
  ON item.id = call_num.record_id AND call_num.varfield_type_code = 'c'
LEFT OUTER JOIN sierra_view.varfield AS vol
  ON item.id = vol.record_id AND vol.varfield_type_code = 'v'