(Recommendations from Jean Pajerek and Joanne Leary)

The first subject heading in the record is generally meant to correlate with the dominant subject matter of the book or whatever, but this is not always possible because there are some subject headings that have to be assigned in pairs, like United States--Foreign relations--China and China--Foreign relations--United States. 

It is possible that a given record will not have any LC subject headings. In the screenshot below we see a typical example of subject headings in a record. The first 2 lines in the screenshot show two parts of a single subject heading: 650  0  $a Cultural property $x Protection (International law). The number 1 in the ord column tells us that these two subfields are the elements that make up the first subject heading in the record. The second subject heading has 3 separate elements, divided into three subfields: 650  0 ‡a Cultural property ‡x Protection ‡z Europe and so on. There are 4 LC subject headings in this record, and numerous other subject terms as well.  To find the first subject term in a record, I recommend using that number 1 in the ord field.



For example, to pull the first LOC subject heading from a bibliographic record using its instance hrid, you would use:

SELECT DISTINCT
    SM.instance_hrid,
    SM.field,
    SM.ind2,
    SM.ord,
    SM.sf,
    SM."content"
FROM
    public.srs_marctab SM
 WHERE SM.instance_hrid = '15309598'
 AND SM.field LIKE '6%%'
 AND SM.ord='1'
 AND SM.ind2='0'

;


Which results in:


Further, if you want to display the subject heading as it appears in the bibliographic record, you may use a STRING_AGG FUNCTION to format the content rows as a subject heading string. Here is an example using instance HRIDs between 6403921 and 6404000:

WITH lc as

(SELECT DISTINCT
        sm.instance_hrid,
        sm.field,
        sm.content

FROM srs_marctab sm

WHERE sm.instance_hrid between '6403921' and '6404000'
        AND sm.field like '6%%'
        AND sm.sf in ('a','x','y','z')
        AND sm.ind2 = '0'
        AND sm.ord = '1'
)

SELECT
        lc.instance_hrid,
        lc.field,
        string_agg (lc.content, ' -- ') as lc_subject

FROM lc

GROUP BY
        lc.instance_hrid,
        lc.field
;


And the results would be:





  • No labels