December 13, 2010 at 8:04 am
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
December 13, 2010 at 8:30 am
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
December 13, 2010 at 11:28 am
here it is, please ignore the bracket errors/syntax, main concern is fields --(value, t.result, result on) please helpppp !!!!
December 13, 2010 at 11:59 am
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
December 13, 2010 at 1:47 pm
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.
July 27, 2011 at 2:32 pm
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),
December 16, 2012 at 11:41 am
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'))
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply