Using < In Case Statment - 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)

Using < In Case Statment

Author

Topic

jdornan
Starting Member

4 Posts

Posted - 2009-01-28 : 02:25:57

I have a written a Select case statement to use in a database that manipulates money values in a computed column. the statement works perfectly until I try to use the < or > operator. I am sure I am screwing up the syntax somehow so any help would be greatly appreciated. Below is my code.

Select [Name], [Cost],
Expense = CASE Cost
When < 10.00 THEN (ROUND(Cost/0.25/0.86, 0))
When > 99.00 THEN (ROUND(Cost/1.3/0.86, 0))
End
From [bar].[dbo].[Winelist]

If I just use

When 10.00 THEN (ROUND(Cost/0.25/0.86, 0))

it works fine.
Any ideas?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-28 : 02:29:41

Select [Name], [Cost],
Expense = CASE
When Cost < 10.00 THEN (ROUND(Cost/0.25/0.86, 0))
When Cost > 99.00 THEN (ROUND(Cost/1.3/0.86, 0))
End
From [bar].[dbo].[Winelist]

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-28 : 02:29:45

Select [Name], [Cost],
Expense = CASE
When Cost < 10.00 THEN (ROUND(Cost/0.25/0.86, 0))
When Cost > 99.00 THEN (ROUND(Cost/1.3/0.86, 0))
End
From [bar].[dbo].[Winelist]

Jai Krishna

jdornan
Starting Member

4 Posts

Posted - 2009-01-28 : 02:35:57

Works perfectly. Thanks for the quick response.
Now just so Ill know in the future why did it work with the = comparison but not the < or > in that format.

jdornan
Starting Member

4 Posts

Posted - 2009-01-28 : 02:52:44

one more question this works fine for a query. Now here is the thing. i want to use it as the code for a computed column. How would I write it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 02:59:43

just write case with declaration like

CREATE TABLE tabl1
(
cost...,

computedcol as CASE
When Cost < 10.00 THEN (ROUND(Cost/0.25/0.86, 0))
When Cost > 99.00 THEN (ROUND(Cost/1.3/0.86, 0))
End
)

jdornan
Starting Member

4 Posts

Posted - 2009-01-28 : 03:09:02

quote:
Originally posted by visakh16

just write case with declaration like

CREATE TABLE tabl1
(
cost...,

computedcol as CASE
When Cost < 10.00 THEN (ROUND(Cost/0.25/0.86, 0))
When Cost > 99.00 THEN (ROUND(Cost/1.3/0.86, 0))
End
)

Awesome,
Thanks a lot for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 03:18:44

welcome

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-28 : 03:49:51

quote:
Originally posted by jdornan

Works perfectly. Thanks for the quick response.
Now just so Ill know in the future why did it work with the = comparison but not the < or > in that format.

ur welcome

see the simplecase, searched case functions in books online

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources