current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
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

1 Answer 1

up vote 2 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.