INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
[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???
 

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

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

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

(OP)
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..........

 

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

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

 

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

(OP)
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.


 

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

(OP)
I meant to write

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

Later.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close