SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
SQLNW
SQLNW
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28121 Visits: 39939
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLNW
SQLNW
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 43
here it is, please ignore the bracket errors/syntax, main concern is fields --(value, t.result, result on) please helpppp !!!!
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28121 Visits: 39939
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLNW
SQLNW
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 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.
nusa001
nusa001
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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),
Clarie DeWayne
Clarie DeWayne
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 151
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'))


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search