Error converting data type varchar to float

Question

Error converting data type varchar to float

rodan-892007

Right there with Babe

Points: 731
More actions
August 27, 2009 at 3:33 pm

#225543

Not too familiar with temp tables or variables, but I go t this far. I ran the following query to populate a temp table:
select illitm,ilmcu,illocn,sum(iltrqt) as TotalSold
into #SoldTable
from proddta.f4111 where ildct in ('RI', 'R3', 'R4', 'II')
and datediff(day,(DATEADD(yy,ilcrdj/1000,0) + ilcrdj%1000 -1),getdate()) <365
group by illitm, ilmcu,illocn
order by illitm, ilmcu,illocn
[/code]
then tried to run the following update:
[code]
update proddta.f41021
set proddta.f41021.liurat=proddta.#SoldTable.TotalSold
from proddta.f41021
join #SoldTable
on proddta.f41021.liitm=proddta.#SoldTable.illitm and proddta.f41021.limcu=proddta.#SoldTable.ilmcu
and proddta.f41021.lilocn=proddta.#SoldTable.illocn
[/code]
Results in error:
[code]Error converting data type varchar to float.[/code]
Do I need to declare a variable in my initial select command? Thanks in advance, gurus...

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

The error is a conversion error, not a variable (syntax) error. somewhere in your data, you have a varchar that is being implicitly converted to a float, but it's not a valid value. Something like 'A1' will not convert to a float because of the "A"

rodan-892007 Right there with Babe Points: 731 More actions · Answer 2

I queried the temp table and sorted by the TotalSold column, found nothing but integers.

Could this error pertain to the join fields, or is it just for the field being set?

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 3

Did you check both tables? Are you sure there aren't any other characters in there?

Do a select where you check the join fields as well, CAST the field as a float and see if you get an error.

SELECT CAST ( proddta.#SoldTable.TotalSold as float)

rodan-892007 Right there with Babe Points: 731 More actions · Answer 4

I changed my original line to cast as a float...

select illitm,ilmcu,illocn,cast(sum(iltrqt) as float) as TotalSold into #SoldTable from proddta.f4111

then ran this...

select illitm, cast(TotalSold as float) from #SoldTable

with no errors.

:crazy:

rodan-892007 Right there with Babe Points: 731 More actions · Answer 5

it is the join fields, just tried a select and join to the temp table without update... got the original error. experimenting...

rodan-892007 Right there with Babe Points: 731 More actions · Answer 6

Found the problem, thanks for the guidance Steve. it was my original join, trying to join on illitm instead of ilitm. Those are both valid fields, but ilitm was the one I should have used.

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 7

Glad to help, happy you found it. I've been stymied by these before, and usually only catch them after digging through lots of data.