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.
| Author |
Topic |
|
Amber_Deslaurier
Starting Member
40 Posts |
 Posted - 2010-05-08 : 11:52:05
|
| Hi,This is a little complicted... okay I have a case statement that makes some decisions for a variety of reasons... in the end of the case statement it names the new column SCORE. Depending on what the case statement says it would assign a value of either (NULL, 1, 0) These values would be present in the column SCORE. I do have 10 other columns before score so each row has a unique score.OKAY heres the problem, I want another column called TRACE that looks at each SCORE and if its 1 or 0 ENTER a value of 1 in the column TRACE for each rows that it applies to in the ENTIRE table which has 250,000 rows or more! BUT if the SCORE is NULL meaning its blank just leave it NULL...I guess I would need another case statement... I did try that but it won't work becuase I already have the other one... I guess I need a volatile table or something? Is there a easy way to code this? Please help!Thanks! Amber- |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-05-08 : 12:04:18
|
you need to make a derived table and do like thisSELECT other columns, CASE WHEN SCORE IS NULL THEN NULL ELSE 1 END AS TRACEFROM(SELECT other columns, CASE WHEN ..... AS SCORE)t ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
ms65g
Constraint Violating Yak Guru
497 Posts |
Posted - 2010-05-09 : 16:44:17
|
quote:
CASE WHEN SCORE IS NULL THEN NULL ELSE 1 END AS TRACE
logically it is equivalence:SIGN(ABS(SCORE)+1) AS TRACE |
|
|
|
|
|
|
COMPLEX: CASE Statement SUB QUERY!