Intermediate type cast error : Char to int

Choudhary.raj · #1 (**permalink**) 05-18-09, 01:28

In normal sql query , while converting char to int for comparison some times db shows type caste error , the same query is running fine on other database.

But if we do the other way round, if we type cast integer to char for comparison its working fine at all the places.

Is there some parameter in the DB that might be leading to this. Any pointer to the solution would be really helpful

Thanks
Raj

rahul_s80 · #2 (**permalink**) 05-18-09, 02:08

no....
because all no. (5)>('5') are char , but not all char ('A' )<> (A) are integer

Choudhary.raj · #3 (**permalink**) 05-29-09, 05:56

Thanks for the reply rahul but iIn this we are sure that the selected records will be integer type only, as a matter of fact the query is running on the other db env that we have.

It gives generic type cast exception.

rahul_s80 · #4 (**permalink**) 05-29-09, 06:21

you must be having ' ' or ',' or '.' or any other char somewhere in the data

Choudhary.raj · #5 (**permalink**) 06-02-09, 02:21

Hi,

No this is also not the scenario, the data set on which we are operating doesn't have any of these. The basic question is why this is working over one db everything same and why not on other.

The data set remains the same in both the db. Is there any env specific variable that might impact this.

Peter.Vanroose · #6 (**permalink**) 06-02-09, 03:00

Can you give an example of a record where the type cast exception happens?
It also helps if we know which platform & version of DB2 you are using.

stolze · #7 (**permalink**) 06-02-09, 05:00

Quote:

Originally Posted by Choudhary.raj

Hi,

No this is also not the scenario, the data set on which we are operating doesn't have any of these. The basic question is why this is working over one db everything same and why not on other.

The data set remains the same in both the db. Is there any env specific variable that might impact this.

You have some differences in the data for sure. Try to find that by narrowing down the error on a few rows only.

tonkuma · #8 (**permalink**) 06-02-09, 07:12

You can use a UDF to find the data causing error.
Although the UDF might not complete, it will be usefull to narrow down the error.

Sample UDF:

Code:

--
-- Description: Returns 0 when the input string is a valid integer or decimal representation,
--              otherwise returns > 0.
--
--              Validity test cases.
--                Test 1  : All characters are '0123456789.-+' or blank.
--                Test 2  : No other characters exists left of '-' or '+' except blank(s).
--                Test 3-1: Number of '-' or '+' should be 0 or 1.
--                Test 3-2: Number of '.' should be 0 or 1.
--                Test 4  : No blanks are allowed between characters except after leading '-' or '+'.
--                Test 5  : It should have at least one digit character.
--

CREATE FUNCTION ISNUMERIC (Source VARCHAR(40))
 RETURNS INTEGER
 LANGUAGE SQL
 DETERMINISTIC
 NO EXTERNAL ACTION
RETURN
CASE 
WHEN translate(source,'','0123456789.-+') <> ''                    THEN 10
WHEN posstr(ltrim(source),'-') > 1
  OR posstr(ltrim(source),'+') > 1                                 THEN 20
WHEN length(rtrim(ltrim(translate(source,'','0123456789.')))) > 1  THEN 31
WHEN length(rtrim(ltrim(translate(source,'','0123456789-+')))) > 1 THEN 32
WHEN posstr(ltrim(rtrim(translate(source,'','-+'))),' ') > 0       THEN 40
WHEN translate(source,'','.-+') = ''                               THEN 50
ELSE 0
END

Example of use of the UDF:

Code:

SELECT c1
     , ISNUMERIC(c1)
  FROM table_name
 WHERE ISNUMERIC(c1) > 0
 FETCH FIRST 10 ROWS ONLY
;

Internet Services

Web Development

Digital Marketing

Consumer Tech