CASE statement - 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

General SQL Server Forums

New to SQL Server Programming

CASE statement

Author

Topic

spitfire122
Starting Member

11 Posts

Posted - 2011-06-15 : 11:18:10

I have a case statement where I need multiple conditions to be applied.

objective: sometimes there is no value in msp3.cma_as_is_value, but there is always a value in ls.principal_balance... so it will say 'yes' to first statement when it should be a no (b/c sometimes there no msp3.cma_as_is_value) so i want it to revert to a 2nd source ls.purchase_value when there is no value in msp3.cma_as_is_value... how do i tell it to go there?

,case when (ls.principal_balance - msp3.cma_as_is_value) > 1 then 'Yes'
	  when (ls.principal_balance - ls.purchase_value) > 1 then 'Yes'
	else 'No' end as Negative_Equity_Flag

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-06-15 : 11:19:52

Nest a case statement in there for that column.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-15 : 11:20:44

What is the meaning of 'no value in msp3.cma_as_is_value'
0?
NULL?
''?

No, you're never too old to Yak'n'Roll if you're too young to die.

spitfire122
Starting Member

11 Posts

Posted - 2011-06-15 : 11:21:27

null

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-15 : 11:25:58

quote:
Originally posted by spitfire122

I have a case statement where I need multiple conditions to be applied.

objective: sometimes there is no value in msp3.cma_as_is_value, but there is always a value in ls.principal_balance... so it will say 'yes' to first statement when it should be a no (b/c sometimes there no msp3.cma_as_is_value) so i want it to revert to a 2nd source ls.purchase_value when there is no value in msp3.cma_as_is_value... how do i tell it to go there?

,case when (ls.principal_balance - msp3.cma_as_is_value) > 1 and msp3.cma_as_is_value is not null then 'Yes'
when (ls.principal_balance - ls.purchase_value) > 1 then 'Yes'
else 'No' end as Negative_Equity_Flag

No, you're never too old to Yak'n'Roll if you're too young to die.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-15 : 12:18:27

CASE is an expression, not a statement! This is important! Here is hatyou want ..

CASE WHEN (LS.principal_balance
– COALESCE (LS.purchase_value, msp3.cma_as_is_value) > 1.00
THEN 'Yes'
ELSE 'No' END AS negative_equity_flag

But we don't write with flags in SQL; that was assembly language. What are you trying to signal with this flag? Juast do the math you need in the CASE expression.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources