July 14, 2009 at 8:41 am
I am modifying some old code and I am running into a strange error when I run the following
select
column1
,column2
,column3
,TollFree# =CASE
WHEN LEFT(a.phone_txt,3) IN ('800','866','877','888') THEN A.phone_txt
WHEN EXISTS (
SELECT 1 FROM PhoneTbl p
WHERE
p.c_num = a.c_num
AND
p.t_cd = a.t_cd
AND
p.loc ='W'
AND
p.type ='T'
AND
LEFT(phone_txt,3) IN ('800','866','877','888')
)
THEN(
SELECT TOP 1
phone_txt
FROM
PhoneTbl p
WHERE
p.c_num = a.c_num
AND
p.t_cd = a.t_cd
AND
p.loc = 'W'
AND
p.type='T'
AND
LEFT(phone_txt,3) IN ('800','866','877','888')
)
ELSE 0
END
from ClientTbl a
I get this error
The conversion of the varchar value '8005642505' overflowed an int column. Maximum integer value exceeded.
Yes I know 8005642505 wont fit into an integer field but here is the kicker. phone_txt and a.phone txt are varchar(10) in the schema. What is making SQL server do this?
P.S. It appears to work if I change the code to select
column1
,column2
,column3
,TollFree# =CASE
WHEN LEFT(a.phone_txt,3) IN ('800','866','877','888') THEN CAST(A.phone_txt AS BIGINT)
WHEN EXISTS (
SELECT 1 FROM PhoneTbl p
WHERE
p.c_num = a.c_num
AND
p.t_cd = a.t_cd
AND
p.loc ='W'
AND
p.type ='T'
AND
LEFT(phone_txt,3) IN ('800','866','877','888')
)
THEN(
SELECT TOP 1
phone_txt
FROM
PhoneTbl p
WHERE
p.c_num = a.c_num
AND
p.t_cd = a.t_cd
AND
p.loc = 'W'
AND
p.type='T'
AND
LEFT(phone_txt,3) IN ('800','866','877','888')
)
ELSE 0
END
from ClientTbl a
This is not a numeric field. WTF.
July 14, 2009 at 9:02 am
The "ELSE" condition on your CASE statement sets the value to 0 (INT), which is probably forcing an implicit cast from varchar to int. Try changing to '0' and see if it takes away the error.
July 14, 2009 at 9:10 am
Fatal Exception Error (7/14/2009)
I am modifying some old code and I am running into a strange error when I run the following
select
column1
,column2
,column3
,TollFree# =CASE
WHEN LEFT(a.phone_txt,3) IN ('800','866','877','888') THEN A.phone_txt
WHEN EXISTS (
SELECT 1 FROM PhoneTbl p
WHERE
p.c_num = a.c_num
AND
p.t_cd = a.t_cd
AND
p.loc ='W'
AND
p.type ='T'
AND
LEFT(phone_txt,3) IN ('800','866','877','888')
)
THEN(
SELECT TOP 1
phone_txt
FROM
PhoneTbl p
WHERE
p.c_num = a.c_num
AND
p.t_cd = a.t_cd
AND
p.loc = 'W'
AND
p.type='T'
AND
LEFT(phone_txt,3) IN ('800','866','877','888')
)
ELSE 0
END
from ClientTbl a
I get this error
The conversion of the varchar value '8005642505' overflowed an int column. Maximum integer value exceeded.
Yes I know 8005642505 wont fit into an integer field but here is the kicker. phone_txt and a.phone txt are varchar(10) in the schema. What is making SQL server do this?
P.S. It appears to work if I change the code to
selectcolumn1
,column2
,column3
,TollFree# =CASE
WHEN LEFT(a.phone_txt,3) IN ('800','866','877','888') THEN CAST(A.phone_txt AS BIGINT)
WHEN EXISTS (
SELECT 1 FROM PhoneTbl p
WHERE
p.c_num = a.c_num
AND
p.t_cd = a.t_cd
AND
p.loc ='W'
AND
p.type ='T'
AND
LEFT(phone_txt,3) IN ('800','866','877','888')
)
THEN(
SELECT TOP 1
phone_txt
FROM
PhoneTbl p
WHERE
p.c_num = a.c_num
AND
p.t_cd = a.t_cd
AND
p.loc = 'W'
AND
p.type='T'
AND
LEFT(phone_txt,3) IN ('800','866','877','888')
)
ELSE 0
END
from ClientTbl a
This is not a numeric field. WTF.
Change your ELSE 0 to ELSE '0'
July 14, 2009 at 11:03 am
Your CASE can only return only data type. So - your ELSE statement is likely screwing things up, since it's the only one that definitely gives a type (numeric by the way).
Try simply changing to
ELSE '0'
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 14, 2009 at 12:00 pm
Wow I can't believe I missed that.
Thanks folks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply