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 

jpockets
Starting Member

45 Posts

Posted - 2008-02-13 : 13:52:38
I have a simple query with a case statement:
Select 
Case
when HR.PREM_CUR_QUOTED_AM <> 0 and HR.PREM_LOST_AUTO_AM <> 0 and HR.PREMIUM_BOUND_AM <> 0 and HR.PREMIUM_BOUND_AM <> 0
THen 0
else 1
end Cur_check
from dbo.THIT_RATIO_DETL HR


In the where clause i want to to put
where cur_check = 1 


but i'm getting an error that says :
Invalid column name 'Cur_check'

Can you use a case statement column in the where clause?

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-13 : 14:30:59
Here is how you can do it.

Select

Case
when HR.PREM_CUR_QUOTED_AM <> 0 and HR.PREM_LOST_AUTO_AM <> 0 and HR.PREMIUM_BOUND_AM <> 0 and HR.PREMIUM_BOUND_AM <> 0
THen 0
else 1
end Cur_check

from dbo.THIT_RATIO_DETL HR

Where
Case
when HR.PREM_CUR_QUOTED_AM <> 0 and HR.PREM_LOST_AUTO_AM <> 0 and HR.PREMIUM_BOUND_AM <> 0 and HR.PREMIUM_BOUND_AM <> 0
THen 0
else 1
end = 1

Why are you doing it this way, however? Could you not do:

Select 1 As cur_check

From dbo.THIT_RATIO_DETL HR

Where
HR.PREM_CUR_QUOTED_AM <> 0 And
HR.PREM_LOST_AUTO_AM <> 0 And
HR.PREMIUM_BOUND_AM <> 0 And
HR.PREMIUM_BOUND_AM <> 0
Go to Top of Page

jpockets
Starting Member

45 Posts

Posted - 2008-02-13 : 14:50:52
Thanks Qualis for the help, i didn't even think about doing it in the where clause 100% right about it, thanks.
Go to Top of Page

SusanthaB
Starting Member

14 Posts

Posted - 2008-02-13 : 23:55:44
If your using SQL Server 2005, you have new way of doing this by using the concept of derived table. In SQL Server 2005, there is a new key word named WITH.

Below is how you write the query,

With O
As
(
Select
Case
when HR.PREM_CUR_QUOTED_AM <> 0 and HR.PREM_LOST_AUTO_AM <> 0 and HR.PREMIUM_BOUND_AM <> 0 and HR.PREMIUM_BOUND_AM <> 0
THen 0
else 1
end Cur_check
from dbo.THIT_RATIO_DETL HR
)
select * from O where O.Cur_check=1

This feature is commanly known as CTE. (Comman Table Express)

http://www.sqlservercentral.com/articles/Development/commontableexpressionsinsqlserver2005/1758/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-14 : 03:28:07
or
Where ISNULL(CAST(HR.PREM_CUR_QUOTED_AM AS int),0)+
ISNULL(CAST(HR.PREM_LOST_AUTO_AM AS int),0) +
ISNULL(CAST(HR.PREMIUM_BOUND_AM AS int),0) +
ISNULL(CAST(HR.PREMIUM_BOUND_AM AS int),0) <4
I'm assuming all these fields are of type bit.
Go to Top of Page
   

- Advertisement -