Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

kristo5747

Programmer
Mar 16, 2011
41
US
[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???
 
Hi kristo5747,
There's a couple of inconsistencies in your SQL that I'm pretty sure will make no difference, but just to be sure can you eliminate them:

1 - IPINFO is not prefixed with an A.
2 - CASE statement does not use AS

As I said, I don't think these will make a difference, but let me know.

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

 
Pleased for you that it's gone away. Shame we've no idea why it ever appeared in the first place! Or disappeared!!

 
Well, according to the IBM doc, there must have some non character type data in my character columns (e.g. DAILYDOWNLOADSIZE).

However, the fact that I was not experiencing contradicted the documentation.

Not sure what is up with DB2. Sometimes, it is really a fantastic RDBMS. Sometimes, it drives me barking mad.

Oh well.


 
I meant to write

"However, the fact that I was not experiencing a systematic error contradicted the documentation."

Later.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top