Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Is it possible to use a CASE WHEN statement in a where clause?
- Thread starter bgruver
- Start date
Hi,
Can you give some psudo SQL of what you are trying to do with this idea of the Case When in the where clause?
How would using the CASE statement help qualify rows to process ( which is all the Where clause does ).
Maybe there is way to do what you want without using the case in the where clause if that isn't allowed.
Can you give some psudo SQL of what you are trying to do with this idea of the Case When in the where clause?
How would using the CASE statement help qualify rows to process ( which is all the Where clause does ).
Maybe there is way to do what you want without using the case in the where clause if that isn't allowed.
The query below runs fine, although as tdatgod asked, why would you not qualify on the attributes within the Case statement themself?
SELECT CountiesV.Name, CountiesV.CountyCode
FROM CountiesV
WHERE (Case When DHSViews.CountiesV.CountyCode > '027' Then 'Rural' Else 'Metro' End) = 'metro' ;
SELECT CountiesV.Name, CountiesV.CountyCode
FROM CountiesV
WHERE (Case When DHSViews.CountiesV.CountyCode > '027' Then 'Rural' Else 'Metro' End) = 'metro' ;
SELECT CountiesV.Name, CountiesV.CountyCode
FROM CountiesV
WHERE (Case When DHSViews.CountiesV.CountyCode > '027' Then 'Rural' Else 'Metro' End) = 'metro' ;
But how does this differ from....
SELECT CountiesV.Name, CountiesV.CountyCode
FROM CountiesV
WHERE
(DHSViews.CountiesV.CountyCode <= '027');
Basically every CASE statement could simply be coverted to another condition in the where clause all put together with "AND"s and "OR"s.
what does the Case statement do that the simple where clause doesn't?
FROM CountiesV
WHERE (Case When DHSViews.CountiesV.CountyCode > '027' Then 'Rural' Else 'Metro' End) = 'metro' ;
But how does this differ from....
SELECT CountiesV.Name, CountiesV.CountyCode
FROM CountiesV
WHERE
(DHSViews.CountiesV.CountyCode <= '027');
Basically every CASE statement could simply be coverted to another condition in the where clause all put together with "AND"s and "OR"s.
what does the Case statement do that the simple where clause doesn't?
The original question was can it be done, and an example, which I provided.
The one reason I can think of using a case statement in a where clause, is to help business users with coding. For example if metro counties are 001,005,027, 082,097, the user would enter 'metro' instead of having to know source codes. If it is a non-indexed attribute, I don't think any efficiency is lost because both are full table scans.
The one reason I can think of using a case statement in a where clause, is to help business users with coding. For example if metro counties are 001,005,027, 082,097, the user would enter 'metro' instead of having to know source codes. If it is a non-indexed attribute, I don't think any efficiency is lost because both are full table scans.
- Status
Similar threads
- Locked
- Question