Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL problem - nvarchar value overflowed an int column

Posted on 2003-12-04
6
Medium Priority
?
4,876 Views
Last Modified: 2013-12-24
i am using the following query

SELECT POSITION_LIST.EMPID, REPORTS_TO, USER_PROFILE.EMPID, USER_PROFILE.USERFNAME, USER_PROFILE.USERLNAME
FROM POSITION_LIST LEFT JOIN HR_TOOLBOX_USER_PROFILE ON EMPID = USER_PROFILE.EMPID
WHERE POSITION_LIST.EMPID Is Not Null AND REPORTS_TO in (SELECT POSID
                                    FROM POSITION_LIST
                                    WHERE POSITION_LIST.EMPID=<cfqueryparam value="#UID#">);

which results in
[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of the nvarchar value '9261416167' overflowed an int column. Maximum integer value exceeded.

the table POSITION_LIST matches POSID and REPORTS_TO
the table was originally designed in ACCESSS with POSID as a random Autonumber
max value is 2146449165 min value is -2138269668
UID id a 7 digit number 0500000 - 2000000
if i remove the subquery it works fine but i dont get the results i need SQL or CF solutions welcome

TIA

0
Comment
Question by:James Rodgers
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 9

Accepted Solution

by:
shooksm earned 2000 total points
ID: 9875428
Is this SQL Server 2000?

You could try converting your values to BIGINTs which support values qreater than 2,147,483,647.  Change the following lines:

WHERE POSITION_LIST.EMPID Is Not Null AND REPORTS_TO in (SELECT POSID
                              FROM POSITION_LIST
                              WHERE POSITION_LIST.EMPID=<cfqueryparam value="#UID#">);

To:

WHERE POSITION_LIST.EMPID Is Not Null AND CONVERT(BIGINT, REPORTS_TO) in (SELECT CONVERT(BIGINT, POSID)
                              FROM POSITION_LIST
                              WHERE POSITION_LIST.EMPID=<cfqueryparam value="#UID#">);
0
 
LVL 25

Author Comment

by:James Rodgers
ID: 9875478
>>Is this SQL Server 2000?
yes

but i got this error

Type bigint is not a defined system type.
0
 
LVL 9

Expert Comment

by:shooksm
ID: 9875595
Couple things, you can try the CAST function instead:

WHERE POSITION_LIST.EMPID Is Not Null AND CAST(REPORTS_TO AS BIGING) in (SELECT CAST(POSID AS BIGINT)

Also, check to see the what the Database compatibility level is set to.  It should be 80 which is SQL Server 2000.  If it is set to anything less, that will be a problem as the BIGINT datatype was introduced in SQL 2000.
0
Understanding Web Applications

Without even knowing it, most of us are using web applications on a daily basis. Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We often confuse these web applications tools for websites.  So, what is the difference?

 
LVL 25

Author Comment

by:James Rodgers
ID: 9875696
>>Is this SQL Server 2000?
i have just been informed that the server is SQL 7 so no bigint datatype

would binary work?

any other solutions, other than changing the datatype in the table?
0
 
LVL 9

Assisted Solution

by:shooksm
shooksm earned 2000 total points
ID: 9875751
Trying casting both to a float or real and see if that works.  Also, you could convert the numeric data to a NVARCHAR but you would loose some performance as textual comparision is usually slower than numeric.
0
 
LVL 25

Author Comment

by:James Rodgers
ID: 9875783
BINGO...

tried real and it worked
0

Featured Post

Understanding Web Applications

Without even knowing it, most of us are using web applications on a daily basis. Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We often confuse these web applications tools for websites.  So, what is the difference?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question