DB2 Conversion Error

dmaheshrao · #1 (**permalink**) 05-31-11, 10:26

I am getting below error when I am type casting String to Int in where clause.

Invalid Character found in a character string argument of the function "INT"
SQLSTATE = 22018

WHERE INT(R_FR_CD) <= 123456 AND INT(R_TO_CD) >= 12389

tonkuma · #2 (**permalink**) 05-31-11, 11:18

The error message fully explains the reason.

For example:

Code:

------------------------------ Commands Entered ------------------------------
VALUES INT('');
------------------------------------------------------------------------------

1          
-----------
SQL0420N  Invalid character found in a character string argument of the 
function "INTEGER".  SQLSTATE=22018

What are the values of R_FR_CD and R_TO_CD?

dmaheshrao · #3 (**permalink**) 05-31-11, 11:32

The values are 123456 and 200007.
I didn't get why VALUES is being used before INT(' ')

tonkuma · #4 (**permalink**) 05-31-11, 11:42

Quote:

The values are 123456 and 200007.

Really?

This worked without any error.

Code:

------------------------------ Commands Entered ------------------------------
WITH
 test_data(R_FR_CD , R_TO_CD) AS (
SELECT '123456' , '200007' FROM sysibm.sysdummy1
)
SELECT *
 FROM  test_data
 WHERE INT(R_FR_CD) <= 123456 AND INT(R_TO_CD) >= 12389
;
------------------------------------------------------------------------------

R_FR_CD R_TO_CD
------- -------
123456  200007 

  1 record(s) selected.

Quote:

I didn't get why VALUES is being used before INT(' ')

VALUES INT('') is equivalent to SELECT INT('') FROM sysibm.sysdummy1

dmaheshrao · #5 (**permalink**) 05-31-11, 11:49

The query which you posted worked even for me, but same applies to my query which fails

May be some issue will get back to you soon...

Thanks,
Mahesh

tonkuma · #6 (**permalink**) 05-31-11, 12:02

Quote:

..., but same applies to my query which fails

So, the values of R_FR_CD and R_TO_CD are not '123456' and '200007'.

Stealth_DBA · #7 (**permalink**) 05-31-11, 12:46

dmaheshrao, You may have some stray non-printable characters in you data.

Code:

SELECT INT('123') FROM SYSIBM.SYSDUMMY1

-----------
        123

SELECT INT('123' || x'00') FROM SYSIBM.SYSDUMMY1

-----------
SQL0420N  Invalid character found in a character string argument of the function "INTEGER".  SQLSTATE=22018

Try using the HEX function and see what shows up:

Code:

SELECT HEX('123' || x'00') FROM SYSIBM.SYSDUMMY1

31323300

Internet Services

Web Development

Digital Marketing

Consumer Tech