Define column value by comparing two columns

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)

Define column value by comparing two columns

Author

Topic

Scal
Starting Member

24 Posts

Posted - 2011-05-18 : 14:35:16

Hi;
I'm trying to find a way to compare 2 columns in a resultset which looks like this:


CurrentNb  ID  Nb  Name 
---------- --- --- -------
2          1   1   Smith    
2          2   2   Doe    
2          3   3   Rob

The "CurrentNb" column is build with a sub query over some join, something like:


SELECT 
	(SELECT someID FROM U WHERE ID = 2
	) AS CurrentNb, 
	* 
FROM 
	U AS U 
	INNER JOIN U_TO_R AS U2R ON U.ID = U2R.Relation_ID

How can I now do something like:

CASE CurrentNb WHEN CurrentNb > Nb THEN 'red' ELSE 'green' END AS WaningColor

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-18 : 14:41:20

You can make your current query into a sub-query and then add the additional column, something like this:


select 
  *,
  case when currentNb > Nb then 'red' else 'green' end as WarningColor
from
(
	SELECT 
		(SELECT someID FROM U WHERE ID = 2
		) AS CurrentNb, 
		* 
	FROM 
		U AS U 
		INNER JOIN U_TO_R AS U2R ON U.ID = U2R.Relation_ID
) s

Scal
Starting Member

24 Posts

Posted - 2011-05-18 : 16:05:06

I was wondering if it was not possible with the existing query to not add another sub query to the whole thing but if that's the only way :)
Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources