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

Question

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.

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!

Answer 1

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.