Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 793
  • Last Modified:

MS SQL max id

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.

Please help how to resolve this problem.

Rgds.
Mehram
0
Mehram
Asked:
Mehram
1 Solution
 
aprestoCommented:
Hi campbel8,

change the datatype from int to long in the table
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the fields acc_id and paraccountid need to be of data type bigint, and not int
0
 
aprestoCommented:
sorry i meant:

Hi Mehram,

change the datatype from int to long in the table

:o)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
MehramAuthor Commented:
Sir the acc_id and paraccountid both are varchar (100)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
assystCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now