Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

MS SQL max id

Posted on 2006-07-18
Medium Priority
791 Views
Sir, I am using below statement and it is showing error following:

select MAI=max(acc_id)+1, x.Paraccountid from
(select acc_id, paraccountid, title from AC_CHARTOFACC where paraccountid ='1002001001')x
group by x.paraccountid

Error:
Server: Msg 248, Level 16, State 1, Line 1
The conversion of the varchar value '1002001001001' overflowed an int column. Maximum integer value exceeded.

Rgds.
Mehram
0
Question by:Mehram
[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

LVL 23

Expert Comment

ID: 17129746
Hi campbel8,

change the datatype from int to long in the table
0

LVL 143

Expert Comment

ID: 17129747
the fields acc_id and paraccountid need to be of data type bigint, and not int
0

LVL 23

Expert Comment

ID: 17129751
sorry i meant:

Hi Mehram,

change the datatype from int to long in the table

:o)
0

Author Comment

ID: 17129767
Sir the acc_id and paraccountid both are varchar (100)
0

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 17129802
>Sir the acc_id and paraccountid both are varchar (100)

select MAI=cast(max(cast(acc_id as bigint))+1 as varchar(100)), x.Paraccountid from
(select acc_id, paraccountid, title from AC_CHARTOFACC where paraccountid ='1002001001')x
group by x.paraccountid
0

LVL 7

Expert Comment

ID: 17129843
Try this query....

If its a varchar field, you have to convert it to a numeric field

We can make use of CONVERT funtion or CAST function for this purpose...

Convert(data type,field name)
Cast (field name as data type)

select MAI=max(convert(numeric,acc_id))+1, x.Paraccountid from
(select acc_id, paraccountid, title from AC_CHARTOFACC where paraccountid ='1002001001') x
group by x.paraccountid
0

Featured Post

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differeâ€¦
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with â€¦
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of demâ€¦
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrasâ€¦
Suggested Courses
Course of the Month9 days, 1 hour left to enroll