error in case statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

error in case statement

Author

Topic

skybvi
Posting Yak Master

193 Posts

Posted - 2012-02-10 : 10:01:35

Hi,
update abc123
SET abc123.WH1_Stock =
case when(abc123.WH1_Stock = '0.00000')
then IS NULL
else abc123.WH1_Stock
end

I am gettting "Incorrect syntax near "IS"
the datatype for WH1_Stock is numeric (20,5).

Regards,
Sushant
DBA
Virgin Islands(U.K)

skybvi
Posting Yak Master

193 Posts

Posted - 2012-02-10 : 10:03:59

If i change 'IS NULL' to ''

then i get

Error converting data type varchar to numeric.

Regards,
Sushant
DBA
Virgin Islands(U.K)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-10 : 10:42:16

Don't use IS unless checking for NULL

try:


UPDATE abc123
SET
	[WH1_Stock] = CASE
		WHEN [WH1_Stock] = 0.00000 THEN NULL
		ELSE [WH1_Stockend]
	END

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 10:55:59

you just need this


update abc123
SET abc123.WH1_Stock = NULLIF(abc123.WH1_Stock ,0.00000)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skybvi
Posting Yak Master

193 Posts

Posted - 2012-02-10 : 10:56:02

Thanks charlie,
It worked.

Regards,
Sushant
DBA
Virgin Islands(U.K)

skybvi
Posting Yak Master

193 Posts

Posted - 2012-02-10 : 10:57:24

Thanks Visakh.

Regards,
Sushant
DBA
Virgin Islands(U.K)

skybvi
Posting Yak Master

193 Posts

Posted - 2012-02-10 : 11:42:32

:(
now wtf happened here.

update abc123
set Itemtype= CASE
WHEN cast(itemtype as Varchar(7))='1' tHEN 'ACTIVE'
WHEN cast(itemtype as Varchar(7))='2' then 'DISC'
else Itemtype
end.

P.S. Itemtype datatype is smallint.
Error:-- Conversion failed when converting the varchar value 'ACTIVE' to data type smallint.

Regards,
Sushant
DBA
Virgin Islands(U.K)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 11:58:01

thats obvious. how do you think you can put values ACTIVE ,DISC etc in smallint field? What meaning does it make?

you're trying to put values to Itemtype which is smallint in your above update which is wht it breaks

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skybvi
Posting Yak Master

193 Posts

Posted - 2012-02-10 : 11:59:25

quote:
Originally posted by visakh16

thats obvious. how do you think you can put values ACTIVE ,DISC etc in smallint field? What meaning does it make?

you're trying to put values to Itemtype which is smallint in your above update which is wht it breaks

But i first trying to change itemtype to varchar

Regards,
Sushant
DBA
Virgin Islands(U.K)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 12:04:18

quote:
Originally posted by skybvi

quote:
Originally posted by visakh16

thats obvious. how do you think you can put values ACTIVE ,DISC etc in smallint field? What meaning does it make?

you're trying to put values to Itemtype which is smallint in your above update which is wht it breaks

But i first trying to change itemtype to varchar

Regards,
Sushant
DBA
Virgin Islands(U.K)

nope..you're just changing type for comparison but not actual type in table ie it still remains as smallint only
so once you do comparison and you put final value back it again tries to convert it back to smallint and breaks as ACTIVE etc are not numeric.

so if you really want to do this, you should do it like below


ALTER TABLE abc123 ALTER COLUMN itemtype varchar(7) NULL
GO

update abc123
set Itemtype= CASE itemtype 
WHEN '1' tHEN 'ACTIVE'
WHEN '2' then 'DISC'
else Itemtype
end.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skybvi
Posting Yak Master

193 Posts

Posted - 2012-02-10 : 12:18:04

Yes visakh, its working now...

But i don't understand 1 thing,
Every time we do change of datatypes by CAST and CONVERT statements,

then, why this time we altering the table design to change the datatype of a column.

Whats the difference this time.

Regards,
Sushant
DBA
Virgin Islands(U.K)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 12:33:16

quote:
Originally posted by skybvi

Yes visakh, its working now...

But i don't understand 1 thing,
Every time we do change of datatypes by CAST and CONVERT statements,

then, why this time we altering the table design to change the datatype of a column.

Whats the difference this time.

Regards,
Sushant
DBA
Virgin Islands(U.K)

you can change data type using cast or convert in SELECT but in that case it gets changed only in the resultset not in actual objects where column resides. so whenever you try to store some value back like you did above it tries to convert it back to its native data type and fails if its imcompatible. so if ever you want store changed data type values back in table column you need to alter column data type before hand and then apply your rules of conversion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skybvi
Posting Yak Master

193 Posts

Posted - 2012-02-10 : 14:21:03

quote:
Originally posted by visakh16

quote:
Originally posted by skybvi

Yes visakh, its working now...

But i don't understand 1 thing,
Every time we do change of datatypes by CAST and CONVERT statements,

then, why this time we altering the table design to change the datatype of a column.

Whats the difference this time.

Regards,
Sushant
DBA
Virgin Islands(U.K)

you can change data type using cast or convert in SELECT but in that case it gets changed only in the resultset not in actual objects where column resides. so whenever you try to store some value back like you did above it tries to convert it back to its native data type and fails if its imcompatible. so if ever you want store changed data type values back in table column you need to alter column data type before hand and then apply your rules of conversion

Cool....Thanks a lot.

Regards,
Sushant
DBA
Virgin Islands(U.K)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 16:05:02

welcome

Hope I was able to explain it clearly for you to understand

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources