CODEWITHSUNDEEP
sqlpresto
adelle
adelle

Reputation: 199

How to cast varchar into int - SQL Error: '=' cannot be applied to varchar, bigint

I have a list of product collection ids in the form of varchar datatype.

Referring to a sample structure below.. I would like to retrieve the item names of collection id (pro_col_id) 3453, 3454, 3455.

enter image description here

My query is as follows:

select pro_col_id, name
from collections_table 
where pro_col_id in (3453, 3454, 3455);

When I ran this query, I got an error

SQL Error: '=' cannot be applied to varchar, bigint

So I tried to cast pro_col_id as int like this:

select pro_col_id, name
from collections_table 
where cast(pro_col_id as int) in (3453, 3454, 3455);

but I still get the same error. Does anyone know what is the issue causing this?

Thank you!

Upvotes: 1

Views: 64118

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If the values are strings, then the comparisons should be strings:

select pro_col_id, name
from collections_table 
where pro_col_id in ('3453', '3454', '3455');

If this does not fix your problem, then the issue is not the where clause. One possibility is that collections_table is not really a table but is a view, and something is amiss in the view.

Upvotes: 3

Related Questions