If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Sqlcode: -420, sqlstate: 22018, sqlerrmc: Bigint

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jul 2003
Location: Los Angeles
Posts: 18
Angry Sqlcode: -420, sqlstate: 22018, sqlerrmc: Bigint

[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..............
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Feb 2008
Location: Japan
Posts: 3,224
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.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jul 2003
Location: Los Angeles
Posts: 18
Quote:
Originally Posted by tonkuma View Post
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...
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Sep 2008
Location: Toronto,Canada
Posts: 648
sorry double post :: please remove one

Last edited by DBFinder; 05-16-11 at 20:21.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Sep 2008
Location: Toronto,Canada
Posts: 648
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
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Feb 2008
Location: Japan
Posts: 3,224
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
;
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Jul 2003
Location: Los Angeles
Posts: 18
Unhappy

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..........
Reply With Quote
Reply

Tags
db2 9.1

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On