URGENT. Can't get a cast to work...

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

apattin wrote:
> 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
>
--#SET TERMINATOR @
CREATE PROCEDURE softintp(IN arg VARCHAR(255), OUT res INTEGER)
CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '22018'
BEGIN SET res = NULL; END;
SET res = INTEGER(arg);
END
@

CREATE FUNCTION softint(arg VARCHAR(255))
RETURNS INTEGER CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE res INTEGER;
CALL softintp(arg, res);
RETURN res;
END
@
--#SET TERMINATOR ;

db2 => VALUES softint('17');

1
-----------
17

1 record(s) selected.

db2 => VALUES softint('hello');

1
-----------
-

1 record(s) selected.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

I think following can handle almost every character string.
In other words, if VALUES INT(arg) returns value without error,
following also returns same value. And if VALUES INT(arg) returns
error, following will returns NULL.

I'm using DB2 for LUW V8.
"RTRIM(LTRIM(" may be able to be replaced by STRIP on DB2 9.

CREATE FUNCTION softint(arg VARCHAR(255))
RETURNS INTEGER
CONTAINS SQL
DETERMINISTIC
NO EXTERNAL ACTION
RETURN
CASE
WHEN SUBSTR(LTRIM(arg),1,1) NOT IN ('-','+')
AND TRANSLATE(RTRIM(LTRIM(arg)),'*',' 0123456789') = ''
OR SUBSTR(LTRIM(arg),1,1) IN ('-','+')
AND TRANSLATE(RTRIM(LTRIM(SUBSTR(LTRIM(arg),2))),'*','
0123456789') = '' THEN
INT(arg)
END;