Hi,
I am not sure if this is how I add a question, but I cannot find anything on
lists.postgesql.org.
I am having a problem with a SQL that I am running in pgAdmin 4.27. The SQL is
being used to check for duplicates in our PostgreSQL DB.
I am trying to edit out the barcode (e.index_entry) when it is equal to
2111300. When I run it with the above, I get and error:
ERROR: operator does not exist: character varying <> integer
LINE 66: and e.index_entry != 2111300 ^
HINT: No operator matches the given name and argument type(s). You might need
to add explicit type casts.
I have tried <> and adding single quotes around 2111300. The <> gets the same
error, and the single quotes '' has it run for a long time.
Any help would be appreciated.
Here is the SQL we are using with the lines I use highlighted:
SELECT
r.creation_date_gmt as created,
e.index_entry as barcode,
'p' || r.record_num || 'a' as patron_record_num,
pn.last_name || ', ' ||pn.first_name || COALESCE(' ' ||
NULLIF(pn.middle_name, ''), '') || ' ' || pr.birth_date_gmt as patron,
pr.ptype_code,
pr.activity_gmt,
pr.expiration_date_gmt,
pr.mblock_code as block_code,
pr.owed_amt::float8::numeric::money as owed_amt,
pr.home_library_code
-- pr.home_library_code,
FROM
sierra_view.patron_record_fullname as pn
JOIN
sierra_view.patron_record as pr
ON
pr.record_id = pn.patron_record_id
JOIN
sierra_view.record_metadata as r
ON
r.id = pr.record_id
JOIN
sierra_view.phrase_entry AS e
ON
(e.record_id = r.id) AND (e.index_tag = 'b') AND
(e.varfield_type_code = 'b')
WHERE
pr.birth_date_gmt || pn.first_name || COALESCE(' ' ||
NULLIF(pn.middle_name, ''), '') || ' ' || pn.last_name
IN
(
SELECT
p.birth_date_gmt ||
n.first_name || COALESCE(' ' || NULLIF(n.middle_name,
''), '') || ' ' || n.last_name as patron_name
-- e.index_entry,
-- count(*) as matches
FROM
sierra_view.record_metadata AS r
JOIN
sierra_view.patron_record AS p
ON
p.record_id = r.id
JOIN
sierra_view.patron_record_fullname AS n
ON
n.patron_record_id = r.id
-- JOIN
-- sierra_view.phrase_entry AS e
-- ON
-- (e.record_id = r.id) AND (e.index_tag = 'b') AND
(e.varfield_type_code = 'b')
WHERE
r.record_type_code = 'p'
-- and r.creation_date_gmt >= '2017-05-01'
and p.mblock_code != 'd'
and e.index_entry != 2111300
GROUP BY
p.birth_date_gmt,
patron_name,
p.ptype_code
-- e.index_entry
HAVING
COUNT(*) > 1
)
-- and pr.mblock_code != 'd'
ORDER BY
pn.last_name || pn.first_name || pr.birth_date_gmt || COALESCE(' ' ||
NULLIF(pn.middle_name, ''), ''),
pr.ptype_code ASC,
pr.activity_gmt DESC
Joseph A. Ruffino
Gail Borden Public Library District
Web Programmer
270 N. Grove Ave.
Elgin, IL 60120
Phone: (847) 429-5986 Fax: (847) 608-5201
http://www.gailborden.info
PLEASE NOTE: Pursuant to Illinois State Law, e-mail communication to and from
this address may be subject to public disclosure.
