current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.5 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 0 down vote favorite

Here's the sequence of my query:

SELECT
      CASE
         WHEN ((BarCode IS NOT NULL) AND (ExternelBarCode IS NULL)) THEN BarCode
         WHEN ((BarCode IS NULL) AND (ExternelBarCode IS NOT NULL)) THEN CAST(ExternelBarCode AS bigint)
         ELSE ExternelBarCode -- When both exist
      END AS TicketBarCode
...

When I run it, I've got the following message:

Arithmetic overflow error converting expression to data type bigint.

The column BarCode is of type bigint, and column ExternelBarCode is of type varchar(250).

BarCode = 178625624324,
ExternelBarCode = 2015591149641227565492

How can I resolve this issue?

share|improve this question
1  
try float/decimal(6,2) and cast else part as well – KumarHarsh Dec 19 '13 at 5:31
    
What data is it trying to convert from ExternelBarCode? – Szymon Dec 19 '13 at 5:33
    
Try to use as said by @Szymon – Vignesh Kumar Dec 19 '13 at 5:43
up vote 4 down vote accepted

The value 2,015,591,149,641,227,565,492 is greater than the maximum value allowed in bigint 2^63-1 (9,223,372,036,854,775,807) so the conversion is not possible.

If you need to return a numeric value, you can cast it to decimal(38,0)

SELECT
      CASE
         WHEN ((BarCode IS NOT NULL) AND (ExternelBarCode IS NULL)) THEN CAST(BarCode as decimal(38,0))
         WHEN ((BarCode IS NULL) AND (ExternelBarCode IS NOT NULL)) THEN CAST(ExternelBarCode AS decimal(38,0))
         ELSE cast(ExternelBarCode as decimal(38,0)) -- When both exist
      END AS TicketBarCode
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.