SQL Query to find Duplicate MARC 650 Fields

Sierra, Millennium & Encore
,
1 
/*
 * Find orders/bibs/items/patrons with duplicate variable-length fields.
 * This specific iteration of this script identifies bibs with duplicate 650 fields.
 *
 * Bob Gaydos <bgaydos@starklibrary.org>
 * Aug 11, 2024
 */
SET search_path = 'sierra_view';
-- This CTE can be adapted to many different record types (see commented-out record types)
WITH DuplicateFields AS
(
    SELECT
        v.record_id,
        v.varfield_type_code,
        v.marc_tag,
        v.marc_ind1,
        v.marc_ind2,
        TRIM(v.field_content) AS field_content,
        COUNT(v.record_id) AS duplicates
    FROM varfield v
  --INNER JOIN order_record r
    INNER JOIN bib_record r   -- unique to this dup 650s query
  --INNER JOIN item_record r
  --INNER JOIN patron_record r
       ON r.id = v.record_id
    WHERE v.marc_tag = '650'  -- unique to this dup 650s query
    GROUP BY
        v.record_id,
        v.varfield_type_code,
        v.marc_tag,
        v.marc_ind1,
        v.marc_ind2,
        TRIM(v.field_content)
    HAVING
        COUNT(v.record_id) > 1
)
SELECT
    rm.record_type_code || rm.record_num::VARCHAR(8) || 'a' AS "Record Num",
    df.duplicates AS "Duplicates",
    df.marc_tag AS "MARC Tag",
    df.marc_ind1 AS "Ind1",
    df.marc_ind2 AS "Ind2",
    df.field_content AS "Field Content"
FROM DuplicateFields df
INNER JOIN record_metadata rm
   ON rm.id = df.record_id
ORDER BY
    "Duplicates" DESC,
    "Record Num" DESC
;
1 Like