Case Statements in Where Clauses

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 2000 Forums

Transact-SQL (2000)

Case Statements in Where Clauses

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-30 : 09:36:57

Mike writes "Within the select statement there are two case statements:
CASE S.S_Adults
WHEN 2 THEN R.R_Rate+P.PPAdult2
WHEN 3 THEN R.R_Rate+P.PPAdult2+P.PPAdult3
WHEN 4 THEN R.R_Rate+P.PPAdult2+P.PPAdult3+P.PPAdult4
ELSE R.R_Rate
END as [Rate],
CASE S.S_Adults
WHEN 2 THEN D.Rate-R.R_Rate+P.PPAdult2
WHEN 3 THEN D.Rate-R.R_Rate+P.PPAdult2+P.PPAdult3
WHEN 4 THEN D.Rate-R.R_Rate+P.PPAdult2+P.PPAdult3+P.PPAdult4
ELSE D.Rate - R.R_Rate
END as [Variance]

I am trying to omit records where the variance = 0 showing only problem records. Can you put the second "Variance" case statement in the Where Clause and include the " <> 0 "??? I haven't had any luck yet trying.

Does that make sense?"

KHeon
Posting Yak Master

135 Posts

Posted - 2002-07-30 : 10:35:47

EDIT: Removed post cause I didn't read the question completely, and my answer was irrelavent.

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

Edited by - KHeon on 07/30/2002 10:37:57

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-07-30 : 11:10:02

You can put the select as a derived table

select * from ( select
CASE S.S_Adults
WHEN 2 THEN R.R_Rate+P.PPAdult2
WHEN 3 THEN R.R_Rate+P.PPAdult2+P.PPAdult3
WHEN 4 THEN R.R_Rate+P.PPAdult2+P.PPAdult3+P.PPAdult4
ELSE R.R_Rate
END as [Rate],
CASE S.S_Adults
WHEN 2 THEN D.Rate-R.R_Rate+P.PPAdult2
WHEN 3 THEN D.Rate-R.R_Rate+P.PPAdult2+P.PPAdult3
WHEN 4 THEN D.Rate-R.R_Rate+P.PPAdult2+P.PPAdult3+P.PPAdult4
ELSE D.Rate - R.R_Rate
END as [Variance] from t ) as s
where s.rate <> 0
and s.variance <> 0

Btw, it is not a case statement, it is a case expression. There is a case statement in SQL which is not supported by T-sql.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources