Results 1 to 7 of 7
  1. #1
    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..............

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,452
    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.

  3. #3
    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...

  4. #4
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    sorry double post :: please remove one
    Last edited by DBFinder; 05-16-11 at 20:21.

  5. #5
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,452
    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
    ;

  7. #7
    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..........

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •