Case statement in where clause
-
What I want to do is:
select * from table
where (if regionid>0 then col1 ==1 else col2==1 endif)
Can't figure out how to do it in T-SQL
Thanx.
Calvin
-
Will this work for you:
select * from table
where (regionid>0 and col1 = 1)
or
(regionid<=0 and col2= 1)
[/quote]
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
Yes, The proposal will work but I don't like the code, it looks like cumbersome.
Especially, my condition is much more complex than this, which will make my code ugly and hard to maintain, and sometimes impossible, since regionid might be a sub query, doing so will decrease the performance since you have to evaluate regionid 2 times.
Is there any decent way to do the job? I don't understand why we don't have something like:
cond1 and (if cond2 then cond3 else cond4 endif)
This is so common when evaluating conditions.
or something similiar?
Or even more reasonable:
cond1 and case when cond2 then cond20 when cond3 then cond30 when cond4 then cond40 else cond50 end
T-SQL is just a lame language. I don't even have a iif function.
quote:
Will this work for you:select * from table
where (regionid>0 and col1 = 1)
or
(regionid<=0 and col2= 1)
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
[/quote]
Edited by - cklein on 05/20/2003 10:24:30 AM
-
Yes, The proposal will work but I don't like the code, it looks like cumbersome.
Especially, my condition is much more complex than this, which will make my code ugly and hard to maintain, and sometimes impossible, since regionid might be a sub query, doing so will decrease the performance since you have to evaluate regionid 2 times.
Is there any decent way to do the job? I don't understand why we don't have something like:
cond1 and (if cond2 then cond3 else cond4 endif)
This is so common when evaluating conditions.
or something similiar?
Or even more reasonable:
cond1 and case when cond2 then cond20 when cond3 then cond30 when cond4 then cond40 else cond50 end
T-SQL is just a lame language. I don't even have a iif function.
quote:
Will this work for you:select * from table
where (regionid>0 and col1 = 1)
or
(regionid<=0 and col2= 1)
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
[/quote]
-
quote:
...T-SQL is just a lame language. I don't even have a iif function....
OK, for a while I thought I would just ignore this statement...but I can't.
I am not sure why your view is that T-SQL is a "lame" language. It is a SET-based (not procedural) programming language that requires an understanding of how set-based operations differ fundamentally from procedural programming operations. It is quite a flexible language; I would argue it is as robust as any other language when used correctly. No, it does not have an IIf function (clearly, you are used to MS Access?); but the CASE Statement is far more flexible than the IIf construct.
If you need help with making your T-SQL code work or making it less "cumbersome", please let us know; we're here to help.
Cheers,
Jay
-
Here is an example of a case in the where clause. Not sure if this is what you are looking for:
create table xxx (regionid int, col1 int, col2 int)
insert into xxx values(0,0,0)
insert into xxx values(0,0,1)
insert into xxx values(0,1,0)
insert into xxx values(0,1,1)
insert into xxx values(1,0,0)
insert into xxx values(1,0,1)
insert into xxx values(1,1,0)
insert into xxx values(1,1,1)
select * from xxx where 1 = (case when regionid>0
then col1
else col2
end)
drop table xxx
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
Thanks, Greg,
Again, I don't like the work around you provided. sorry. 🙂
Actually I was using the first approach you mentioned before I asked the question.
OK, my query is actually kindof complicated, I just don't want to make a query which I have to look at it for 5 minutes to understand it 3 months later.
All I don't understand that why CASE statement can't return a boolean value, but it can return string, int, float ,...,etc. Is there anything wrong with boolean value? I intend to think this is a bug in the T-SQL interpreter.
Here is my actual scenerio:
select assets based on OrderType
if OrderType is not "TFW", get all assets where AssetTypeDesc in ("RGNEW", "RGREF", "dsad" ,"dada", "dsada") any way a bunch of them.
if OrderType IS "TFW", then based on @RegionCodeID.
If @RegionCodeID is 0 then select AssetTypeDesc in ("jkjlk", ...) a different set of desc.
if @RegionCodeID is not 0, then if AssetRegionCodeID = @RegionCodeID, AssetTypeDesc should be in ("gfdgfd"...)
else
AssetTypeDesc should be in ("dsadja", "dsada",...) a different set.
Yes, I have worked around it by using your first approach with a long nested case statement. It works, I juts don't like it.
If we have a IIF or CASE statement can return a boolean value, then things will be much more elegent.
Calvin
-
SELECT AssetFields
FROM Asset
WHERE OrderType <> 'TFW'
AND AssetTypeDesc IN ('RGNEW', 'RGREF', 'dsad' ,'dada', 'dsada')
UNION ALL
SELECT AssetFields
FROM Asset
WHERE OrderType = 'TFW'
AND RegionCode = 0
AND AssetTypeDesc in ('jkjlk', ...)
UNION ALL
SELECT AssetFields
FROM Asset
WHERE OrderType = 'TFW'
AND RegionCode <> 0
AND AssetTypeDesc in ('other values')Again, use SET-based procedures to UNION sets of clearly disparate data that is stored in the same table. Is it possible that this query is attempting to accomplish more than is needed in one step? Can it be made more elegant simply by cutting into a variety of steps and then executing? In other words, it seems like your table structures are in conflict with your business rules, or at least that the table structures don't adequetely support your business rules...
-
I thought of Union All before. But I am not sure whether it supports Cursor, actually I am using this select statement as a cursor to walk though records, and I eliminated the order by clause.
But maybe a union all is the way to go. The actually statement is still much more complicate than this, as it joins several tables.
As to I do too much stuff in one step? Well, I am lazy, I hate to duplicate code (I am a C/C#) programmer. I want to reuse code, and T-SQL is not good at that, so I process 2 order type in one stored procedure since they are only different in where clause.
The table is designed properly in my understanding. We could make things easier if I set up another helper tables to store AssetTypeDesc with different conditions, so I can simply use a join, but I didn't do it, since it's only used in one place.
The business rule is added to the over all database granually. The client don't know what they want in the beginning, but they know you didn't do enough until you show your application. Don't argue with me how to do a project, it's impossible to gather all the business rules to do a decent design before get your hands dirty.
To be honest, with union all, it's easy to understand, but too many duplicate code (consider this is a complicate query which could possible be 1 or 2 pages long.) But I think your solution is good, because maintainability/readability is priority#1 in my list.
Now my logic is not that complicated, union all will have problem with complicated logic, since it has to walk through almost every logic path by a different select statement if that logic path is difficult to implement with a case statement. Again, is there any particular reason a case statement incapable of return a boolean value? Why can't we say:
cond1 and case .... end?
Calvin
-
quote:
...Again, is there any particular reason a case statement incapable of return a boolean value? Why can't we say:cond1 and case .... end?
Greg Larsen already answered this question, with the following code:
select * from xxx where 1 = (case when regionid>0
then col1
else col2
end)CASE can return anything you want it to -- a field value or a constant. In this case, Greg was showing you that the CASE statement can return a zero or one based on your conditions, and compare that to the static value of 1.
I think you are looking for something like this:
select * from xxx
where 1 =
(
case
when condition1 then 1
when condition2 then 1
when condition3 then 1
else 0
end
)Calvin,
I didn't mean to offend you with my comment about business requirements and the structure of your tables; I simply meant to illuminate what I thought was the source of the problem and possibly help you find an alternate solution. As to wanting to accomplish lots of things in one step, I understand your intention, I just disagree with the approach. No big deal. Good luck.
-
Thanx.
The return 0 or 1 approach will work, though it's still not a perfect solution, but I will prefer it than Union All. 🙂
By the way, there's another 2 things about SQL kill me all the time, not sure whther you gurus already looked into it or not:
1. Efficient Source Control for Stored Procedures? Or what's the best choice? I got used to VSS integrated with Visual Studio. 🙂
2. SQL formatter? Is there a free SQL formatter around? I am using SQL 2000, a pages long stored procedure is hard to read.
Thanx,
Calvin
-
CKLEIN hang in there.
Start searching for Joe Celko Smarties(=book).
-
A couple notes:
I know you prefer not to use the UNION ALL approach, but I would be willing to wager that the query plan generated for an identical query using UNION ALL will outperform the same thing with a WHERE 1=CASE STATEMENT query; the optimizer will more likely be able to utilize appropriate indexes with the UNION ALL statements.
As to SourceSafe, I TOTALLY agree. I talked to an MVP at VSLive recently in Chicago and they intimated that Yukon should include support for SourceSafe, which would be killer.
As for SQL formatter, Query Analyzer is pretty much free-form; it is up to the programmer to define the format they feel most comfortable with. However, if your're interested, I would recommend checking out the templates ability of QueryAnalyzer 2000. It might come in handy...
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply