Sqlcode: -420, sqlstate: 22018, sqlerrmc: Bigint

kristo5747 · #1 (**permalink**) 05-16-11, 18:06

[Running DB2 UDB version 9] Why does this SQL statement work

Code:

SELECT 
       CASE
       WHEN A.DAILYDOWNLOADSIZE is null THEN 0
    else bigint(A.DAILYDOWNLOADSIZE)
       END "DAILYDOWNLOADSIZE",
       CASE
       WHEN A.DAILYDOWNLOADTIME is null THEN 0
    else bigint(A.DAILYDOWNLOADTIME)
       END "DAILYDOWNLOADTIME"
FROM 
EDMPROD.MQT_STB_FACTS A

...when this one does not

Code:

SELECT DISTINCT 
bigint(A.CAMID) AS "CAMID",
bigint(A.RID) AS "RID",
A.SOFTWAREVERSION,
A.MODELNUMBER,
A.MANUFACTURERID,
A.MODDATE,
A.POSTTIME,
A.DELIVERYMETHOD,
bigint(A.UPTIME )  AS "UPTIME",
bigint(A.NUMBEROFRESETSSINCELASTSWDL )  AS "NUMBEROFRESETSSINCELASTSWDL",
bigint(A.NUMBEROFSEARCHSIGNALOSDTUNER1 ) AS "NUMBEROFSEARCHSIGNALOSDTUNER1",
bigint(A.NUMBEROFSEARCHSIGNALOSDTUNER2 ) AS "NUMBEROFSEARCHSIGNALOSDTUNER2",
bigint(A.NUMBEROFUSERDISKREFORMATS ) AS "NUMBEROFUSERDISKREFORMATS",
bigint(A.NUMBEROFSYSTEMDISKREFORMATS ) AS "NUMBEROFSYSTEMDISKREFORMATS",
bigint(A.NUMBEROFRECOVEREDDISKERRORS ) AS "NUMBEROFRECOVEREDDISKERRORS",
IPINFO,
       CASE
       WHEN A.DAILYDOWNLOADSIZE is null THEN 0
    else bigint(A.DAILYDOWNLOADSIZE)
       END "DAILYDOWNLOADSIZE",
       CASE
       WHEN A.DAILYDOWNLOADTIME is null THEN 0
    else bigint(A.DAILYDOWNLOADTIME)
       END "DAILYDOWNLOADTIME",
bigint(A.TOTALNUMBEROFDOWNLOADSSTARTED ) AS "TOTALNUMBEROFDOWNLOADSSTARTED",
bigint(A.TOTALNUMOFCOMPLETEDDLS ) AS "TOTALNUMOFCOMPLETEDDLS",
CURRENT_DATE AS "LOAD_DATE"
FROM 
EDMPROD.MQT_STB_FACTS A

I get an error

Code:

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

.

By troubleshooting the query, I confirm the culprits are the two columns DAILYDOWNLOADSIZE and DAILYDOWNLOADTIME. Both must be casted from character(15) to bigint.

What is the second SQL statement not working??? I think I am going bonkers..............

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

Why did you confirmed the culprits are the two columns DAILYDOWNLOADSIZE and DAILYDOWNLOADTIME?

There are many other columns argumented to BIGINT, and BIGINT for the two columns worked fine.

kristo5747 · #3 (**permalink**) 05-16-11, 18:40

Quote:

Originally Posted by tonkuma

Why did you confirmed the culprits are the two columns DAILYDOWNLOADSIZE and DAILYDOWNLOADTIME?

There are many other columns argumented to BIGINT, and BIGINT for the two columns worked fine.

If I removed DAILYDOWNLOADSIZE and DAILYDOWNLOADTIME columns for the second SQL query, the error disappeared. I added both columns back, the error re-appeared.

This is why I believe these two are the culprits.

Weird part is that until this past Sunday, the second query *never* failed. Something has changed in the data source but I do not know what...

DBFinder · #4 (**permalink**) 05-16-11, 20:14

sorry double post :: please remove one

DBFinder · #5 (**permalink**) 05-16-11, 20:16

Any non numeric character might be creeping in.

For example 'comma' as thousands seperator will not be accepted. More likely to creep in when you use non database standard output captured from console output.
You need to check contents of these fields some how. There are various ways to deploy error trapping. (eg. try . . catch)

Please note that only decimal point is allowed. Not even a space in between the digits is allowed.

This is the only reason you can get this error.

Regards
DBFinder

Code:

C:\Program Files\IBM\SQLLIB\BIN>db2 insert into test values '123,456,789,01'
DB20000I  The SQL command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2 select bigint(id) from test

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

tonkuma · #6 (**permalink**) 05-17-11, 01:48

By using the following sample UDF, you can find non-numeric values, like ...

Code:

SELECT CAMID
     , RID
     , DAILYDOWNLOADSIZE
     , ISNUMERIC(DAILYDOWNLOADSIZE) AS isnum_DAILYDOWNLOADSIZE
     , DAILYDOWNLOADTIME
     , ISNUMERIC(DAILYDOWNLOADTIME) AS isnum_DAILYDOWNLOADTIME
 FROM  EDMPROD.MQT_STB_FACTS
 WHERE ISNUMERIC(DAILYDOWNLOADSIZE) = 0
   OR  ISNUMERIC(DAILYDOWNLOADTIME) = 0
;

Sample UDF:

Code:

--
-- Description: Returns 1 when the input string is a valid integer or decimal representation,
--              otherwise returns O.
--
--              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.
--
-- Author: TOKUNAGA, Takashi
--

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

kristo5747 · #7 (**permalink**) 05-17-11, 14:35

I was losing my mind over this so I made a simple change in my staging table (EDMPROD.MQT_STB_FACTS).

Instead of typing the columns as characters and then casting them as bigint, I dropped and recreated my table with the datatypes set to BIGINT(8).

I removed the castings from my SQL statement and the error went away.

Go figure..........

Internet Services

Web Development

Digital Marketing

Consumer Tech