> in Select Case

[Kliot]

I have a question about using greater and less than in a case statement.


CASE value
WHEN > 10 THEN Something
WHEN < 10 THEN SomethingElse
...
END

This doesn't work. The "> <" is not valid syntax, the following statement would work:

CASE
WHEN value > 10 THEN Something
WHEN value < 10 THEN SomethingElse
...
END

My Question is "value" is actually a query and it doesn't seem right to me that I should repeat the rather complex query for every wHEN clause. What's the best way to handle this?

Thanks

 

[markros]

Use CROSS APPLY technique (SQL Server 2005 and up), e.g.

select SomeFields, CASE when F.Result > 10 then ... when ... end

from myTable
CROSS APPLY (Some Query goes here related to the main table) F

See example of CROSS APPLY (although I think INNER JOIN in this case is better solution - or may be the same, I'll explore later):

http://social.msdn.microsoft.com/Fo...l/thread/c6ec9ff3-2931-45d1-8865-b8d682fa3b75

PluralSight Learning Library

 

[Kliot]

Thanks for the tip, I'm not familiar with Cross Apply, I'll give it a try.