Tags: adding, converting, error, numeric, programming, query, records, run, select, sql, type, varchar

Error converting data type varchar to numeric.

On Programmer » SQL

6,568 words with 2 Comments; publish: Thu, 29 May 2008 00:28:00 GMT; (20046.88, « »)

Hi All

I am getting an 'Error converting data type varchar to numeric' when i run a

query to select all the records and adding an additional column 'Comm' which

is an Amonut column copy but, inserts a 'DataError' message whereever the

amount is a zero. I would like to keep the table structure as is. How can i

achieve this? I have the table, sample data and the query pasted below:

--This is what i am trying to achive:

F_id Amount Comm

-- -- --

a 57 57

b 7 7

c 0 DataError

d 0 DataError

e 5 5

--Table

Create table #TempTb78 (F_id char(2), Amount decimal(18,2))

--Samlpe Data

insert #TempTb78 select 'a','57'

union all select 'b','7'

union all select 'c','0'

union all select 'd','0'

union all select 'e','5'

--select * from #TempTb78

select F_id, Amount,

Comm = CASE

WHEN Amount ='0' THEN 'DataError'

WHEN Amount <> '0' THEN Amount

END

from #TempTb78

--drop table #TempTb78

I have tried and this and it did not work:

1.

select F_id, Amount,

Cast (Comm as varchar(10)) = CASE

WHEN Amount ='0' THEN 'DataError'

WHEN Amount <> '0' THEN Amount

END

from #TempTb78

2.

select F_id, Amount,

Comm = CASE

WHEN Cast (Amount as varchar(10)) ='0' THEN 'DataError'

WHEN Amount <> '0' THEN Amount

END

from #TempTb78

Thank you in advance.

All Comments

Leave a comment...

  • 2 Comments
    • Since in Comm you are trying to put both a numeric and a string depending on

      the value of Amount, SQL Server must convert one or the other. If you don't

      tell it what to do it uses the datatype precedence and tries to convert the

      string to a number (because numbers are a higher precedence under the rules

      for SQL) and gets an error. To avoid this, tell it to convert the number,

      something like:

      select F_id, Amount,

      CASE

      WHEN Amount =0 THEN 'DataError'

      WHEN Amount <> 0 THEN Cast (Amount As varchar(21))

      END

      from #TempTb78

      Tom

      "MittyKom" <MittyKom.sql.todaysummary.com.discussions.microsoft.com> wrote in message

      news:FEBE6F6F-7E9B-476D-B25B-C80C37A27C29.sql.todaysummary.com.microsoft.com...

      > Hi All

      > I am getting an 'Error converting data type varchar to numeric' when i run

      > a

      > query to select all the records and adding an additional column 'Comm'

      > which

      > is an Amonut column copy but, inserts a 'DataError' message whereever the

      > amount is a zero. I would like to keep the table structure as is. How can

      > i

      > achieve this? I have the table, sample data and the query pasted below:

      > --This is what i am trying to achive:

      > F_id Amount Comm

      > -- -- --

      > a 57 57

      > b 7 7

      > c 0 DataError

      > d 0 DataError

      > e 5 5

      >

      > --Table

      > Create table #TempTb78 (F_id char(2), Amount decimal(18,2))

      > --Samlpe Data

      > insert #TempTb78 select 'a','57'

      > union all select 'b','7'

      > union all select 'c','0'

      > union all select 'd','0'

      > union all select 'e','5'

      > --select * from #TempTb78

      > select F_id, Amount,

      > Comm => CASE

      > WHEN Amount ='0' THEN 'DataError'

      > WHEN Amount <> '0' THEN Amount

      > END

      > from #TempTb78

      > --drop table #TempTb78

      > I have tried and this and it did not work:

      > 1.

      > select F_id, Amount,

      > Cast (Comm as varchar(10)) => CASE

      > WHEN Amount ='0' THEN 'DataError'

      > WHEN Amount <> '0' THEN Amount

      > END

      > from #TempTb78

      > 2.

      > select F_id, Amount,

      > Comm => CASE

      > WHEN Cast (Amount as varchar(10)) ='0' THEN 'DataError'

      > WHEN Amount <> '0' THEN Amount

      > END

      > from #TempTb78

      > Thank you in advance.

      #1; Thu, 29 May 2008 00:29:00 GMT
    • Mitty,

      Is this what you're trying to do?

      select F_id, Amount,

      Comm = CASE

      WHEN Amount ='0' THEN 'DataError'

      ELSE cast(Amount as varchar(10))

      END

      from #TempTb78

      -- Bill

      I think there is some other problem

      "MittyKom" <MittyKom.sql.todaysummary.com.discussions.microsoft.com> wrote in message

      news:FEBE6F6F-7E9B-476D-B25B-C80C37A27C29.sql.todaysummary.com.microsoft.com...

      > Hi All

      > I am getting an 'Error converting data type varchar to numeric' when i run

      > a

      > query to select all the records and adding an additional column 'Comm'

      > which

      > is an Amonut column copy but, inserts a 'DataError' message whereever the

      > amount is a zero. I would like to keep the table structure as is. How can

      > i

      > achieve this? I have the table, sample data and the query pasted below:

      > --This is what i am trying to achive:

      > F_id Amount Comm

      > -- -- --

      > a 57 57

      > b 7 7

      > c 0 DataError

      > d 0 DataError

      > e 5 5

      >

      > --Table

      > Create table #TempTb78 (F_id char(2), Amount decimal(18,2))

      > --Samlpe Data

      > insert #TempTb78 select 'a','57'

      > union all select 'b','7'

      > union all select 'c','0'

      > union all select 'd','0'

      > union all select 'e','5'

      > --select * from #TempTb78

      > select F_id, Amount,

      > Comm => CASE

      > WHEN Amount ='0' THEN 'DataError'

      > WHEN Amount <> '0' THEN Amount

      > END

      > from #TempTb78

      > --drop table #TempTb78

      > I have tried and this and it did not work:

      > 1.

      > select F_id, Amount,

      > Cast (Comm as varchar(10)) => CASE

      > WHEN Amount ='0' THEN 'DataError'

      > WHEN Amount <> '0' THEN Amount

      > END

      > from #TempTb78

      > 2.

      > select F_id, Amount,

      > Comm => CASE

      > WHEN Cast (Amount as varchar(10)) ='0' THEN 'DataError'

      > WHEN Amount <> '0' THEN Amount

      > END

      > from #TempTb78

      > Thank you in advance.

      #2; Thu, 29 May 2008 00:30:00 GMT