CASE WHEN - Problem - 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 2008 Forums

Transact-SQL (2008)

CASE WHEN - Problem

Author

Topic

pod12
Starting Member

2 Posts

Posted - 2012-09-04 : 10:58:56

Hi,

I don't know how to temporarily store the result from a select statement in a 'case when' condition and to use the result, so that I don't have to make the select statement again.

e.g.
select name, case when (select count(*) from table1) > 100 then 1 else (select count(*) from table1) end from tmptable

As you can see in my example I have to do the (select count(*) from table1) two times, how can this be done with just one statement and use somehow this result for the else condition?

thx pod12

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-09-04 : 11:03:16

[code]select NAME , case when COUNT(*) > 100 THEN 1 ELSE COUNT(*) END as CNT
from tmptable
group by NAME[/code]

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

pod12
Starting Member

2 Posts

Posted - 2012-09-04 : 13:31:47

Hi,

actually this was just an example the select in the 'case when' condition is much longer and also has some where conditions in it,
I thougt that it may be possible to somehow store the result in a variable something like:

select name, case when @a = (select count(*) from table1) > 100 then 1 else @a end from tmptable

so not to have another select statement?

any idea?

thx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources