Hi all,
I have this table:
create table my_table (file_id int not null, property_id int not null,
property_value varchar(255));
In some instances the property_value is a string representing an
integer. I would like to do this:
select file_id from my_table where property_id = 101 and
int(property_value) 60 and
file_id in (select file_id from some_other_table);
Now:
* some_other_table contains 200 file_ids
* my_table contains thousands of rows
If I run this query:
select file_id from my_table where property_id = 101 and file_id in
(select file_id from some_other_table);
I get 50 rows, meaning there are 50 potential matches to test against
int(property_value) 60.
The query fails with SQL0420N Invalid character found in a character
string...etc.
I checked the property_value contents for the selected subset and they
are all proper integer representations.
I've assumed that the query is executed in such a way that DB2 first
checks for
"int(property_value) 60" before it checks for any other condition
and it therefore encounters nulls that it can't cast.
(BTW, I'm aware that the "file_id in (select file_id from
some_other_table)" condition can be expressed as a join, but it won't
solve the problem).
Am I right? HOW DO I FIX THIS?? I'm guessing it's a job for COALESCE,
but I can't get the syntax right either.
Thanks all,
Alejandrina