Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Conversion failed when converting the varchar value '12.80' to data type int. Expand / Collapse
Author
Message
Posted Monday, December 13, 2010 8:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 23, 2010 8:00 PM
Points: 19, Visits: 43
hi all, request your help.

Conversion failed when converting the varchar value '12.80' to data type int.

I checked all datatypes in my proc, they are all consistent.

thanks
Post #1033821
Posted Monday, December 13, 2010 8:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 12,897, Visits: 32,105
you'll have to show us the procedure; the error itself is not descriptive enough to point out anything obvious.

the onlything i can offer is that if you are storing that decimal as a varchar, you'll have to double convert i think;
this returns the exact error you described:
select CONVERT(int,'12.80')


Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '12.80' to data type int.



select  CONVERT(int,CONVERT(decimal(19,2),'12.80'))



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1033846
Posted Monday, December 13, 2010 11:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 23, 2010 8:00 PM
Points: 19, Visits: 43
here it is, please ignore the bracket errors/syntax, main concern is fields --(value, t.result, result on) please helpppp !!!!

Post #1033995
Posted Monday, December 13, 2010 11:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 12,897, Visits: 32,105
the error is your data types in your first table variable, and the way you are doing your case statements.

your first table is declared all as varchar(20), but then your second table is declared as floats, but you join them together, requiring a conversion
then in your CASE statements, you are trying to mix data types...CASe one value is a float, else it's a varchar...you cannot do that.

= (case 
when v.data_type_id in (1,2) then convert(float, vs.L_rej)
when vs.L_Rej in ('Fail','No','0.00') then '0'
when vs.L_Rej in ('Pass','Yes') then '1'
else vs.L_Rej end),

--should be
= (case
when v.data_type_id in (1,2) then convert(float, vs.L_rej)
when vs.L_Rej in ('Fail','No','0.00') then 0.00
when vs.L_Rej in ('Pass','Yes') then 1.00
else vs.L_Rej end),

this should work correctly, allowing for the cleanup of the columns you trimmed out for the example
DECLARE @tab1 table
(
spec int,
low_rej float,
low_wa float,
low_us float,
target float,
up_us float,
up_wa float,
up_rej float,
sampling_interval int,
Value varchar(35),

)

INSERT INTO @tab1
SELECT distinct
'spec' = (case
when vs.l_rej is not null then 1
when vs.l_wa is not null then 1
when vs.l_us is not null then 1
when vs.target is not null then 1
when vs.u_us is not null then 1
when vs.u_wa is not null then 1
when vs.u_rej is not null then 1
else 0
end),
d.data_type_desc,
'low_rej' = (case
when v.data_type_id in (1,2) then convert(float, vs.L_rej)
when vs.L_Rej in ('Fail','No','0.00') then 0.00
when vs.L_Rej in ('Pass','Yes') then 1.00
else vs.L_Rej end),
'low_wa' = (case
when vs.l_Wa in ('Fail','No','0.00') then 0.00
when vs.l_wa in ('Pass','Yes') then 1.00
else vs.l_wa end),
'low_us' = (case
when vs.l_us in ('Fail','No','0.00') then 0.00
when vs.l_us in ('Pass','Yes') then 1.00
else vs.l_us end),
'Target' = (case
when vs.Target in ('Fail','No','0.00') then 0.00
when vs.Target in ('Pass','Yes') then 1.00
else vs.Target end),
'up_user' = (case
when vs.u_us in ('Fail','No','0.00') then 0.00
when vs.u_us in ('Pass','Yes') then 1.00
else vs.u_us end),
'up_warn' = (case
when vs.u_Wa in ('Fail','No','0.00') then 0.00
when vs.u_wa in ('Pass','Yes') then 1.00
else vs.u_wa end),
'up_reject' = (case
when vs.U_Rej in ('Fail','No','0.00') then 0.00
when vs.U_Rej in ('Pass','Yes') then 1.00
else vs.U_Rej end),
'Value' = (case
when t.Result in ('Fail','No','0.00') then 0.00
when t.Result in ('Pass','Yes') then 1.00
else t.Result end),


FROM tes t
JOIN vara v on t.var_id = v.var_id
JOIN var_sp vs on v.var_id = vs.var_id
JOIN prod p on vs.prod_id = p.prod_id
JOIN prod_starts ps on p.prod_id = ps.prod_id
and (t.result_on >= ps.start_time
and (t.Result_on <= ps.end_time or ps.end_time is null))
JOIN data_type d on v.data_type_id = d.data_type_id
JOIN users u on t.ent_by = u.user_id
where t.Result_on between @startdate and @Enddate
and (vs.expiration_date > @startdate or vs.expiration_date is null)
and v.data_type_id in (1,2,51,54)
and (t.result <> vs.target or vs.target is null)
order by t.result_on, t.entry_on, p.prod_code, vs.var_id

DECLARE @tab2 table
(
Low_rej float,
Low_wa float,
Low_us float,
Target float,
up_us float,
Up_wa float,
up_rej float,
Value float,
data_id int,
data_desc varchar(50),
result_type int,
result_on datetime,
entry_on datetime,
prod_start datetime,
prod_end datetime,
operator varchar(35),
comment_id int,
comment_user_id int,
comment_entry datetime,
comment_text varchar(500)
)

INSERT INTO @tab2
select
'low_rej' = (case
when data_id in (1,2,51,54) then convert(float, low_rej)
else low_rej
end),
'low_wa' = (case
when data_id in (1,2,51,54) then convert(float, low_wa)
else low_wa
end),
'low_us' = (case
when data_id in (1,2,51,54) then convert(float, low_us)
else low_us
end),
'target' = (case
when data_id in (1,2,51,54) then convert(float, target)
else target
end),
'up_us' = (case
when data_id in (1,2,51,54) then convert(float, up_us)
else up_us
end),
'up_wa' = (case
when data_id in (1,2,51,54) then convert(float, up_wa)
else up_wa
end),
'up_rej' = (case
when data_id in (1,2,51,54) then convert(float, up_rej)
else up_rej
end),
'value' = (case
when data_id in (1,2,51,54) then convert(float, value)
else value
end),

from @tab2 r
LEFT JOIN comments c on r.comment_id = c.comment_id
LEFT JOIN users u on c.user_id = u.user_id
where spec >= 1

DECLARE @final table
(
low_rej float,
low_wa float,
low_us float,
Target float,
up_us float,
up_wa float,
up_rej float,
Value float,
result_type int,
no_upper int,
)

INSERT INTO @final
select
low_rej,
low_wa,
low_us,
target,
up_us,
up_wa,
up_rej,
value,
result_on,
ent_on,
'result_type' = (case
when data_id = 51 then 1
when value > isnull(up_rej, up_wa) then 1
when value <= isnull(up_rej, up_wa) then 0
when value > target then 2
else null
end),
'no_upper' = (case
when up_us is not null then 1
when up_wa is not null then 1
when up_rej is not null then 1
else 0
end),
prod_start,
prod_end,
'comment_text' = (case
when comment_text < 'a' then null
else comment_operator + ' (' + convert(varchar(23),comment_entry) + ') ' + comment_text
end)
from @tab2 c
JOIN prod_units pu on c.pu_id = pu.pu_id

select *
from @final
where no_upper <> 0
and result_type <> 0
order by pu_id, result_on, prod_code, var_id




Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1034013
Posted Monday, December 13, 2010 1:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 23, 2010 8:00 PM
Points: 19, Visits: 43
thank a million for looking at my query. I understood the logic you said.

I tried your way but I am still getting the error cant convert varchar 12.8 to datatype int. As soon as I add in @tab1


--else CAST(t.result AS float) end), ------------** instead of ---else t.Result end),


It works fine



'Value' = (case
when t.Result in ('Fail','No','0.00') then '0.00'
when t.Result in ('Pass','Yes') then '1.00'
--else CAST(t.result AS float) end), ------------**
else t.Result end),

please tell me what do you think/ thanks a ton.
Post #1034066
Posted Wednesday, July 27, 2011 2:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 30, 2013 8:37 AM
Points: 1, Visits: 18
It might be too late but maybe other are interested:



'Value' = (
CASE
WHEN t.Result in ('Fail','No','0.00') THEN '0.00'
WHEN t.Result in ('Pass','Yes') THEN '1.00'
ELSE CONVERT(VARCHAR, CAST(t.result AS float))
END),

OR

'Value' = (
CASE
WHEN t.Result in ('Fail','No','0.00') THEN 0.00
WHEN t.Result in ('Pass','Yes') THEN 1.00
ELSE CAST(t.result AS float)
END),
Post #1149659
Posted Sunday, December 16, 2012 11:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 23, 2014 12:38 PM
Points: 14, Visits: 127
Lowell, thank you! Your post fixed my problem converting Decimal stored as Varchar into an INT (still working in 2005 & 2008)


select  CONVERT(int,CONVERT(decimal(19,2),'12.80'))

Post #1397014
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse