Where condition question - 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 2005 Forums

Transact-SQL (2005)

Where condition question

Author

Topic

bnath001
Starting Member

4 Posts

Posted - 2010-12-28 : 13:23:06

Moring folks,

I have a simple SELECT sql statement. I have one column and one expression field. But, in the WHERE condtion, I want use the expression field. How can I do that.

Below is the sql statement and I want in the WHERE condition, "Where 'Status' = 'ERROR'. But it doesn't work.

SELECT account.name Account
port_dtl.cusip as Cusip,
'Status' = CASE WHEN port_dtl.mtm_price = null and port_dtl.input_sprd = null
THEN 'ERROR' ELSE 'OK' END
from dbo.port_dtl,
dbo.compdesc, dbo.portdesc, dbo.account
where port_dtl.compid = 1 and port_dtl.portid = 18
AND 'Status' = 'OK'

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-28 : 13:42:03

SELECT * FROM
(SELECT account.name Account
port_dtl.cusip as Cusip,
'Status' = CASE WHEN port_dtl.mtm_price = null and port_dtl.input_sprd = null
THEN 'ERROR' ELSE 'OK' END
from dbo.port_dtl,
dbo.compdesc, dbo.portdesc, dbo.account
where port_dtl.compid = 1 and port_dtl.portid = 18) dt
WHERE Status = 'OK'

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

Subscribe to my blog

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-12-28 : 13:43:43

You have to use the whole expression. ALso, you can't= do "= null" use " is null".

Jim


SELECT account.name Account
port_dtl.cusip as Cusip, 
'Status' = CASE WHEN port_dtl.mtm_price = null and port_dtl.input_sprd = null 
THEN 'ERROR' ELSE 'OK' END 
from dbo.port_dtl, 
dbo.compdesc, dbo.portdesc, dbo.account
where port_dtl.compid = 1 and port_dtl.portid = 18 
AND 
CASE WHEN coalesce(port_dtl.mtm_price,port_dtl.input_sprd)  is null
THEN 'ERROR' ELSE 'OK' END  = 'OK'

Everyday I learn something that somebody else already knew

bnath001
Starting Member

4 Posts

Posted - 2010-12-28 : 13:47:20

Thanks so much Tara.
nath

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-28 : 13:56:01

You're welcome, glad to help.

I prefer the derived table approach as it simplifies the query (in my eyes at least).

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

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources