Hi,
I have a table that has a field set to decimal data type and the following
query needs to update it to a varchar type if it meets the criteria. Why am
I still getting the "Error converting varchar to numeric" error? Thanks!
UPDATE [Activities Data]
SET [Activities Data].[SUMMARY DISPOSITION] =
Case
When ([ACTCD] In (1,5,6))
Then Cast('In Process' as varchar (20))
When ([ACTCD] In (3,7,8,16,17))
Then Cast('Completed' as varchar (20))
When ([ACTCD] In (2,9,10,11,12,13,15))
Then Cast('Closed' as varchar (20))
ELSE [ACTCD]
END
WHERE ([Activities Data].PGMNO In (11165,11175,11177,11169,21175))>>>
UPDATE [Activities Data]
SET [Activities Data].[SUMMARY DISPOSITION] =
Case
When ([ACTCD] In (1,5,6))
Then Cast('In Process' as varchar (20))
When ([ACTCD] In (3,7,8,16,17))
Then Cast('Completed' as varchar (20))
When ([ACTCD] In (2,9,10,11,12,13,15))
Then Cast('Closed' as varchar (20))
ELSE [ACTCD]
END
WHERE ([Activities Data].PGMNO In (11165,11175,11177,11169,21175))arkred">
Instead of constantly running an UPDATE statement like this, wouldn't it
make more sense to create a VIEW or a computed column?
Anyway, CASE is an expression that returns a single value. Regardless of
the outcome, this value must be at least implicitly convertible to the same
data type. You have three varchar results, and an INT result, and the only
one you didn't convert is the only one that needed it. How about :
UPDATE [Activities Data]
SET [Activities Data].[SUMMARY DISPOSITION] =
Case
When ([ACTCD] In (1,5,6))
Then 'In Process'
When ([ACTCD] In (3,7,8,16,17))
Then 'Completed'
When ([ACTCD] In (2,9,10,11,12,13,15))
Then 'Closed'
ELSE CONVERT(VARCHAR(20), [ACTCD])
END
WHERE ([Activities Data].PGMNO In (11165,11175,11177,11169,21175))
I still think it's a mistake to store this redundant data because you can
already figure it out at any time; and queries won't have to verify that the
UPDATE has taken place to be sure that the answer is correct.
"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:0911EBFD-9E0F-4E67-8799-137C58A7E1DD@.microsoft.com...
> Hi,
> I have a table that has a field set to decimal data type and the following
> query needs to update it to a varchar type if it meets the criteria. Why
> am
> I still getting the "Error converting varchar to numeric" error? Thanks!
>
> UPDATE [Activities Data]
> SET [Activities Data].[SUMMARY DISPOSITION] =
> Case
> When ([ACTCD] In (1,5,6))
> Then Cast('In Process' as varchar (20))
> When ([ACTCD] In (3,7,8,16,17))
> Then Cast('Completed' as varchar (20))
> When ([ACTCD] In (2,9,10,11,12,13,15))
> Then Cast('Closed' as varchar (20))
> ELSE [ACTCD]
> END
> WHERE ([Activities Data].PGMNO In (11165,11175,11177,11169,21175))|||If the field datatype is decimal (as you indicated) then you are getting the
error because you are trying to put a varchar into a decimal -and that won't
work.
Something doesn't look right in this code example. 'In Process',
'Completed', 'Closed', are in fact strings -why are you needing to cast()
them as varchar()?
What is the datatype of the [Activities Data].[SUMMARY DISPOSITION] field?
Having the actual table DDL would be useful.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:0911EBFD-9E0F-4E67-8799-137C58A7E1DD@.microsoft.com...
> Hi,
> I have a table that has a field set to decimal data type and the following
> query needs to update it to a varchar type if it meets the criteria. Why
> am
> I still getting the "Error converting varchar to numeric" error? Thanks!
>
> UPDATE [Activities Data]
> SET [Activities Data].[SUMMARY DISPOSITION] =
> Case
> When ([ACTCD] In (1,5,6))
> Then Cast('In Process' as varchar (20))
> When ([ACTCD] In (3,7,8,16,17))
> Then Cast('Completed' as varchar (20))
> When ([ACTCD] In (2,9,10,11,12,13,15))
> Then Cast('Closed' as varchar (20))
> ELSE [ACTCD]
> END
> WHERE ([Activities Data].PGMNO In (11165,11175,11177,11169,21175))|||Patrice wrote:
> Hi,
> I have a table that has a field set to decimal data type and the following
> query needs to update it to a varchar type if it meets the criteria. Why
am
> I still getting the "Error converting varchar to numeric" error? Thanks!
>
> UPDATE [Activities Data]
> SET [Activities Data].[SUMMARY DISPOSITION] =
> Case
> When ([ACTCD] In (1,5,6))
> Then Cast('In Process' as varchar (20))
> When ([ACTCD] In (3,7,8,16,17))
> Then Cast('Completed' as varchar (20))
> When ([ACTCD] In (2,9,10,11,12,13,15))
> Then Cast('Closed' as varchar (20))
> ELSE [ACTCD]
> END
> WHERE ([Activities Data].PGMNO In (11165,11175,11177,11169,21175))
What is the datatype of [Activities Data].[SUMMARY DISPOSITION]? What
happens in your CASE statement is ACTCD is not one of the numeric values
you've listed? My assumption is that one of your records is falling
through the CASE statment, returning the true value of ACTCD, which is
then being stuffed into [SUMMARY DISPOSITION], and that's where your
error is coming from.
