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)
 Is it possible to use >, < operarators in CASE?

Author  Topic 

agniwoni
Starting Member

28 Posts

Posted - 2007-07-12 : 09:58:39
Here is a fragment of select statement, that doesn't work (bold text generates an error):


CASE SUM(dbo.REPORT_DOCS.Movement)
when null then 'DEAD_STOCK'
WHEN 0 then 'DEAD_STOCK'
WHEN 1 then 'DEAD_STOCK'
ELSE (CASE
(SUM(dbo.REPORT_STOCKS.Stock)
/ SUM(dbo.REPORT_DOCS.Movement) * 26)
WHEN ( > 30) then 'SLOW_MOVING_STOCK'
ELSE 'NORMAL' WNE)
END

Any idea why?
Thanks for help!

PS(4 KH): i've read BOL;-)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-12 : 10:00:25
case when fullCondition then ... else ... end

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-12 : 10:06:37
....
CASE
WHEN
(SUM(dbo.REPORT_STOCKS.Stock)
/ SUM(dbo.REPORT_DOCS.Movement) * 26)
> 30 then ...


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

agniwoni
Starting Member

28 Posts

Posted - 2007-07-12 : 10:14:05
thanks: removing argument after CASE, and repeating whole condition afrter each WHERE works!
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-12 : 10:55:56
quote:
Originally posted by agniwoni

thanks: removing argument after CASE, and repeating whole condition afrter each WHERE works!


each WHERE or each WHEN?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

akumar.pune
Starting Member

1 Post

Posted - 2007-07-13 : 08:21:33
quote:
Originally posted by agniwoni

Here is a fragment of select statement, that doesn't work (bold text generates an error):


CASE SUM(dbo.REPORT_DOCS.Movement)
when null then 'DEAD_STOCK'
WHEN 0 then 'DEAD_STOCK'
WHEN 1 then 'DEAD_STOCK'
ELSE (CASE
(SUM(dbo.REPORT_STOCKS.Stock)
/ SUM(dbo.REPORT_DOCS.Movement) * 26)
WHEN ( > 30) then 'SLOW_MOVING_STOCK'
ELSE 'NORMAL' WNE)
END

Any idea why?
Thanks for help!

PS(4 KH): i've read BOL;-)




You try like this
CASE
WHEN
(SUM(dbo.REPORT_STOCKS.Stock)/
SUM(dbo.REPORT_DOCS.Movement) * 26) >30 THEN
'SLOW_MOVING_STOCK'
ELSE 'NORMAL'
END


Hope I satisfied your query.

Thanks
AmrendraK
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-07-13 : 08:24:58
did you even read the entire thread? check Madhivanan's post on 07/12/2007 : 10:06:37
He posted the exact same solution.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page
   

- Advertisement -