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

[kg6ypx]

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

[Helmut Tessarek]

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

[Lennart Jonsson]

This idea was posted some years ago by Troels Arvin (can't find the
original post now)

CREATE PROCEDURE castalesce_bigint_(IN strval VARCHAR(100))
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
BEGIN
DECLARE retval INT DEFAULT 1;
DECLARE dateval DATE;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET retval=-1;
SET dateval=bigint(strval);
RETURN retval;
END @

CREATE FUNCTION castalesce_bigint(strval VARCHAR(100))
RETURNS BIGINT
DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
BEGIN ATOMIC
DECLARE retval INT;
-- The following is needed because declaration of SQLEXECPTION handlers
-- isn't allowed in UDFs:
CALL castalesce_bigint_(strval);
GET DIAGNOSTICS retval = DB2_RETURN_STATUS;
IF retval = 1 THEN RETURN bigint(strval);
ELSE RETURN NULL;
END IF;
END @

There might be an error or two in there but the idea should hold (the
original thread was about date). Now:

select DAILYDOWNLOADSIZE, DAILYDOWNLOADTIME
from EDMPROD.MQT_STB_FACTS
where castalesce_bigint(DAILYDOWNLOADSIZE) is null
or castalesce_bigint(DAILYDOWNLOADTIME) is null

reveals what cannot be casted.

However, since you are doing a full table scan in your first query my
guess is that the error comes from some of the casts that's in q2 but
not in q1.

/Lennart

[Lennart Jonsson]

On 2011-05-17 06:42, Lennart Jonsson wrote:
[...]

> select DAILYDOWNLOADSIZE, DAILYDOWNLOADTIME
> from EDMPROD.MQT_STB_FACTS
> where castalesce_bigint(DAILYDOWNLOADSIZE) is null
> or castalesce_bigint(DAILYDOWNLOADTIME) is null
>

If DAILYDOWNLOADSIZE is nullable:

where ( castalesce_bigint(DAILYDOWNLOADSIZE) is null and
DAILYDOWNLOADSIZE is not null )
or ( ...

/Lennart

[kg6ypx]

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