SQLCODE: -420, SQLSTATE: 22018, SQLERRMC: BIGINT

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

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

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 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???

On 16.05.11 19:52 , kg6ypx wrote:
> 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???

The only reason why the second query would not work is, if someone inserted a
row in the table (after the first query) which contained incompatible characters.

This might be a dumb question, but why are you casting almost all rows to
bigint? Wouldn't it be better to create the columns as bigint?

Anyway, do a

SELECT DAILYDOWNLOADSIZE, DAILYDOWNLOADTIME FROM EDMPROD.MQT_STB_FACTS

and check, if there are any characters in the output which are not a number or
a decimal character.

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab