Problem with CASE statement and Explicit Convert

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)

Problem with CASE statement and Explicit Convert

Author

Topic

mivey4
Yak Posting Veteran

66 Posts

Posted - 2009-10-24 : 07:55:24

Hi!

I have a question concerning an anomaly that I am seeing with an explicit conversion in a CASE statement where it seems as though my Conversion is being ignored when it's placed within the context of the statement.

Example: This is what I want
SELECT CONVERT(numeric(10,3),0) As Result
Results -> 0.000

But in a CASE statement, this is what I am getting:

SELECT CASE WHEN BottlesPerCase = 0 THEN
CONVERT(numeric(10,3),0)
ELSE ISNULL(CONVERT(numeric(10,3),BottlesInStock) / Products.BottlesPerCase,0)
END AS Result
Results -> 0.00000000000000

If I execute the second statement and comment the ELSE block, then I get 0.000.

It's as though the explicit CONVERT is being ignored when placed in the CASE statement.

Can anyone offer any suggestions as to why this is happening, or even better; offer a way around the issue???

I appreciate the feedback!
Thanks

Oracle OCA
Adaptec ACSP

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-24 : 08:04:20

[code]
SELECT CASE WHEN BottlesPerCase = 0 THEN
CONVERT(numeric(10,3),0)
ELSE ISNULL(convert(numeric(10,3), CONVERT(numeric(10,3),BottlesInStock) / Products.BottlesPerCase ),0)
END AS Result
[/code]

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-24 : 08:08:54

The only explizit convert happens to BottlesInStock in your ELSE.
What is about BottlesPerCase?
And if ISNULL comes to work there is no convert, there is a simple 0.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-24 : 08:10:06

Oh yes, kh never sleeps!

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-24 : 08:14:07

Yes. It's still early here. The night is still young. I am going out soon

KH
[spoiler]Time is always against us[/spoiler]

mivey4
Yak Posting Veteran

66 Posts

Posted - 2009-10-24 : 08:17:30

Thanks guys!

Both of your feedbacks made sense, I wasn't looking close enough into the code.

KH, thanks for the example it resolved my concerns. I needed to perform an explicit conversion on the actual field in the equation as well to force the desired results.

Have a good one!!!

Oracle OCA
Adaptec ACSP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources