You could try reddit.com/r/sql
RobR
On Wednesday, January 6, 2021, 01:55:23 PM EST, Joseph Ruffino
<jruff...@gailborden.info> wrote:
#yiv6276017970 #yiv6276017970 -- _filtered {} _filtered {}#yiv6276017970
#yiv6276017970 p.yiv6276017970MsoNormal, #yiv6276017970
li.yiv6276017970MsoNormal, #yiv6276017970 div.yiv6276017970MsoNormal
{margin:0in;margin-bottom:.0001pt;font-size:11.0pt;font-family:sans-serif;}#yiv6276017970
a:link, #yiv6276017970 span.yiv6276017970MsoHyperlink
{color:blue;text-decoration:underline;}#yiv6276017970
span.yiv6276017970EmailStyle18 {font-family:New
serif;color:windowtext;font-weight:normal;font-style:normal;}#yiv6276017970
.yiv6276017970MsoChpDefault {font-family:sans-serif;} _filtered
{}#yiv6276017970 div.yiv6276017970WordSection1 {}#yiv6276017970
Rik,
Is there a user list I can send this too?
It is cast, “as barcode” in line 2, but it will not accept it when I do barcode
!= 2111300. I have tried adding quotes around ‘2111300’, and it has been
running for 30+ minutes. Do you know if another way to cast it?
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
From: richard coleman <rcoleman.ascen...@gmail.com>
Sent: Wednesday, January 6, 2021 12:42 PM
To: Joseph Ruffino <jruff...@gailborden.info>
Cc: pgadmin-support@lists.postgresql.org
Subject: Re: Varying Character comparison
Joseph,
This is really an SQL question, not something specific to PostgreSQL or
pgAdmin. That being said, it looks like your e.index_entry field is of type
varchar (character varying) and you are trying to compare it to an int. If
that is the case you will need to either cast the e.index_entry field in your
queries to INT (which will fail if any aren't castable to that type, or wrap
your 2111300 in quotes (ex:'2111300' ) to make it a string as opposed to an int.
I hope that helps,
rik.
On Wed, Jan 6, 2021 at 1:25 PM Joseph Ruffino <jruff...@gailborden.info> wrote:
Hi,
I am not sure if this is how I add a question, but I cannot find anything
onlists.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 to2111300.
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.
PLEASE NOTE: Pursuant to Illinois State Law, e-mail communication to and from
this address may be subject to public disclosure.
