You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 13 Next »


Why would you need to include number of pages in a report?

  • physical collections inventory 
  • planning collection shifts, especially when books are not located together and direct measurement (linear feet) is not feasible
  • estimating the number of trucks needed to move materials to the Annex or another location
  • doing tracers; height of volume and number of pages give an approximation of the size of the item being searched for
  • counting the number of exposures needed to scan or copy a book


Where are page number data fields stored?

  • in the MARC record, field 300, subfield a (the 300 field and the a subfield are both repeatable in a record). See Marc21 format for bibliographic data for details.
  • page numbers are also extracted from the folio_inventory.instance table jsonb array and written to the instance_physical_descriptions derived table. See derivation code for this table.
  • NOTE: Some electronic resources have a 300 field subfield a showing page numbers, if the original form of the work was print material

This example below shows 236 pages in the item, which is an electronic resources. If your query is only pulling items from a physical location, you should not get any electronic resources. However, it is good to be aware that the physical description may include

It is impossible to account for every variation of page number entry in your query



Basic code to extract physical description from the instance table

This method does not use the problematic marc__t table. This code extracts the "physical descriptions" array from the instance record; records that do not have a physical descriptions array will not drop out, because a left join lateral function is used. 


WITH parameters AS 
    (SELECT 
    'law,ref' AS location_code_filter -- enter a location code between the quote marks; if left blank, the query will get all locations except for 'serv,remo'
    )

SELECT distinct
    instance.id AS instance_id,
    instance.jsonb#>>'{hrid}' AS instance_hrid,
    instance.jsonb#>>'{title}' AS title,
    location__t.name AS location_name,
    holdings_record__t.call_number,
    phys.jsonb#>>'{}' AS field_300,
    CASE
        WHEN substring (phys.jsonb#>>'{}','\d{1,4}') IS NOT NULL
        AND (phys.jsonb#>>'{}' LIKE '%page%' OR phys.jsonb#>>'{}' LIKE '%p.%' OR phys.jsonb#>>'{}' LIKE '%leaves%' OR phys.jsonb#>>'{}' LIKE '% l.%')
        THEN substring (phys.jsonb#>>'{}','\d{1,4}')::INT
        ELSE NULL
    END AS page_count_estimate,
    phys.ordinality AS record_sequence_ordinality

FROM folio_inventory.instance
    LEFT JOIN LATERAL jsonb_array_elements (jsonb_extract_path (instance.jsonb,'physicalDescriptions')) WITH ordinality AS phys (jsonb)
    ON true
    
    LEFT JOIN folio_inventory.holdings_record__t 
    ON instance.id = holdings_record__t.instance_id 
    
    LEFT JOIN folio_inventory.location__t 
    ON holdings_record__t.permanent_location_id = location__t.id 

WHERE 
    CASE WHEN (SELECT location_code_filter FROM parameters) =''
    THEN location__t.code != 'serv,remo' 
    ELSE location__t.code = (SELECT location_code_filter FROM parameters) 
    END 
;

Result:


Physical Descriptions array example from the instance table:













  • No labels